SQL 从 2 个查询中除以 2 个值 [英] SQL dividing 2 values from 2 queries

问题描述

我有 2 个查询，如下所示:

I have 2 queries that are as follows:

  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'

我将如何划分这两者之间的计数?它总是只返回 2 个值，一个称为 Exp，一个称为 NonExp.

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.

谢谢

推荐答案

基本上将这两个查询当作子查询，如下所示.

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

如果您想更进一步，您可以将 cvu.id 作为选择的一部分并修改连接，以便您可以在所有 cvu.id 中执行此操作

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

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

这篇关于SQL 从 2 个查询中除以 2 个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！