如何做一个子查询GROUP_CONCAT
,我有以下数据模型:如何做一个子查询GROUP_CONCAT
`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但在Google上没有的标题。我将如何做到这一点?以下是我迄今为止:
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
但我怎么构建查询到包括该项目是否是在谷歌(使用CASE语句,Or的任何需要的)
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
使用条件聚合来确定标题是否在指定的商店中。
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
count(case when store = 'google' then 1 end) >= 1
计数所有行对于给定标题分配1
到在他们google
行之后。 (否则他们将被分配null
并且count
忽略空值。)之后,if
检查count
,并且如果标题上至少有一个google
存储,则将其分类。
我会做类似下面
SELECT
*
FROM
title t
INNER JOIN
version v ON
v.title_id = t.id
CROSS APPLY (
SELECT
*
FROM
version_price vp
WHERE
vp.store <> 'google'
) c ON c.version_id == v.id
语法可能只是有点过,因为我不能测试它现在,但我相信这是你想要的精神。交叉申请也是一个非常有效的加入,它总是有帮助的!
我从来没有听说过交叉申请。这对mysql有效吗?另外,你可以在这里的sqlfiddle上试试看它是否有效:http://sqlfiddle.com/#!9/e52b53/1/0 – David542
给我几分钟,我会回应! – NewDeveloper
在调查之后,我没有在MySQL中看到任何“Cross Apply”。对于那个很抱歉!我想这个解决方案只适用于MSSQL和SQL。 – NewDeveloper
这会给你不是谷歌版本价格的数量,而是谷歌上的数字。 (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
可以省略,因为如果没有提供ELSE它被暗示;但为了清楚起见,我包括在内
这可能是最没有效率的上述答案的,但下面的子查询会的工作,使用%like%
条件:
select *, case when stores like '%google%' then 1 else 0 end on_google
from (select title.id, title.name, group_concat(distinct store order by store) stores
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) x
id name stores on_google
1 Titanic Google,iTunes 1
2 Avatar iTunes 0
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;
'where store 'google''?除了“不同”的东西外,并不是group_concat的工作是为你过滤东西。过滤在'where'子句中完成。 –
你期望得到什么? '1 |泰坦尼克号Google,iTunes'或者只是'1 |泰坦尼克号iTunes'? – Alex
@MarcB请看更新的问题。 – David542