SQL 从 2 个查询中除以 2 个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5079879/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL dividing 2 values from 2 queries
提问by user380432
I have 2 queries that are as follows:
我有 2 个查询，如下所示：
SELECT COUNT(cvu.[ID]), 'Exp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate < GetDate()
AND cvu.Id = '4C1'
And the second one:
第二个：
SELECT COUNT(cvu.[ID]), 'NonExp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate > GetDate()
AND cvu.Id = '4C1'
How would I divide the counts between these two? It will always only return 2 values and one will be called Exp and one will be called NonExp.
我将如何划分这两者之间的计数？它总是只返回 2 个值，一个称为 Exp，一个称为 NonExp。
Thanks
谢谢
回答by AgentDBA
Basically treat those two queries as sub queries as below.
基本上将这两个查询视为子查询，如下所示。
select x.number / y.number
from
(
SELECT COUNT(cvu.[ID]) as number, 'Exp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate < GetDate()
AND cvu.Id = '4C1'
) x
join
(
SELECT COUNT(cvu.[ID]) as number, 'NonExp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate > GetDate()
AND cvu.Id = '4C1'
) y on 1=1
If you wanted to take it further you could then have the cvu.id as part of the select and modify the join so you could do it across all cvu.id's
如果您想更进一步，您可以将 cvu.id 作为选择的一部分并修改连接，以便您可以在所有 cvu.id 中执行此操作
select x.id, x.number / y.number
from
(
SELECT cvu.id, COUNT(cvu.[ID]) as number, 'Exp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate < GetDate()
group by cvu.Id
) x
join
(
SELECT cvu.id, COUNT(cvu.[ID]) as number, 'NonExp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate > GetDate()
group by cvu.Id
)y on x.id = y.id
回答by Da Rock
Here's something I just did that shows how to divide the results from two separate queries--you have to CAST them to a divisible datatype in order to get a result other than Zero:
这是我刚刚做的一些事情，它展示了如何从两个单独的查询中划分结果——您必须将它们转换为可分割的数据类型才能获得零以外的结果：
WITH
T1 AS (SELECT DISTINCT COUNT(StudID) as NumA FROM TBL WHERE MetTarget = 'Y'),
T2 AS (SELECT COUNT( DISTINCT FallScore + SprgScore ) as NumB FROM TBL)
SELECT CAST(T1.NumA AS FLOAT) / CAST(T2.NumB AS FLOAT) * 100 as PctMetTgt
FROM T1, T2
回答by JAiro
Try this: select (Query1) / (Query1)
试试这个：选择（查询1）/（查询1）
in you Case it would be:
在你的情况下，它将是：
select (SELECT COUNT(cvu.[ID]), 'Exp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate < GetDate()
AND cvu.Id = '4C1') / (SELECT COUNT(cvu.[ID]), 'NonExp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate > GetDate()
AND cvu.Id = '4C1')
回答by Nathan DeWitt
SELECT
(SELECT COUNT(cvu.[ID]) --, 'Exp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate < GetDate()
AND cvu.Id = '4C1' )
/
(SELECT COUNT(cvu.[ID]) --, 'NonExp' AS [Exp]
FROM [dbo].[tblClientVehicleUnit] cvu
WHERE ExpirationDate > GetDate()
AND cvu.Id = '4C1' )
