mssql中按天计数字段

Question

我有一个包含调度插槽的表，称为:
ScheduleSlots

字段:
id(int)
scheduleID(int)
time(datetime)
availableslots(int)
CalendarGroupID(int)
Level(int)
enabled(bit)


我想要设置一个gridview，在其中获取所有日期并计算每天的已启用和已禁用日期。


我不确定如何编写sql语句来执行此操作。

即。

日期启用已禁用
3/31/2021 20 20
4/1/2021 10 30

SELECT Time, scheduleID,
(SELECT COUNT(Enabled) FROM [dbo].[ScheduleSlots] 
WHERE Cast(Time as Date)>='2021-03-31' AND Cast(Time as Date)<='2021-04-01' AND CalendarGroupID=1 AND Level=1 AND Enabled=1) as Enabled, 
(SELECT COUNT(Enabled) FROM [dbo].[ScheduleSlots] 
WHERE Cast(Time as Date)>='2021-03-31' AND Cast(Time as Date)<='2021-04-01' AND CalendarGroupID=1 AND Level=1 AND Enabled=0) as Disabled
FROM [dbo].[ScheduleSlots] 
WHERE Cast(Time as Date)>='2021-03-31' AND Cast(Time as Date)<='2021-04-01' AND CalendarGroupID=1 AND Level=1
GROUP BY scheduleID, Time

Answer 1

这是一个简单的分组查询：

SELECT 
  CONVERT(DATE, "Time") AS "Date", 
  SUM(CONVERT(INT, "Enabled")) AS "Enabled",
  COUNT() - SUM(CONVERT(INT, "Enabled")) AS "Disabled"
FROM "dbo"."ScheduleSlots"
WHERE "CalendarGroupID" = 1 
  AND "Level" = 1
GROUP BY CONVERT(DATE, "Time")

Answer 2

你可以在没有内部选择的情况下做到这一点：

SELECT [Time],
    SUM([Enabled]) as [Enabled], 
    SUM([Disabled]) as [Disabled]
FROM [dbo].[ScheduleSlots] 
WHERE 
    [Time]>='2021-03-31' 
    AND [Time]<='2021-04-01'
    AND CalendarGroupID=1 
    AND Level=1
GROUP BY Cast([Time] as Date)