sql - TSQL按字符匹配过滤

如何在给定数据上不建立新的用户功能(即可以使用内置功能)的情况下过滤出计数匹配项的数量？

要求是使行的gw列号出现相同的次数，或者如果存在一组不同的数目，则它们的编号必须与其他计数匹配。即它可能像Sandy一样全为1，也可能是Don，因为它有两倍的“1”和两倍的“2”。 Voland不能满足要求，因为他有两次“1”，但只有一次“2”，依此类推。您根本不希望将“0”计数为零。

login      gw1 gw2 gw3 gw4 gw5
Peter       1   0   1   0   0
Sandy       1   1   1   1   0
Voland      1   0   1   2   0
Don         1   2   0   1   2

Diserid输出为:

login      gw1 gw2 gw3 gw4 gw5
Peter       1   0   1   0   0
Sandy       1   1   1   1   0
Don         1   2   0   1   2

值可以是任意正数。为了匹配标准值，还必须至少总计两次。即1 2 3 4 0不好。因为每个值仅出现一次。 1 1 0 3 3是一个匹配项。

最佳答案

SQL Fiddle

WITH Cte(login, gw) AS(
    SELECT login, gw1 FROM TestData WHERE gw1 > 0 UNION ALL
    SELECT login, gw2 FROM TestData WHERE gw2 > 0 UNION ALL
    SELECT login, gw3 FROM TestData WHERE gw3 > 0 UNION ALL
    SELECT login, gw4 FROM TestData WHERE gw4 > 0 UNION ALL
    SELECT login, gw5 FROM TestData WHERE gw5 > 0
),
CteCountByLoginGw AS(
    SELECT
        login, gw, COUNT(*) AS cc
    FROM Cte
    GROUP BY login, gw
),
CteFinal AS(
    SELECT login
    FROM CteCountByLoginGw c
    GROUP BY login
    HAVING
        MAX(cc) > 1
        AND COUNT(DISTINCT gw) = (
                SELECT COUNT(*)
                FROM CteCountByLoginGw
                WHERE
                    c.login = login
                    AND cc = MAX(c.cc)
        )
)
SELECT t.*
FROM CteFinal c
INNER JOIN TestData t
    ON t.login = c.login

首先，您对表进行unpivot，但不包括等于0的gw。
结果(CTE)为:

login      gw
---------- -----------
Peter      1
Sandy      1
Voland     1
Don        1
Sandy      1
Don        2
Peter      1
Sandy      1
Voland     1
Sandy      1
Voland     2
Don        1
Don        2

然后，执行COUNT(*) GROUP BY login, gw。结果将是(CteCountByLoginGw):

login      gw          cc
---------- ----------- -----------
Don        1           2
Peter      1           2
Sandy      1           4
Voland     1           2
Don        2           2
Voland     2           1

最后，仅获取login大于max(cc)的那些1。这是为了消除像1,2,3,4,0这样的行。唯一的login与gw相同的max(cc)。这是为了确保gw列的出现与其他列相同:

login      gw1         gw2         gw3         gw4         gw5
---------- ----------- ----------- ----------- ----------- -----------
Peter      1           0           1           0           0
Sandy      1           1           1           1           0
Don        1           2           0           1           2