本文介绍了SQL 从 2 个查询中除以 2 个值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有 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 个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 17:01
查看更多