V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
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
wuhang89
V2EX  ›  MySQL

mysql 查询语句书写疑问

  •  1
     
  •   wuhang89 · 2015-09-02 17:27:45 +08:00 · 4468 次点击
    这是一个创建于 3130 天前的主题,其中的信息可能已经有所发展或是发生改变。

    +-------+------------+-----+-------+------+---------+
    | 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%的语句?求教了

    14 条回复    2015-09-03 17:55:53 +08:00
    lichao
        1
    lichao  
       2015-09-02 17:42:15 +08:00
    补基础知识啊

    ..... 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
        2
    wuhang89  
    OP
       2015-09-02 17:52:14 +08:00
    @lichao
    你这个不对,因为一张图片是包含多个颜色的,我如果筛选的话肯定是要同时满足两个条件的,而你的语句只是满足一个条件的查询,不过还是很感谢你。
    lichao
        3
    lichao  
       2015-09-02 17:59:20 +08:00
    @wuhang89 没明白你描述的是什么
    qiayue
        4
    qiayue  
       2015-09-02 18:07:00 +08:00
    那就把一楼的 or 改成 and
    bestsanmao
        5
    bestsanmao  
       2015-09-02 18:07:45 +08:00
    @lichao
    楼主的意思应该是这样
    它一个图片可能由几个记录进行描述( picture_id 相同表示代表同一个图片)
    每条记录的意思是 这个颜色值的点在像素总数中的百分比

    想要构建的查询是 比如 红色像素占 20%同时绿色像素点 10%的图片 ID 都有哪些
    luban
        6
    luban  
       2015-09-02 18:07:51 +08:00
    @lichao 他的意思是图片一个点颜色满足 a ,另一个点颜色满足 b ,他这个图片有 id 来标识是否一张图片
    @wuhang89 可以使用左连接,表自身和自身进行连接查询,表 1 满足 a 颜色,表 2 满足 b 颜色,图片 id 来连接两个表
    qiayue
        7
    qiayue  
       2015-09-02 18:08:43 +08:00
    终于明白了,比如你的图片 ID 是 5232723 在表里记录了好几个颜色
    你要找的是符合多个颜色的同一个图片 ID
    skydiver
        8
    skydiver  
       2015-09-02 18:09:12 +08:00
    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
        9
    skydiver  
       2015-09-02 18:09:44 +08:00
    table 是表名字。
    qiayue
        10
    qiayue  
       2015-09-02 18:10:33 +08:00
    如果不用表连接的话,也可以按照一楼的方案先把数据查询出来,然后用程序来做处理
    darluc
        11
    darluc  
       2015-09-03 00:09:01 +08:00
    如果没有重复数据的话,
    把一楼的改改就好了
    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
        12
    wuhang89  
    OP
       2015-09-03 11:32:08 +08:00
    我想了下,目前这种数据结构 mysql 是查不出来的,你可以查两个颜色,那么多个颜色查询呢?我现在尝试用 elasticsearch 进行查询。
    realpg
        13
    realpg  
       2015-09-03 17:06:20 +08:00   ❤️ 1
    @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
        14
    wuhang89  
    OP
       2015-09-03 17:55:53 +08:00
    @realpg
    你好,您这种搜索,如果两个颜色速度还凑合,如果我要搜索 10 个颜色的呢?因为现实生活中照片存在 10 个以上的颜色是很正常的,那么你如何去构建呢?我这边已经做出来了,我使用的是 elasticsearch 进行查询,效果和速度我非常满意, mysql 还有一个麻烦的地方是比较 hsv 空间距离的时候也非常不好计算。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1712 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 16:42 · PVG 00:42 · LAX 09:42 · JFK 12:42
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.