获得按时隙分组的售票和预订

Question

好的，当涉及到SQL时，我绝对不是专家，我将以这句话作为开头。此外，希望我发布的信息将足以让这些人知道我需要做什么。话虽如此，但我的疑问是：

USE GM001

SELECT tblCalendarTemp.strSessionName                              AS Mission,
       CASE tblcalendartemp.lngslot
         WHEN 1 THEN '11:10 AM'
         WHEN 2 THEN '11:30 AM'
         WHEN 3 THEN '11:50 AM'
         WHEN 4 THEN '12:10 PM'
         WHEN 5 THEN '12:30 PM'
         WHEN 6 THEN '12:50 PM'
         WHEN 7 THEN '1:10 PM'
         ...
         WHEN 34 THEN '10:10 PM'
         WHEN 35 THEN '10:30 PM'
         WHEN 36 THEN '10:50 PM'
         WHEN 37 THEN '11:10 PM'
         WHEN 38 THEN '11:30 PM'
         WHEN 39 THEN '11:50 PM'
         WHEN 40 THEN '12:10 AM'
       END                                                         AS [Time of Session],
       tblCalendarTemp.lngSold                                     AS Total,
       COUNT(tblItemReserve.lngQuantity)                           AS Reserved,
       tblCalendarTemp.lngSold - COUNT(tblItemReserve.lngQuantity) AS Tickets
FROM   tblCalendarTemp
       LEFT OUTER JOIN tblItemReserve
         ON tblCalendarTemp.dateStart = tblItemReserve.dateCalendar
WHERE  ( tblCalendarTemp.lngMerchItemID = 1 )
       AND ( tblItemReserve.lngMerchItemID = 1 )
GROUP  BY tblCalendarTemp.lngSold,
          tblCalendarTemp.lngSlot,
          tblCalendarTemp.strSessionName 

我得到的结果在这张照片里。

。

显然，它所做的是对整个lngQuantity列进行计数。我已经尝试过sum，而不是count，并有类似的结果。我对在最终结果中使用这两种方法都很满意。我想说的是(仅用几行作为例子)：

Neptune : 1:10PM : 29 : 20 : 9
Pluto : 1:30PM : 13 : 13 : 0
Mercury : 1:50PM : 0 : 0 : 0
Venus : 2:10PM : 13 : 13 : 0

我在这里错过了什么？有人能帮我吗？我会张贴更多的信息，如果需要！

编辑:好的，这是一些更多的信息，希望这将有助于解决问题。这是CalendarTemp表的图片

。这是ItemReserve表中相关信息的图片。

。CalendarTemp表列出了当前每一次激光标签任务的门票销售数量，并在pos给客人打电话时进行了更新。itemreserve表显示已安排激光标记的生日事件和组事件，其中的活动包括在日历表中。我想要做的是在一个节目中显示出有多少客人有票(走过来买票的人)，激光标签游戏中有多少人参加团体活动，以及总数(显然是日历表中显示的数字)。因此，我需要将每个时隙的所有lngquantity列相加，并将其与另一个表lngslot匹配。如果需要其他信息，请告诉我。

编辑编号2：

好的，这是原始数据的独家新闻。tblCalendarTemp表是一个每天都会自动更改的表。它有一个lngSlot列，其增量为1，这一列表示激光标记任务。这些任务分20分钟递增。我有lngMerchItemID =1，因为这是激光标签任务，还有其他我没有跟踪的lngMerchItemID(派对房间、卡丁车比赛等等)。lngSold列显示了每个游戏中有多少人，并在某个团体被预定为激光标记任务或有人购买一张票时进行更新。dateStart列指示日历的日期。

tblItemReserve表显示了特定激光标记任务中的保留点(已预订到任务中的事件)。它的lngSlot列具有与tblCalendarTemp相同的相关时隙。但是，它的lngSlot不增加，并且相同的数字可以重复。其原因是lngQuantity列。在每个活动中，每个人都有一行被预定到特定的激光标签任务中。例如，Smith Party有10个人，因此将有10行具有相同的lngSlot，lngQuantity将始终读取每一行的1.00。dateCalendar应该与另一个表中的dateStart相同。我需要将每个lngQuantity的所有lngSlot加起来，并将其与另一个表中的lngSlot相匹配。

我希望有一个结果表，第一列是名为“任务名称”的strSession列。第二列应该是使用lngSlot列的时间增量，正如我们已经确定的那样，按lngSlot数值顺序排列。第三列应该是任务中的总人数( lngSold列，由于它是自动更新的，不需要对其执行任何数学函数)。第四列应该是每个lngSlot的lngQuantity's之和(仅用于lngMerchItemID = 1)，并被命名为保留。最后，第五栏应该是售票，只需从第三栏减去第四栏。

希望这个信息，再加上下面的截图是足够的解释，你们可以帮助我完成这个查询。我的查询非常接近，但它接受了lngMerchItemID = 1的整个lngMerchItemID列的总和。

Answer 1

对于查询的第一部分和所有这些硬编码的CASE表达式，有一种更好的方法：

;WITH n(i) AS 
(
  SELECT TOP (40) ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects
),
d(lngSlot, [Time of Session]) AS 
(
  SELECT i, RIGHT(CONVERT(CHAR(19), DATEADD(MINUTE, 20*i, '10:50 AM'), 0),7) 
  FROM n
)
SELECT lngSlot, [Time of Session]
FROM d
ORDER BY lngSlot;

部分结果：

lngSlot    Time of Session
-------    ---------------
1          11:10AM
2          11:30AM
3          11:50AM
4          12:10PM
5          12:30PM
6          12:50PM
7           1:10PM
...        
34         10:10PM
35         10:30PM
36         10:50PM
37         11:10PM
38         11:30PM
39         11:50PM
40         12:10AM

更多没有循环或乏味的CASE表达式的生成集的示例：

http://sqlperformance.com/generate-a-set-1

http://sqlperformance.com/generate-a-set-2

http://sqlperformance.com/generate-a-set-3

因此，知道了这一点，并对输入到所需输出的原始数据做了一些假设，您所要查找的查询可能是：

;WITH n(i) AS 
(
  SELECT TOP (40) ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects
),
d(lngSlot, [Time of Session]) AS 
(
  SELECT i, RIGHT(CONVERT(CHAR(19), DATEADD(MINUTE, 20*i, '10:50 AM'), 0),7) 
  FROM n
)
SELECT 
  Mission    = t.strSessionName,
  d.[Time of Session],
  [Total]    = SUM(t.lngSold),
  [Reserved] = COALESCE(SUM(r.lngQuantity),0),
  Tickets    = SUM(t.lngSold) - COALESCE(SUM(r.lngQuantity),0) 
FROM d
INNER JOIN dbo.tblCalendarTemp AS t
ON d.lngSlot = t.lngSlot
LEFT OUTER JOIN dbo.tblItemReserve AS r
ON t.dateStart = r.dateCalendar
AND t.lngMerchItemID = r.lngMerchItemID
WHERE t.lngMerchItemID = 1
GROUP BY t.strSessionName, d.[Time of Session];

在你的问题中包括样本数据是非常重要的，否则我们不知道你是如何得到你的“答案”的。