| id | a | b | c | d | e |
|---|---|---|---|---|---|
| 1 | a1 | b1 | c1 | d1 | e1 |
| 2 | a2 | b2 | c2 | d2 | e2 |
我想要匹配 a-e 来查询列，有 5 个字段能匹配到 3 就行，比如，a=a1 and b=b2 and c=c1 and d=d2 and e=e1,因为 id=1 能匹配到 3 个字段就能查到，请问大佬，sql 怎么写？ 不知道表达得是否清楚
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 1488 days ago, the information mentioned may be changed or developed.
 |
1
akira Aug 29, 2022
初级写法，直接上 条件组合
(a = a1 and b = b1 and c = c1 ) or (a = a1 and b = b1 and d = d1) or (a = a1 and b = b1 and d = e1) or |
|
2
akira Aug 29, 2022  2
中级写法，先分别 计算 每一行的 a-e 是否和你输入的一致，然后汇总看结果
select * , `cal_a` + cal_b + cal_c + cal_d + cal_e `cal` from ( select id , if(a = a1, 1, 0) `cal_a`, if(b = b1, 1, 0) `cal_b`, if(c = c1, 1, 0) `cal_c`, if(d = d1, 1, 0) `cal_d`, if(e = e1, 1, 0) `cal_e`, ... ) a where `cal` >=3 |
 |
3
wxf666 Aug 29, 2022
这样？这要求 a b c d e 都为 NOT NULL ，且这会扫全表
select * from ... where (a=a1) + (b=b2) + (c=c1) + (d=d2) + (e=e1) >= 3 |
 |
5
xuanbg Aug 30, 2022
无论怎么写 SQL ，全表扫描跑不了。
|
 |
6
laqow Aug 30, 2022
如果 abcde 都有索引一楼应该比较好吧
|
Select Language