尝试匹配GROUP_CONCAT中的多个值 [英] Try to match multiple values that are in GROUP_CONCAT

问题描述

options_table

options_id    | object_id  |   option_value
=========================================
1            |    1       |    drink
2            |    2       |    ice
3            |    1       |    bath
4            |    2       |    soda
5            |    2       |    drink
6            |    3       |    ice
7            |    4       |    bath
8            |    2       |    bath
9            |    1       |    storm


object_table

object_id    |   object_name
=============================
1            |    sun
2            |    moon
3            |    mars
4            |    jupiter

查询

SELECT object_table.object_name GROUP_CONCAT(options_table.option_value ) as object_options 
FROM options_table
LEFT JOIN object_table
ON object_table.object_id = options_table.object_id    
GROUP BY options_table.object_id

所以我得到类似

object_name | object_options
=========================================
moon        | ice, soda, drink, bath
sun         | drink, bath, storm
mars        | ice
jupiter     | bath

可以说用户希望所有具有选项"drink"和选项"bath"的对象.所以我只会得到这个结果.

Lets say the user want all objects that has option "drink" AND option "bath". So i only get this result.

object_name | object_options
=========================================
moon        | ice, soda, drink, bath
sun         | drink, bath, storm

我如何编辑查询才能获得此结果?

How do i have to edit the query to get this result?

推荐答案

最简单的方法: http ://www.sqlfiddle.com/#！2/1b2e3/5

select obj.object_name, 
    group_concat(opt.option_value order by opt.options_id) as object_options
from options_table opt
join object_table obj using(object_id)
group by obj.object_id
having sum(opt.option_value in ('drink','bath')) = 2
order by obj.object_name;

输出:

| OBJECT_NAME |      OBJECT_OPTIONS |
-------------------------------------
|        moon | ice,soda,drink,bath |
|         sun |    drink,bath,storm |

---

如果要使用更多ANSI-SQL方式进行查询，请不要依赖MySQLism(布尔/整数对偶)，而应在SUM上使用显式值: 查看全文