我有两个表product
和specs
,如果至少有一个规范与WHERE匹配,我想获得产品和一个group_concat(所有规范)。这是我目前所拥有的,但它只返回一个匹配WHERE的规范。
select p.ID, p.name, p.manufacturer GROUP_CONCAT(s.specValue order by s.pID,',')
from product as p
JOIN spec AS s ON p.ID = s.pID
WHERE s.specValue = 'micro'
group by p.ID
product table
| ID | name | manufacturer |
| 1 | Iphone | apple |
| 2 | galaxy | samsung |
| 3 | note | samsung |
------------------------------
spec table
| ID | pID | specName | specVlaue |
| 1 | 1 | charger | bad |
| 2 | 2 | charger | micro |
| 3 | 2 | keypad | touch |
| 4 | 4 | charger | micro |
-----------------------------------
您可以使用以下在WHERE
子句中使用IN
的内容:
select p.ID,
p.name,
p.manufacturer,
GROUP_CONCAT(s.specValue order by s.pID,',') AllSpecs
from product as p
JOIN spec AS s
ON p.ID = s.pID
WHERE p.ID in (select pID
from spec s1
where s1.specValue = 'micro')
group by p.ID
请参阅SQL Fiddle with Demo。
或者您可以使用EXISTS
:
select p.ID,
p.name,
p.manufacturer,
GROUP_CONCAT(s.specValue order by s.pID,',') AllSpecs
from product as p
JOIN spec AS s
ON p.ID = s.pID
WHERE exists (select pID
from spec s1
where s1.specValue = 'micro'
and p.ID = s1.pid)
group by p.ID
请参阅带有演示的SQL Fiddle
两者都给出了结果:
| ID | NAME | MANUFACTURER | ALLSPECS |
--------------------------------------------
| 2 | galaxy | samsung | touch,micro |
如果您不想使用子查询,可以使用HAVING
子句来筛选值为的记录
select p.ID,
p.name,
p.manufacturer,
GROUP_CONCAT(s.specValue order by s.pID,',') AllSpecs
from product as p
JOIN spec AS s
ON p.ID = s.pID
group by p.ID
having GROUP_CONCAT(s.specValue order by s.pID,',') like '%micro%'
请参阅带有演示的SQL Fiddle