SQL：使颜色表中的颜色可搜索 [英] SQL: Make colors from color-table searchable
问题描述
我有一个包含不同颜色卡片的数据库。
I have a database with cards of different colors.
卡片可以是 无色或一种或多种颜色：
红色， 绿色， 白色， 蓝色， 黑色
A Card can be "Colorless" or one or more colors: "Red","Green","White","Blue","Black"
纸牌在具有 id，名称和其他一些卡片信息，
The cards is in a Card table with id, name and some other card info,
和颜色位于颜色表中，其中名称（例如：红色），颜色代码（例如： r）和颜色 id
and the colors is in a Color table with name(ex:"Red"), a color code(ex:"r") and a color id
还有一个连接表，其中带有 card_id 和 color_id
And there are a Connection table with card_id and color_id
所以问题是？
如何使颜色可搜索？
我希望能够找到所有仅红色 ，
的卡片以及所有红色或蓝色 ，
a的卡片找出所有红色和蓝色 （以及3,4和5种颜色相同！）的卡
I like to be able to find all card that are "only Red",
aswell as all cards that are "Red or Blue",
and all cards that are "Red and Blue" (and the same for 3,4 and 5 colors!)
有30种不同的组合颜色：
There are 30 different combinations of color:
//000001//000010//000011//000100//000101//000110//000111
//001000//001001//001010//001011//001100//001101//001111
//010000//010001//010010//010011//010100//010101//010110
//010111//011000//011001//011010//011011//011100//011101
//011111
//100000 (Colorless)
我发现这对 UNION 是可行的，但
必须将全部30个
I have found out that this is posible with UNION but
one have to make all 30 different combinations and then UNION them all together!
SELECT
c.id
'false' AS Colors,
'true' AS Red,
'false' AS Blue
'false' AS Green
'false' AS White
'false' AS Black
'false' AS Colorless
FROM cards_data AS c
INNER JOIN con_cards_colors AS ccc_red ON c.id = ccc_red.cards_id
INNER JOIN colors AS co_red ON co_red.id = ccc_red.colors_id
WHERE
co_red.name = "Red"
Union
(...)
这似乎是一个不错的解决方案！
This dosnt seem like a good solution!
因此，任何想法者都会会被赞赏吗？
（最好，我喜欢某种SQL视图。。）
(Optimal I like some kind of a SQL View..)
推荐答案
您将在此处使用条件聚合。例如，对于红色和蓝色，您要查找以下卡片
You'd use conditional aggregations here. For Red and Blue for example you want to find cards where
- 两种颜色都存在
- 没有其他颜色颜色存在
这意味着如果我将一张卡片计算为红色和蓝色，我必须得到2。如果我计算所有颜色，我也必须得到2.（与一种，三种或多种颜色相同。）
That means if I count Red and Blue for a card I must get 2. If I count all colors I must also get 2. (Same for one, three or more colors.)
因此，使用此查询，仅更改提到的颜色和颜色数：
So use this query and only change the colors mentioned and the number of colors:
select *
from cards_data where id in
(
select cards_id
from con_cards_colors
group by cards_id
having count(case when colors_id in (select id from colors where name in ('Red','Blue')) then 1 end) = 2 -- i.e. find all
and count(*) = 2 -- i.e. find only those and no others
);
这篇关于SQL：使颜色表中的颜色可搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
