mysql - 在 SQL 查询/子查询中同时选择匹配和不匹配的结果

Question

我正在尝试查找与短裤颜色最相似的商品(相对于它们所拥有的颜色总数)。我可以轻松地首先选择匹配计数，然后为每个匹配项目分别选择总计数然后除以，但我更愿意尽可能在一个语句中执行此操作，这样可以最大限度地减少最终数百个(如果不是的话)数以千计的查询。

开始查询:

SELECT clothes,
        COUNT(clothes.id) AS matches
 FROM clothes
    AND clothes.item!='shorts' 
    AND clothes.colors IN (
        SELECT clothes.colors 
        FROM clothes 
        WHERE item='shorts' 
        DESC LIMIT 5000
    ) 

 GROUP BY item ORDER BY matches DESC

表格设置:

id  | item    | colors
=========================
1   | shorts  | red 
2   | shorts  | blue 
3   | shorts  | green
4   | pants   | red 
5   | pants   | blue
6   | pants   | white
7   | shirts  | red 
8   | shirts  | blue 
9   | shirts  | gray 
10  | shirts  | orange 
11  | shirts  | purple 
12  | shirts  | black 
13  | shirts  | white 
14  | shirts  | tan 
15  | shirts  | fuscia 

目标输出

Item   | Matches | Total | Ratio
==================================
pants  | 2       | 3     | 2/3
shirts | 2       | 9     | 2/9

关于如何在单个查询语句中执行此操作的任何想法？

Answer 1

总计，使用计数。要仅对匹配项求和，请将 CASE 与 SUM 结合使用:

SELECT clothes.Item, SUM(
        CASE WHEN clothes.
                colors IN (
                    SELECT clothes.
                        colors
                    FROM clothes
                    WHERE item = 
                        'shorts' DESC 
                        LIMIT 5000
                    ) THEN 1 ELSE 0 END) 
    Matches, COUNT(clothes.
        id) AS matches, CAST(SUM
        (CASE WHEN clothes.
                    colors IN (
                        SELECT clothes
                            .colors
                        FROM clothes
                        WHERE item = 
                            'shorts' 
                            DESC LIMIT 
                            5000
                        ) THEN 1 ELSE 0 
                END) AS VARCHAR) + 
    '/' + CAST(COUNT(clothes.
            id) AS VARCHAR) AS 
    Ratio
FROM clothes clothes.item != 
    'shorts'