如何从连接的MySQL表中选择数据？

Question

我基本上有两个表：

A(id int, name varchar(10), info varchar(10))
B(id int, item varchar(10))

A
1 A Hello
2 B World
3 C Foo

B
1 apple
1 orange
1 hammer
2 glass
2 cup

使用以下查询...

mysql> SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
       FROM a
       LEFT OUTER JOIN b ON (a.id=b.id)
       GROUP BY 1,2,3;

我得到了期望的输出：

+------+------+-------+------------------------------------+
| id   | name | info  | group_concat(b.item separator ' ') |
+------+------+-------+------------------------------------+
|    1 | A    | Hello | apple orange hammer                | 
|    2 | B    | World | glass cup                          | 
|    3 | C    | Foo   | NULL                               | 
+------+------+-------+------------------------------------+

我现在想修改查询以获取一个结果表，其中包含相同的信息，但仅限以字母o开头的项，(像'o%')如何获得以下输出？

+------+------+-------+------------------------------------+
| id   | name | info  | group_concat(b.item separator ' ') |
+------+------+-------+------------------------------------+
|    1 | A    | Hello | apple orange hammer                |                        
+------+------+-------+------------------------------------+

请注意，我仍然希望呈现整个字符串'apple orange hammer'，而不仅仅是'orange'。

- Ed Taylor

Answer 1

SELECT  a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ') AS bs
FROM    a
LEFT OUTER JOIN
        b
ON      b.id = a.id
GROUP BY
        id, name, info
HAVING   
        bs RLIKE '[[:<:]]o'

或者这样（更具索引效率）：

SELECT  a.id, a.name, a.info,
        (
        SELECT  GROUP_CONCAT(b.item SEPARATOR ' ') AS bs
        FROM    b
        WHERE   b.id = a.id
        ) AS bs
FROM    (
        SELECT  DISTINCT b.id
        FROM    b
        WHERE   item LIKE 'o%'
        ) q
JOIN    a
ON      a.id = q.id

如果您在 b (item, id) 上创建了一个组合索引，那么后面的查询速度会更快。

- Quassnoi

Answer 2

SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
FROM a
LEFT OUTER JOIN b ON (a.id=b.id)
WHERE a.id in (select id from b where upper(SUBSTRING(b.item,0,1)) = 'O')
GROUP BY 1,2,3

- afftee

Answer 3

另一种方法是：

SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ') AS items
   FROM a
   LEFT OUTER JOIN b ON (a.id=b.id)
   GROUP BY 1,2,3
   HAVING items REGEXP '[[:<:]]o';

- outis

Answer 4

SELECT * FROM
  (SELECT a.id, a.name, a.info, group_concat(b.item SEPARATOR ' ')
  FROM a
  LEFT OUTER JOIN b ON (a.id=b.id)
  GROUP BY 1,2,3)
WHERE a.id IN (SELECT bb.id FROM b AS bb WHERE name like 'o%');

我怀疑这个表现良好，但对我来说似乎很合理。

- dlamblin

Answer 5

在使用group by之后，您需要使用having来对group_concat上的like运算符进行操作。

Group By 1,2,3
HAVING group_concat(b.item SEPARATOR ' ') like ('%o%')

请注意，我只是在寻找任何需要调整类似语句以匹配您要求的人。

- Jeff Beck