在mysql中清除查询透视表中的空值

vjhs03f7 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(421)

我在mysql引擎版本5.x中使用了以下数据集作为示例

+-----------+------------+
| name      | occupation |
+-----------+------------+
| Samantha  | Doctor     |
| Julia     | Actor      |
| Maria     | Actor      |
| Meera     | Singer     |
| Ashely    | Professor  |
| Ketty     | Professor  |
| Christeen | Professor  |
| Jane      | Actor      |
| Jenny     | Doctor     |
| Priya     | Singer     |
+-----------+------------+

我想透视表中的职业列，以便每个名称按字母顺序排序，并显示在其相应的职业下面。输出列标题应分别为doctor、professor、singer和actor。
我试试这个

SELECT CASE occupation WHEN 'Doctor' THEN name END AS Doctor,
       CASE occupation WHEN 'Professor' THEN name END AS Professor,
       CASE occupation WHEN 'Singer' THEN name END AS Singer,
       CASE occupation WHEN 'Actor' THEN name END AS Actor
FROM occupations;

输出

+----------+-----------+--------+-------+
| Doctor   | Professor | Singer | Actor |
+----------+-----------+--------+-------+
| NULL     | NULL      | NULL   | Jane  |
| NULL     | NULL      | NULL   | Julia |
| NULL     | NULL      | NULL   | Maria |
| NULL     | NULL      | Meera  | NULL  |
| NULL     | NULL      | Priya  | NULL  |
| NULL     | Ashely    | NULL   | NULL  |
| NULL     | Christeen | NULL   | NULL  |
| NULL     | Ketty     | NULL   | NULL  |
| Jenny    | NULL      | NULL   | NULL  |
| Samantha | NULL      | NULL   | NULL  |
+----------+-----------+--------+-------+

但是我想要这个输出

Doctor   Professor  Singer Actor
Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

我忙得不可开交，任何帮助都将不胜感激。

sql mysql pivot pivot-table

来源：https://stackoverflow.com/questions/61897219/clean-nulls-from-query-pivot-table-in-mysql

1条答案

按热度按时间

hjzp0vay1#

这有点棘手。您需要聚合，但没有可聚合的内容。为此，您需要一个行号：

SELECT MAX(CASE occupation WHEN 'Doctor' THEN name END) AS Doctor,
       MAX(CASE occupation WHEN 'Professor' THEN name END) AS Professor,
       MAX(CASE occupation WHEN 'Singer' THEN name END) AS Singer,
       MAX(CASE occupation WHEN 'Actor' THEN name END) AS Actor
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY occupation) as seqnum
      FROM occupations o
     ) o
GROUP BY seqnum;

这是一把db<>小提琴，说明了上述方法的有效性。
编辑：
在旧版本中，可以使用相关子查询：

SELECT MAX(CASE occupation WHEN 'Doctor' THEN name END) AS Doctor,
       MAX(CASE occupation WHEN 'Professor' THEN name END) AS Professor,
       MAX(CASE occupation WHEN 'Singer' THEN name END) AS Singer,
       MAX(CASE occupation WHEN 'Actor' THEN name END) AS Actor
FROM (SELECT o.*,
             (SELECT COUNT(*)
              FROM occupations o2
              WHERE o2.occupation = o.occupation AND
                    o2.name <= o.name
             ) as seqnum
      FROM occupations o
     ) o
GROUP BY seqnum;

赞(0）回复(0）举报 2021-08-09

我来回答

在mysql中清除查询透视表中的空值

1条答案

相关问题

热门标签

最新问答