计算会话ID出现的次数(MSSQL)
计算会话ID出现的次数(MSSQL)
提问于 2013-01-28 22:52:28
我试图把在我们的票务网站上的用户的实时视图。
目前，我在网站上有两个会话(会话列)。我当前的查询返回以下内容：
EVENTID EVENT Session SessionOpened SessionLength NumberofTix
1619 BB King 11 2013-01-28 09:34:16.300 -2 2
1622 BB King 12 2013-01-28 09:36:55.073 0 1
1622 BB King 12 2013-01-28 09:36:55.077 0 3但是，它应该看起来像这样：
EVENTID EVENT Session SessionOpened SessionLength NumberofTix
1619 BB King 11 2013-01-28 09:34:16.300 -2 2
1622 BB King 12 2013-01-28 09:36:55.073 0 4为什么会话12返回2行-1行包含1张票证，1行包含3张票证？
查询：
SELECT distinct set_eSRO_Event as EventID, shName as EventName, setSRO_UID as SRO_SessionID, setDateTime as SessionStarted,
DATEDIFF(mi, getDate(), setDateTime) as SessionTimer, Count(*) AS NumberofTickets FROM SessionTickets
INNER JOIN Shows on shCode = setShowCode
WHERE setStatus = 0
GROUP BY setsro_UID, shName, set_eSRO_Event, setDateTime回答 2
Stack Overflow用户
回答已采纳
发布于 2013-01-28 22:59:49
你的结果列和查询的别名不一致，所以很难说。我认为setDateTime是不同的(= SessionStarted或SessionOpened)。当你按setDatetime分组时，如果它们是不同的=>两行。
因此，向setDatetime添加一个MIN聚合函数(就像您“想要”的结果一样)，并从GROUP BY中删除setDatetime
SELECT set_eSRO_Event as EventID,
shName as EventName,
setSRO_UID as SRO_SessionID,
MIN(setDateTime) as SessionStarted,
DATEDIFF(mi, getDate(), MIN(setDateTime)) as SessionTimer,
COUNT(*) AS NumberofTickets
FROM SessionTickets
INNER JOIN Shows on shCode = setShowCode
WHERE setStatus = 0
GROUP BY setsro_UID, shName, set_eSRO_EventStack Overflow用户
发布于 2013-01-28 23:00:05
在不知道哪一列属于哪个表以及SQL Server版本的情况下，您可以尝试执行以下查询：
SELECT EventID,
EventName,
SRO_SessionID,
MIN(SessionStarted) SessionStarted,
SessionTimer,
SUM(NumberofTickets) NumberofTickets
FROM ( SELECT set_eSRO_Event as EventID,
shName as EventName,
setSRO_UID as SRO_SessionID,
setDateTime as SessionStarted,
DATEDIFF(mi, getDate(), setDateTime) as SessionTimer,
Count(*) AS NumberofTickets
FROM SessionTickets
INNER JOIN Shows
ON shCode = setShowCode
WHERE setStatus = 0
GROUP BY set_eSRO_Event, shName, setSRO_UID, setDateTime,
DATEDIFF(mi, getDate(), setDateTime)) A
GROUP BY EventID, EventName, SRO_SessionID, SessionTimer页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14564642
复制相关文章
相似问题
腾讯云开发者
