如何使用 SQLite 将值转入列中? [英] How do I pivot values into columns with SQLite?

问题描述

我用以下代码制作了一个名为 tbl 的表格:

I made a table called tbl with this code:

CREATE TABLE tbl
    (
      `Year` int, 
      `Album` varchar(255),
      `Artist` varchar(255),
      `Label` varchar(255),
      `Genre` varchar(255),
      `id` int
    )
;

INSERT INTO tbl
    (
      `Year`,
      `Album`,
      `Artist`,
      `Label`,
      `Genre`,
      `id`
    )
VALUES
    (1990, "Greatest Hits", "The Best", "Least Def", "hip hop", 123),
    (1990, "Greatest Hits", "The Best", "Roofless", "hip hop", 123),
    (1990, "4-Boyz", "3 Guyz", "Pacific", "pop-dance", 23),
    (1990, "4-Boyz", "3 Guyz", "Atlantic", "pop-dance", 23)
;

我想运行一个查询来显示每年流派的数量，不要因为 Label 列而重复计算.我想要这个:

I want to run a query to show me the count of genres for each year, without double counting because of the Label column. I want this:

Year, hip hop, pop-dance
1990, 1, 1

我必须运行什么查询才能得到我想要的?

What query must I run to get what I want?

推荐答案

因为你不能使用 pivot，所以你可以这样做.

Because you can't use pivot, you can do this.

select year,
count(distinct case when `Genre` = 'hip hop' then 1 end) as hiphop,
count(distinct case when `Genre` = 'pop-dance' then 1 end) as popdance
from tbl
group by year

这篇关于如何使用 SQLite 将值转入列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！