如何在group_concat中进行子查询

Question

我有以下数据模型：

`title`

- id
- name

`version`

- id
- name
- title_id

`version_price`

- id
- version_id
- store
- price

下面是数据的一个例子：

`title`

- id=1, name=titanic
- id=2, name=avatar

`version`

- id=1, name="titanic (dubbed in spanish)", title_id=1
- id=2, name="avatar directors cut", title_id=2
- id=3, name="avatar theatrical", title_id=2

`version_price`

- id=1, version_id=1, store=itunes, price=$4.99
- id=1, version_id=1, store=google, price=$4.99
- id=1, version_id=2, store=itunes, price=$5.99
- id=1, version_id=3, store=itunes, price=$5.99

我想构建一个查询，它将给我所有在iTunes上有version_price但在谷歌上没有的标题。我该怎么做呢？这是我到目前为止所知道的：

select 
    title.id, title.name, group_concat(distinct store order by store)
from
    version inner join title on version.title_id=title.id inner join version_price on version_price.version_id=version.id 
group by 
    title_id

这给了我一个group_concat，它显示了我所拥有的：

id  name    group_concat(distinct store order by store) 
1   Titanic Google,iTunes                               
2   Avatar  iTunes                                       

但是，我该如何构造一个查询来包含该项目是否在Google上(使用case语句或任何需要的语句)呢？

id  name    group_concat(distinct store order by store) on_google 
1   Titanic Google,iTunes                                true
2   Avatar  iTunes                                       false

它基本上是在做一个group_concat LIKE '%google%'，而不是一个普通的where子句。

下面是我所拥有的当前查询的SQL的链接：http://sqlfiddle.com/#!9/e52b53/1/0

Answer 1

使用条件聚合来确定标题是否在指定的存储区中。

select title.id, title.name, group_concat(distinct version_price.store order by store),
if(count(case when store = 'google' then 1 end) >= 1,'true','false') as on_google
from version 
inner join title on version.title_id=title.id 
inner join version_price on version_price.version_id=version.id
group by title.id, title.name

在将1分配给其中包含google的行之后，count(case when store = 'google' then 1 end) >= 1对给定标题的所有行进行计数。(否则将为它们分配null，并且count将忽略空值。)此后，if检查count，如果标题上至少有一个google商店，则对标题进行分类。

Answer 2

这将给你的版本价格不在谷歌上，并在谷歌上的数字。(COUNT不计算空值。)

SELECT t.id, t.name
    , COUNT(DISTINCT vpNotG.id) > 0 AS onOtherThanGoogle
    , COUNT(DISTINCT vpG.id) > 0 AS onGoogle
FROM title AS t
    INNER JOIN version AS v ON t.id=v.title_id 
    LEFT JOIN version_price AS vpNotG 
       ON v.id=vpNotG.version_id
       AND vpNotG.store <> 'Google'
    LEFT JOIN version_price AS vpG 
       ON v.id=vpG.version_id 
       AND vpG.store = 'Google'
GROUP BY t.id

或者使用类似于vkp的另一种解决方案：

SELECT t.id, t.name
   , COUNT(DISTINCT CASE WHEN store = 'Google' THEN vp.id ELSE NULL END) AS googlePriceCount
   , COUNT(DISTINCT CASE WHEN store = 'iTunes' THEN vp.id ELSE NULL END) AS iTunesPriceCount
   , COUNT(DISTINCT CASE WHEN store <> 'Google' THEN vp.id ELSE NULL END) AS nonGooglePriceCount
FROM title AS t
   INNER JOIN version AS v ON t.id = v.title_id
   INNER JOIN version_price AS vp ON v.id = vp.version_id
GROUP BY t.id

注意:可以省略ELSE NULL，因为如果没有提供其他参数，它就是隐含的；但为了清楚起见，我将其包含在内。

Answer 3

http://sqlfiddle.com/#!9/b8706/2

您只需：

SELECT 
    title.id, 
    title.name, 
    group_concat(distinct version_price.store),
    MAX(IF(version_price.store='google',1,0)) on_google
FROM version 
INNER JOIN title 
ON version.title_id=title.id 
INNER JOIN version_price 
ON version_price.version_id=version.id 
GROUP BY title_id;

如果需要过滤记录，则将HAVING添加到查询中：

SELECT 
    title.id, 
    title.name, 
    group_concat(distinct version_price.store),
    MAX(IF(version_price.store='google',1,0)) on_google
FROM version 
INNER JOIN title 
ON version.title_id=title.id 
INNER JOIN version_price 
ON version_price.version_id=version.id 
GROUP BY title_id
HAVING on_google;