SQL从2个查询中除以2个值

Question

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

  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。

谢谢

Answer 1

基本上将这两个查询视为子查询，如下所示。

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的

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