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

我有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。

谢谢

最佳答案

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

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