时间戳

首页 > 解决方案 > 查询返回多个 id

mysql - 查询返回多个 id

问题描述

我有一个查询，可以在多个列（命名为 S1、S2、S3、S4、S5 和 S6）中找到最常见的数字。查询似乎工作得很好，但它不计算列中每个 id 的总数.

这是查询：

SELECT tag, qty, firstname, lastname, spelarid, position from (
SELECT s1 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ' GROUP BY tag
UNION
SELECT s2 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
UNION
SELECT s3 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
UNION
SELECT s4 AS tag, count(1) AS qty FROM matchmal  where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
UNION
SELECT s5 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
UNION
SELECT s6 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
) temp LEFT JOIN spelare ON spelare.spelarid = tag
WHERE tag != 23 AND qty > 1
ORDER BY qty DESC

上面的查询返回 fe

标签 37 (Robin Johansson) 总值应为“21”，标签 28 Sebastian Borgs 总值应为“17”等。

我试过 GROUP BY spelarid 和标签，但它并没有加起来总数。

标签： mysqlsql

解决方案

您可以取消透视和计数。我想你想要这样的东西：

SELECT m.tag, COUNT(s.spelareid)
FROM (SELECT (CASE n WHEN 1 THEN s1 WHEN 2 THEN w2
                     WHEN 3 THEN s3 WHEN 4 THEN w4
                     WHEN 5 THEN s5 WHEN 6 THEN w6
              END) AS tag
      FROM matchmal m CROSS JOIN
           (SELECT 1 as n UNION ALL
            SELECT 2 as n UNION ALL
            SELECT 3 as n UNION ALL
            SELECT 4 as n UNION ALL
            SELECT 5 as n UNION ALL
            SELECT 6 as n
           ) n
      WHERE 23 IN (s1, s2, s3, s4, s5, s6) AND
            m.goal = 0 AND
            m.lage = 'EQ'
     ) m LEFT JOIN
     spelare s
     ON m.tag = s.spelareid
GROUP BY m.tag

推荐阅读