SQL：查找行并根据匹配列的数量排序？

Question

假设我们有一个具有如此简单结构的“汽车”表…

car_id INT
color ENUM('black','white','blue')
weight ENUM('light','medium','heavy')
type ENUM('van','sedan','limo')

拳头，我选择的是汽车（1，黑色，重型，豪华轿车），然后我想获取相关汽车的列表，这些列表按匹配列的数量排序（没有任何列的权重）。所以，首先我期望看到（黑色，重型，豪华轿车）汽车，然后我期望看到只有2个匹配字段的汽车，等等。

是否可以使用SQL执行这种排序？

对不起，我的英语，但我真的希望我对您的问题很清楚。

谢谢你。

Answer 1

可能有几种方法可以优化子查询，但是不使用case语句或次优联接子句：

select
        *
    from
        (
            select
                    selection.CarId,
                    selection.Colour,
                    selection.Weight,
                    selection.Type,
                    3 as Relevance
                from
                    tblCars as selection
                where
                    selection.Colour = 'black' and selection.Weight = 'light' and selection.Type = 'van'
            union all
            select
                    cars.CarId,
                    cars.Colour,
                    cars.Weight,
                    cars.Type,
                    count(*) as Relevance
                from
                    tblCars as cars
                inner join
                    (
                        select
                                byColour.CarId
                            from
                                tblCars as cars
                            inner join
                                tblCars as byColour
                            on
                                cars.Colour = byColour.Colour
                            where
                                cars.Colour = 'black' and cars.Weight = 'light' and cars.Type = 'van'
                                and
                                byColour.CarId <> cars.CarId
                        union all
                        select
                                byWeight.CarId
                            from
                                tblCars as cars
                            inner join
                                tblCars as byWeight
                            on
                                cars.Weight = byWeight.Weight
                            where
                                cars.Colour = 'black' and cars.Weight = 'light' and cars.Type = 'van'
                                and
                                byWeight.CarId <> cars.CarId
                        union all
                        select
                                byType.CarId
                            from
                                tblCars as cars
                            inner join
                                tblCars as byType
                            on
                                cars.Type = byType.Type
                            where
                                cars.Colour = 'black' and cars.Weight = 'light' and cars.Type = 'van'
                                and
                                byType.CarId <> cars.CarId
                    ) as matches
                on
                    cars.CarId = matches.CarId
                group by
                    cars.CarId,
                    cars.Colour,
                    cars.Weight,
                    cars.Type
        ) as results
    order by
        Relevance desc

输出：

CarId   Colour  Weight  Type    Relevance
1       black   light   van     3
3       white   light   van     2
4       blue    light   van     2
5       black   medium  van     2
6       white   medium  van     1
7       blue    medium  van     1
8       black   heavy   limo    1