mysql 查询语句书写疑问

+-------+------------+-----+-------+------+---------+
| id | picture_id | red | green | blue | percent |
+-------+------------+-----+-------+------+---------+
| 25495 | 5232689 | 80 | 76 | 73 | 57 |
| 25497 | 5232689 | 151 | 146 | 140 | 44 |
| 25421 | 5232695 | 160 | 109 | 65 | 46 |
| 25423 | 5232695 | 225 | 195 | 152 | 55 |
| 25371 | 5232723 | 22 | 19 | 24 | 63 |
| 25377 | 5232723 | 35 | 32 | 37 | 29 |
| 25375 | 5232723 | 87 | 75 | 79 | 7 |
| 25373 | 5232723 | 186 | 109 | 114 | 3 |
| 25323 | 5232729 | 17 | 16 | 19 | 12 |
| 25321 | 5232729 | 28 | 26 | 29 | 60 |
| 25313 | 5232729 | 40 | 37 | 41 | 13 |
| 25319 | 5232729 | 89 | 85 | 88 | 3 |
| 25317 | 5232729 | 146 | 142 | 142 | 2 |
| 25315 | 5232729 | 236 | 230 | 220 | 12 |
| 25257 | 5232731 | 15 | 13 | 18 | 26 |
| 25271 | 5232731 | 23 | 20 | 25 | 46 |
| 25265 | 5232731 | 34 | 30 | 35 | 20 |
| 25273 | 5232731 | 62 | 55 | 59 | 3 |
| 25269 | 5232731 | 99 | 176 | 138 | 2 |
| 25267 | 5232731 | 102 | 98 | 101 | 2 |
+-------+------------+-----+-------+------+---------+
数据结构如上，存储的是相关图片颜色的比值，如何去构建颜色筛选的语句，就好比颜色=（ 10,23,40 ）比值=40%同时颜色=（ 110 ， 120 ， 130 ）比值=60%的语句？求教了

语句

颜色

比值

MySQL

14 replies • 2015-09-03 17:55:53 +08:00

lichao

Sep 2, 2015

补基础知识啊

..... where (
( red = 10 and green = 23 and blue = 40 and percent = 40 )
or
( red = 110 and green = 120 and blue = 130 and percent = 60 )
)

wuhang89

Sep 2, 2015

@lichao
你这个不对，因为一张图片是包含多个颜色的，我如果筛选的话肯定是要同时满足两个条件的,而你的语句只是满足一个条件的查询，不过还是很感谢你。

lichao

Sep 2, 2015

@wuhang89 没明白你描述的是什么

qiayue

Sep 2, 2015

那就把一楼的 or 改成 and

bestsanmao

Sep 2, 2015

@lichao
楼主的意思应该是这样
它一个图片可能由几个记录进行描述（ picture_id 相同表示代表同一个图片）
每条记录的意思是这个颜色值的点在像素总数中的百分比

想要构建的查询是比如红色像素占 20%同时绿色像素点 10%的图片 ID 都有哪些

luban

Sep 2, 2015

@lichao 他的意思是图片一个点颜色满足 a ，另一个点颜色满足 b ，他这个图片有 id 来标识是否一张图片
@wuhang89 可以使用左连接，表自身和自身进行连接查询，表 1 满足 a 颜色，表 2 满足 b 颜色，图片 id 来连接两个表

qiayue

Sep 2, 2015

终于明白了，比如你的图片 ID 是 5232723 在表里记录了好几个颜色
你要找的是符合多个颜色的同一个图片 ID

skydiver

Sep 2, 2015

select A.picture_id from table A join table B on B.picture_id = A.picture_id where (
( A.red = 10 and A.green = 23 and A.blue = 40 and A.percent = 40 )
and
( B.red = 110 and B.green = 120 and B.blue = 130 and B.percent = 60 )
)

skydiver

Sep 2, 2015

table 是表名字。

qiayue

Sep 2, 2015

如果不用表连接的话，也可以按照一楼的方案先把数据查询出来，然后用程序来做处理

darluc

Sep 3, 2015

如果没有重复数据的话，
把一楼的改改就好了
select count (*) as cnt
..... where (
( red = 10 and green = 23 and blue = 40 and percent = 40 )
or
( red = 110 and green = 120 and blue = 130 and percent = 60 )
)
group by picture_id having cnt = 2;

wuhang89

Sep 3, 2015

我想了下，目前这种数据结构 mysql 是查不出来的，你可以查两个颜色，那么多个颜色查询呢？我现在尝试用 elasticsearch 进行查询。

realpg

Sep 3, 2015

@wuhang89 没啥查不出来的。只是性能很差很难优化。
我把你的模型再抽象一下，因为你这还有 rgbp 四个参数，我直接精简成一个 color 方便测试和演示

注意， V2EX 有防注入和防跨站会把括号什么的加一些空格导致语法问题，如果要自己测试请自行修复 SQL 语句

表结构和基础数据：

CREATE TABLE IF NOT EXISTS `test_color` (
`id` int (10 ) unsigned NOT NULL AUTO_INCREMENT,
`picid` int (10 ) unsigned NOT NULL,
`color` int (10 ) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `test_color` (`id`, `picid`, `color`) VALUES
(1, 1, 3 ),
(2, 1, 5 ),
(3, 2, 4 ),
(4, 2, 6 ),
(5, 2, 3 ),
(6, 3, 5 ),
(7, 3, 7 ),
(8, 4, 7 ),
(9, 4, 3 ),
(10, 4, 5 ),
(11, 4, 2 );

建立的需求模型即是要找出同时有 color:3 和 color:5 的 picid ，需求基本跟楼主原来的模型一致吧？楼主的只是需要同时找出 r g b p 四个参数，需要多写一堆 and ， select 后面变量要多列一堆

select distinct picid
from (select a.id as aid,a.color as acolor,a.picid as picid,b.id as bid,b.color as bcolor from test_color a
join test_color b on a.picid=b.picid where a.id<>b.id ) c
where acolor=5 and bcolor=3

收工

wuhang89

Sep 3, 2015

@realpg
你好，您这种搜索，如果两个颜色速度还凑合，如果我要搜索 10 个颜色的呢？因为现实生活中照片存在 10 个以上的颜色是很正常的，那么你如何去构建呢？我这边已经做出来了，我使用的是 elasticsearch 进行查询，效果和速度我非常满意， mysql 还有一个麻烦的地方是比较 hsv 空间距离的时候也非常不好计算。