我有一个包含调度插槽的表，称为:
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
你可以在没有内部选择的情况下做到这一点：
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)
这是一个简单的分组查询：
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")
