SQL 循环遍历表以计数和分组

Question

我有一张这样的 table

 ID|list
  1|:A:B:C:
  2|:B:D:A:
  3|:C:A:D:

我想计算每个 A、B、C、D 的数量并将它们分组为

A|3
B|2
C|2
D|2

我正在考虑遍历表并将值从计数语句插入到临时表中，如下所示

select count(*) from table where list like %:A:%

它看起来确实很丑。有没有更好的方法来做到这一点？

(我正在使用来自 qt 的 sqlite)

Answer 1

永远，永远，永远不要在一列中存储多个值!更好的数据库设计将是

movies table
----------
id
name
release_date
...


actors table
------------
id
first_name
last_name
birth_date
...


movie_cast table
----------------
movie_id
actor_id

然后，您可以像这样选择特定电影的所有 Actor

select a.lastName, a.first_name
from actors a
join movie_cast c on c.actor_id = a.id
join movies m on c.movie_id = m.id
where m.name = 'Citizen Kane'

或者像这样选择特定 Actor 出演的所有电影

select m.name
from actors a
join movie_cast c on c.actor_id = a.id
join movies m on c.movie_id = m.id
where a.lastName ='Wayne'
and a.first_name 'John'