使用多个列的*条目的SQL查询

I’ve got a Sqlite Database I want to query. My basic SQL Knowledge enables me to query for most I need but I’m stuck here:

我有一个Sqlite数据库，我想查询。我的SQL基础知识使我能够查询我需要的大部分内容，但我被困在这里:

The table looks like this (simplified!):

这个表格看起来是这样的(简化了!)

| Name            | Year | Title   | Musician 1 | Musician 2  |
| --------------- | ---- | ------- | ---------- | ----------- |
| Doe, Jon        | 2007 | Title 1 | Beatles    | The Stooges |
| May, Peter      | 2001 | Title 2 |            | Beatles     |
| Schmidt, Andrea | 1997 | Title 3 | Nick Cave  |             |

I’d like to be able to query for Top Musician in the 2000s and get an output like

我希望能够查询21世纪的*音乐家，并获得类似的输出

| Name        | Count |
| ----------- | ----- |
| Beatles     | 2     |
| The Stooges | 1     |

How would you archieve this? Thanks!

你怎么把它存档?谢谢!

2 个解决方案

#1

If I correct understood, you need this

如果我理解对了，你需要这个

select  count(*), name from (
    select Musician_1 as Name from table where Year >= 2000
    union all 
    select Musician_2 as Name from table where Year >= 2000
) t
group by Name 
order by count(*) desc

#2

you could use an union all

你可以使用联合

  select musician, count(*) from (

      select year, musician_1 as musician
      from my_table 
      union  all

      select year, musician_2
      from my_table 
  ) t 
  where t.year between 2000 and 2010
  group by t.musician

#1