SQL:查找行并根据匹配列的数量排序? [英] SQL : find rows and sort according to number of matching columns?

问题描述

让我们想象一下，我们有一个具有如此简单结构的汽车"桌子...

Let's imagine that we are having "cars" table with such a simple structure...

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

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

Fist, I'm selecting car (1, black, heavy, limo), then I'd like to get list of related cars sorted by number of matching columns (without any column weight). So, first I'm expecting to see (black, heavy, limo) cars, then I'm expecting to see cars with only 2 matching fields etc.

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

Is it possible to execute this kind of sorting using SQL?

对不起，我的英语，但是我真的希望我的问题对你很清楚.

Sorry for my English, but I really hope that my question is clear for you.

谢谢.

推荐答案

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

There are probably a few ways to optimise the sub-queries, but without using case statements or sub-optimal join clauses:

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

这篇关于SQL:查找行并根据匹配列的数量排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！