Project个
| project_id | project_name |
|---|---|
| 1 | Crispy |
| 2 | Smile |
| 3 | Genie |
Artist个
| artist_id | artist_name |
|---|---|
| 1 | Lisa Simpson |
| 2 | John Smith |
| 3 | Sally Winter |
Credit个
| credit_id | credit_name |
|---|---|
| 1 | Producer |
| 2 | Vocalist |
| 3 | Writer |
| 4 | Mixer |
Song个
| song_id | song_name | project_id |
|---|---|---|
| 23 | Hello | 1 |
| 12 | Goodie | 3 |
| 30 | Butterfly | 2 |
| 55 | Lollipop | 1 |
Remix个
| remix_id | remix_name | project_id |
|---|---|---|
| 10 | Hello (Extended Mix) | 1 |
| 22 | Goodie (Bad Remix) | 3 |
| 31 | Butterfly (Sugar Remix) | 2 |
| 45 | Lollipop (Radio Mix) | 1 |
Credit_To_Artist个
| autoid | credit_id | artist_id | song_id | remix_id |
|---|---|---|---|---|
| 1 | 1 | 1 | 23 | null |
| 1 | 1 | 1 | null | 10 |
| 1 | 1 | 1 | 55 | null |
| 1 | 1 | 1 | null | 45 |
| 1 | 3 | 2 | 23 | null |
| 1 | 3 | 3 | 12 | null |
| 1 | 3 | 3 | 30 | null |
| 1 | 1 | 1 | 30 | null |
| 1 | 1 | 1 | null | 31 |
我想要显示一个按字母顺序的项目列表，其中显示了有多少歌曲或修复特定的艺术家计数.
The output I am wanting to achieve is this:(艺人ID=1)
| artist_id | project_id | project_name | songcount | remixcount | total |
|---|---|---|---|---|---|
| 1 | 1 | Crispy | 2 | 2 | 4 |
| 1 | 2 | Smile | 1 | 1 | 2 |
哪一个查询就能实现这一结果？我已经try 了下面的查询，它可以很好地获得歌曲总数，但它不能用于混音计数，我不确定如何将这些加在一起得到总数.
提前谢谢您.
SELECT a.artist_id, p.project_id, p.project_name,
COUNT(DISTINCT c2a.song_id) as songcount,
COUNT(DISTINCT c2a.remix_id) as remixcount
FROM `Project` p
LEFT JOIN `Song` s ON s.project_id = p.project_id
LEFT JOIN `Remix` rm ON rm.project_id = p.project_id
LEFT JOIN `Credit_To_Artist` c2a ON c2a.song_id = s.song_id
INNER JOIN `Artist` a ON a.artist_id = c2a.artist_id
WHERE c2a.artist_id = 1
GROUP BY p.project_id
ORDER BY p.project_name ASC