代码之家 › 专栏 › 技术社区 › r3plica

带有select和group by和calculated列的SQL Server问题

sql-server sql

r3plica · 技术社区 · 7 年前

我有一个SQL正在尝试构建:

select 
    a.Name,
    (SELECT COUNT(b.PlannedCollectionDate) WHERE b.PlannedCollectionDate < GETDATE()) AS Due,
    (SELECT COUNT(b.PlannedCollectionDate) WHERE b.PlannedCollectionDate = GETDATE()) AS Today,
    (SELECT COUNT(b.PlannedCollectionDate) WHERE b.PlannedCollectionDate = DATEADD(DAY, 1, GETDATE())) AS Expected,
    (SELECT COUNT(b.PlannedCollectionDate) WHERE b.PlannedCollectionDate > DATEADD(DAY, 1, GETDATE())) AS Planned
from Centers AS a
INNER JOIN Collections AS b
ON a.Id = b.CenterId
GROUP BY a.Name

但我得到一个错误:

“Collections.PlannedCollectionDate”列在选择列表中无效,因为它不包含在聚合函数或Group By子句中。

我知道我可以这样做:

select 
    a.Name,
    (SELECT COUNT(Id) FROM Collections WHERE CenterId = a.Id AND PlannedCollectionDate < GETDATE()) AS Due,
    (SELECT COUNT(Id) FROM Collections WHERE CenterId = a.Id AND PlannedCollectionDate = GETDATE()) AS Today,
    (SELECT COUNT(Id) FROM Collections WHERE CenterId = a.Id AND PlannedCollectionDate = DATEADD(DAY, 1, GETDATE())) AS Expected,
    (SELECT COUNT(Id) FROM Collections WHERE CenterId = a.Id AND PlannedCollectionDate > DATEADD(DAY, 1, GETDATE())) AS Planned
from Centers AS a

但我认为这样做要慢一些,因为我必须从同一个表(集合)中进行多次选择。

所以,我的问题是,我该怎么做才能使我的第一个查询工作?我不认为按计划的集合日期分组是正确的,因为它会把我的计数

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

我认为您需要条件聚合:

select ce.Name,
       SUM(CASE WHEN co.PlannedCollectionDate < GETDATE() THEN 1 ELSE 0 END) AS Due,
       SUM(CASE WHEN co.PlannedCollectionDate = GETDATE() THEN 1 ELSE 0 END) AS Today,
       SUM(CASE WHEN co.PlannedCollectionDate = DATEADD(DAY, 1, GETDATE()) THEN 1 ELSE 0 END) AS Expected,
       SUM(CASE WHEN co.PlannedCollectionDate > DATEADD(DAY, 1, GETDATE()) THEN 1 ELSE 0 END) AS Planned
from Centers ce join
     Collections co
     on ce.Id = co.CenterId
group by ce.Name;

这实现了你所写的。注意使用 有意义 表别名。

但是,它不会做你想要的,因为 GETDATE() 具有时间组件。要解决此问题,请将其转换为日期:

select ce.Name,
       SUM(CASE WHEN co.PlannedCollectionDate < CONVERT(DATE, GETDATE()) THEN 1 ELSE 0 END) AS Due,
       SUM(CASE WHEN co.PlannedCollectionDate = CONVERT(DATE, GETDATE()) THEN 1 ELSE 0 END) AS Today,
       SUM(CASE WHEN co.PlannedCollectionDate = DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) THEN 1 ELSE 0 END) AS Expected,
       SUM(CASE WHEN co.PlannedCollectionDate > DATEADD(DAY, 1, CONVERT(DATE, GETDATE())) THEN 1 ELSE 0 END) AS Planned
from Centers ce join
     Collections co
     on ce.Id = co.CenterId
group by ce.Name;

注意,这假设 PlannedCollectionDate 没有时间组件。