sql-server - SQL Server count() over() 与不同

Question

我正在开发一个项目，我们需要计算不同行的数量。该场景的简化版本包括一个 user 表、一个 keyword 表和一个 keyword_user 表。

user 表仅包含常见的用户元数据，例如姓名等。下面列出了其他表。

关键字用户:

id
user_id
keyword_id

关键字:

id,
description

我想要做的是根据用户的keyword_id找到最大用户数(5)，同时还计算匹配行的总数。计数必须是不同的。

查询:

SELECT TOP 5 u.[id], 
             u.[firstname], 
             u.[lastname], 
             total = Count(*) OVER() 
FROM   [user] u 
       INNER JOIN [keyword_user] ku 
               ON u.[id] = ku.[user_id] 
WHERE  ( ku.keyword_id IN ( '5f6501ec-0a71-4067-a21d-3c5f87a76411', 'c19b95c0-8554-4bbd-9526-db8f1c4f1edf')) 
       AND u.id NOT IN ( '12db3001-b3b9-4626-8a02-2519102cb53a' ) 

结果集:

+--------------------------------------+-----------+----------+-------+
|                  id                  | firstname | lastname | total |
+--------------------------------------+-----------+----------+-------+
| F0527AC3-747A-45A6-9CF9-B1F6C7F548F8 | Kasper    | Thomsen  |     3 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     3 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     3 |
+--------------------------------------+-----------+----------+-------+

问题:

问题在于，Michael 被计算了两次，因此总计数为 3，而我希望它为 2。当使用 count() over() 时，您无法解析表达式入其中，即蕴含鲜明。另外，如果我只是SELECT DISTINCT，除了总计数仍然是 3 之外，我的结果集看起来不错。

如果我需要包含更多信息来支持该问题，请告诉我，我会尽力回答。

MSSQL 创建数据库脚本(示例数据)

example_data.sql

想要的结果集:

+--------------------------------------+-----------+----------+-------+
|                  id                  | firstname | lastname | total |
+--------------------------------------+-----------+----------+-------+
| F0527AC3-747A-45A6-9CF9-B1F6C7F548F8 | Kasper    | Thomsen  |     2 |
| 95988F6D-9C91-4779-B6C3-3D4B4D6AE836 | Michael   | Jacobsen |     2 |
+--------------------------------------+-----------+----------+-------+

Answer 1

你真的应该在问题中解释你需要什么，而不是在评论中。

在CTE_Users中，我们找到给定关键字的所有不同用户。然后将结果与 user 连接以获取用户详细信息。至少它会根据给定的小样本数据产生您期望的结果。

WITH
CTE_Users
AS
(
    SELECT DISTINCT ku.user_id
    FROM
        keyword_user AS ku
    WHERE
        ku.keyword_id IN (
            '5f6501ec-0a71-4067-a21d-3c5f87a76411', 
            'c19b95c0-8554-4bbd-9526-db8f1c4f1edf')
        AND ku.user_id NOT IN (
            '12db3001-b3b9-4626-8a02-2519102cb53a')
)
SELECT TOP(5)
    u.id
    ,u.firstname
    ,u.lastname
    ,COUNT(*) OVER() AS total
FROM
    user AS u
    INNER JOIN CTE_Users ON CTE_Users.user_id = u.id
;