sql - SQL 按字符匹配过滤

Question

如何在不对给定数据构建新的用户函数(即您可以使用内置函数)的情况下过滤掉计数匹配项的数量？

要求是获取 gw 列编号出现相同次数的行，或者如果存在不同的一组数量，则它们的数量必须与其他数量相匹配。 IE。它可能像 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

值可以是任意正数。为了匹配标准值也必须至少是总数的两倍。 IE。 1 2 3 4 0 不行。因为每个值只出现一次。 1 1 0 3 3 匹配。

Answer 1

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

最后，只获取max(cc)大于1的login。这是为了消除像 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