代码之家 › 专栏 › 技术社区 › Kirzilla

SQL:查找行并根据匹配列的数目排序?

mysql sql

Kirzilla · 技术社区 · 16 年前

让我们想象一下,我们有一张结构如此简单的“汽车”桌……

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

首先,我选择的是汽车(1,黑色,重型,豪华轿车),然后我想得到相关的汽车清单,按匹配列的数量排序(没有任何列的重量)。所以,首先我希望看到(黑色,重型,豪华轿车)汽车,然后我希望看到只有2个匹配领域的汽车等。

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

对不起,我的英语,但我真的希望我的问题对你来说是清楚的。

谢谢您。

4 回复 | 直到 7 年前

Joe Lloyd 16 年前

可能有几种方法可以优化子查询,但不使用 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

Tomalak 16 年前

不是效率太高,但是…

SELECT
  exact.car_id   AS e_car_id, exact.color   AS e_color, 
  exact.weight   AS e_weight, exact.type    AS e_type,
  related.car_id AS r_car_id, related.color AS r_color, 
  related.weight AS r_weight, related.type  AS r_type,
  CASE WHEN related.color = exact.color      THEN 1 ELSE 0 END
   + CASE WHEN related.weight = exact.weight THEN 1 ELSE 0 END
   + CASE WHEN related.type = exact.type     THEN 1 ELSE 0 END
  AS rank
FROM
  cars AS exact
  INNER JOIN cars AS related ON (
    related.car_id <> exact.car_id 
    AND CASE WHEN related.color = exact.color   THEN 1 ELSE 0 END
      + CASE WHEN related.weight = exact.weight THEN 1 ELSE 0 END
      + CASE WHEN related.type = exact.type     THEN 1 ELSE 0 END
    >= 1
  )
WHERE
  exact.car_id = 1 /* black, heavy, limo */
ORDER BY
  rank DESC

这不会在大型数据集上运行得很快,因为join和order by都不能使用索引。很可能存在一个更优化的版本。

我的测试设置的输出如下:

e_car_id  e_color  e_weight  e_type  r_car_id  r_color  r_weight  r_type  rank
1         black    heavy     limo    7         black    heavy     limo    3
1         black    heavy     limo    2         black    light     limo    2
1         black    heavy     limo    3         black    heavy     van     2
1         black    heavy     limo    4         black    medium    van     1
1         black    heavy     limo    5         blue     light     limo    1

Brent Foxwell 7 年前

我知道这是一个老问题,但是你应该能够用括号括住一个表达式来计算它。

SELECT   *           
FROM     `cars`
WHERE    `color` = "black"
   OR    `weight` = "heavy"
   OR    `type` = "limo"
ORDER BY (   (`color` = "black")
           + (`weight` = "heavy")
           + (`type` = "limo") 
         ) DESC

括号内的每个表达式如果为真,则等于1;如果为假,则等于0;因此,其和将是匹配的数目。

Ian 16 年前

mysql> select * from cars;
+--------+-------+--------+-------+
| car_id | color | weight | type  |
+--------+-------+--------+-------+
|      1 | black | light  | van   |
|      2 | black | light  | sedan |
|      3 | black | light  | limo  |
|      4 | black | medium | van   |
|      5 | black | medium | sedan |
|      6 | black | medium | limo  |
|      7 | black | heavy  | van   |
|      8 | black | heavy  | sedan |
|      9 | black | heavy  | limo  |
|     10 | white | light  | van   |
|     11 | white | light  | sedan |
|     12 | white | light  | limo  |
|     13 | white | medium | van   |
|     14 | white | medium | sedan |
|     15 | white | medium | limo  |
|     16 | white | heavy  | van   |
|     17 | white | heavy  | sedan |
|     18 | white | heavy  | limo  |
|     19 | blue  | light  | van   |
|     20 | blue  | light  | sedan |
|     21 | blue  | light  | limo  |
|     22 | blue  | medium | van   |
|     23 | blue  | medium | sedan |
|     24 | blue  | medium | limo  |
|     25 | blue  | heavy  | van   |
|     26 | blue  | heavy  | sedan |
|     27 | blue  | heavy  | limo  |
+--------+-------+--------+-------+
27 rows in set (0.00 sec)

select *,
(case
  when color = 'black' and weight = 'heavy' and type = 'limo'
    then 3
  when ( color = 'black' and type = 'limo') or 
      (color = 'black' and weight = 'heavy') or 
       (weight = 'heavy' and type = 'limo')
    then 2
  else 1
end) sort_order
from cars
where color = 'black' or weight = 'heavy' or type = 'limo'
order by sort_order desc;


+--------+-------+--------+-------+------------+
| car_id | color | weight | type  | sort_order |
+--------+-------+--------+-------+------------+
|      9 | black | heavy  | limo  |          3 |
|     27 | blue  | heavy  | limo  |          2 |
|     18 | white | heavy  | limo  |          2 |
|      8 | black | heavy  | sedan |          2 |
|      7 | black | heavy  | van   |          2 |
|      6 | black | medium | limo  |          2 |
|      3 | black | light  | limo  |          2 |
|     24 | blue  | medium | limo  |          1 |
|     25 | blue  | heavy  | van   |          1 |
|     21 | blue  | light  | limo  |          1 |
|     26 | blue  | heavy  | sedan |          1 |
|     17 | white | heavy  | sedan |          1 |
|     16 | white | heavy  | van   |          1 |
|     15 | white | medium | limo  |          1 |
|     12 | white | light  | limo  |          1 |
|      5 | black | medium | sedan |          1 |
|      4 | black | medium | van   |          1 |
|      2 | black | light  | sedan |          1 |
|      1 | black | light  | van   |          1 |
+--------+-------+--------+-------+------------+
19 rows in set (0.00 sec)