MySQL选择相似度最大的行

Question

我有一张桌子，如：

id | val1 | val2 | val3 | val4 | val5

如何找到最大计数相等的行(并非所有的值都相等)。

示例:说我在表中有4行：

1 | Mercedes | E | black | 250hp | 270kmh | 
2 | Mercedes | C | white | 250hp | 240kmh | 
3 | BMW      | C | white | 250hp | 250kmh | 
4 | PORCHE   | E | red   | 300hp | 290kmh | 

我选择：

val1=PORCHE val2=E val3=red val4=250 val5=270 

并得到：

1 | Mercedes | E | black | 250hp | 270kmh | 
4 | PORCHE   | E | red   | 300hp | 290kmh | 

因为两者都有三个相等的字段。

也不是关于汽车的问题，我希望用一张桌子来解决这个问题。

关于检查用户硬件并比较它是否绝对相等或有多少相等的表格。

Answer 1

这是MySql 8.0+的一种解决方案，它使用rank()窗口函数返回最匹配的带有领带的行：

with cte as (
  select *,
    rank() over (order by
      (val1 = 'PORSCHE') + 
      (val2 = 'E') + 
      (val3 = 'red') + 
      (val4 = '250hp') + 
      (val5 = '270km') desc 
    ) rn
  from tablename  
) 
select * from cte
where rn = 1

见演示。

这将适用于以前的版本，但不会返回领带，这只是第一次最好的比赛：

select *
from tablename 
order by 
      (val1 = 'PORSCHE') + 
      (val2 = 'E') + 
      (val3 = 'red') + 
      (val4 = '250hp') + 
      (val5 = '270km') desc 
limit 1

见演示。

Answer 2

我在这里用以下示例数据模型在本地重新创建了您的案例：

CREATE TABLE `cars` (
  `id` int(11) NOT NULL,
  `val1` varchar(45) DEFAULT NULL,
  `val2` varchar(45) DEFAULT NULL,
  `val3` varchar(45) DEFAULT NULL,
  `val4` varchar(45) DEFAULT NULL,
  `val5` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `cars` (`id`, `val1`, `val2`, `val3`, `val4`, `val5`) VALUES ('1', 'Mercedes', 'E', 'black', '250hp', '270kmh');
INSERT INTO `cars` (`id`, `val1`, `val2`, `val3`, `val4`, `val5`) VALUES ('2', 'Mercedes', 'C', 'white', '250hp', '240kmh');
INSERT INTO `cars` (`id`, `val1`, `val2`, `val3`, `val4`, `val5`) VALUES ('3', 'BMW', 'C', 'white', '250hp', '250kmh');
INSERT INTO `cars` (`id`, `val1`, `val2`, `val3`, `val4`, `val5`) VALUES ('4', 'PORSCHE', 'E', 'red', '300hp', '270kmh');

为了获得你的“相似投票”，你可以这样做：

select id, count(*) as votes from
(
    select id from cars where val1 = 'PORSCHE'
    union all
    select id from cars where val2 = 'E'
    union all 
    select id from cars where val3 = 'red'
    union all
    select id from cars where val4 = '250hp'
    union all
    select id from cars where val5 = '270kmh'
) as votes
group by id

使用您的测试数据，这将生成如下内容：

id  votes
1   3
2   1
3   1
4   4

现在来了棘手的部分:我们只想要“最大的选票”(最佳匹配)。这里的挑战是，我们需要在以上两次进行此选票查询:一次提取最大值，第二次用于确定与最大票数相关的ids。如果您只想要“第一次最佳匹配”，您可以使用order by votes desc limit 1。如果您想获得“拥有最高票数的所有ids”，那么您可以这样做：

select * from (
    select id, count(*) as votes from
    (
        select id from cars where val1 = 'PORSCHE'
        union all
        select id from cars where val2 = 'E'
        union all 
        select id from cars where val3 = 'red'
        union all
        select id from cars where val4 = '250hp'
        union all
        select id from cars where val5 = '270kmh'
    ) as votes
    group by id
) hits where votes = (
    select max(votes) from (
        select id, count(*) as votes from
        (
            select id from cars where val1 = 'PORSCHE'
            union all
            select id from cars where val2 = 'E'
            union all 
            select id from cars where val3 = 'red'
            union all
            select id from cars where val4 = '250hp'
            union all
            select id from cars where val5 = '270kmh'
        ) as votes
        group by id
    ) as hits
)

不幸的是，这重复了选择查询(还需要计算两次)。关于如何在SQL只选择列上最大值的行。上最好地解决这样的问题，有大量的讨论。

在您的例子中，我还会考虑将“相似投票”写到临时表 (如果您希望比较多行的话)。这是否合适取决于应用程序具有何种类型的数据库访问。

Answer 3

根据描述，我认为这将是最简单的解决方案：

select t.*
from (select t.*,
             rank() over (order by (t.val1 = @val1) + (t.val2 = @val2) + (t.val3 = @val3) + (t.val4 = @val4) + (t.val5 = @val5) desc) as seqnum
      from t
     ) t
where seqnum = 1;

在版本8之前的MySQL版本中，这稍微复杂一些，但没有那么糟糕：

select t.*
from t
where (t.val1 = @val1) + (t.val2 = @val2) + (t.val3 = @val3) + (t.val4 = @val4) + (t.val5 = @val5)
     ) = (select max( (t2.val1 = @val1) + (t2.val2 = @val2) + (t2.val3 = @val3) + (t2.val4 = @val4) + (t2.val5 = @val5) )
          from t t2
         );