SQL Union 与 COUNT() 函数未返回预期结果

发布于 2024-12-28 01:30:28 字数 703 浏览 2 评论 0原文

我正在尝试返回所有使用过语音会议的 MSFT Lync 用户及其使用次数。我必须使用 UNION，因为表中有 User1 和 User2，他们都参加了我想要在一列中的会议。

SQL 如下：

SELECT U.UserUri,
       COUNT(U.UserUri) AS COUNT
FROM   SessionDetails AS S
       INNER JOIN Users U
         ON S.User1Id = U.UserId
WHERE  MediaTypes = 48
GROUP  BY U.UserUri
UNION
SELECT U.UserUri,
       COUNT(U.UserUri) AS COUNT
FROM   SessionDetails AS S
       INNER JOIN Users U
         ON S.User2Id = U.UserId
WHERE  MediaTypes = 48
GROUP  BY U.UserUri

返回的结果显示参加过一次以上会议的任何人在列表中出现两次，总计有两个。实际上，输入此内容后，它必须按 User1Id 和 User2ID 进行分组。如果一个人有 9 次对话，但只发起了其中 6 次，那么他们将成为用户 1 6 次，用户 2 3 次，从而创建两个不同的分组。

现在我知道为什么了，但是有什么想法如何返回不重复的 UserUri 吗？非常感谢任何帮助！

原文

I am trying to return all MSFT Lync Users who have used voice conferencing and the number of times they used it. I have to use the UNION because the table has User1 and User2 who both participated in the conference which I want in one column.

Here is the SQL:

SELECT U.UserUri,
       COUNT(U.UserUri) AS COUNT
FROM   SessionDetails AS S
       INNER JOIN Users U
         ON S.User1Id = U.UserId
WHERE  MediaTypes = 48
GROUP  BY U.UserUri
UNION
SELECT U.UserUri,
       COUNT(U.UserUri) AS COUNT
FROM   SessionDetails AS S
       INNER JOIN Users U
         ON S.User2Id = U.UserId
WHERE  MediaTypes = 48
GROUP  BY U.UserUri

The returned results have anyone who participated in more than one conference appearing in the list twice, with two count totals. Actually, after typing this out it must be grouping by User1Id and User2ID. If a person had 9 conversations but iniated only 6 of them they would be User1 6 times and User2 3 times, creating the two different groupings.

Now I know why, but any ideas how to return no duplicate UserUri? Any help greatly appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

素罗衫 2025-01-04 01:30:28

UNION后做聚合吗？

SELECT
    T.UserUri, COUNT(*)
FROM
    (
    SELECT U.UserUri
    FROM   SessionDetails AS S
           INNER JOIN Users U
             ON S.User1Id = U.UserId
    WHERE  MediaTypes = 48
    UNION ALL
    SELECT U.UserUri
    FROM   SessionDetails AS S
           INNER JOIN Users U
             ON S.User2Id = U.UserId
    WHERE  MediaTypes = 48
    ) T
GROUP BY 
   T.UserUri

编辑：修复别名，将 ALL 添加到 UNION

Do the aggregate after the UNION?

SELECT
    T.UserUri, COUNT(*)
FROM
    (
    SELECT U.UserUri
    FROM   SessionDetails AS S
           INNER JOIN Users U
             ON S.User1Id = U.UserId
    WHERE  MediaTypes = 48
    UNION ALL
    SELECT U.UserUri
    FROM   SessionDetails AS S
           INNER JOIN Users U
             ON S.User2Id = U.UserId
    WHERE  MediaTypes = 48
    ) T
GROUP BY 
   T.UserUri

Edit: fixed aliases, added ALL to UNION

回复收藏 0 原文

~没有更多了~