SQL中的设置差异

Question

我有以下查询，该查询为我提供了一个标题在其中可用的商店的group_concat：

http://sqlfiddle.com/#!9/e52b53/1/0

我目前使用的查询给了我以下信息：

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

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

我想添加一个额外的列，为我提供以下(硬编码)存储之间的设置差异：("iTunes", "Google", "Amazon")。然后，正确的查询将给出：

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

我该怎么做？

Answer 1

你可以和所有商店一起坐一张桌子。我将把它们列在子查询中，但最好是实际创建这样一个表：

select     title.id, 
           title.name, 
           group_concat(distinct store order by store),
           group_concat(distinct nullif(stores.name, store) order by stores.name)
from       version
inner join title 
        on version.title_id=title.id 
cross join (select 'iTunes' as name union
            select 'Google' union
            select 'Amazon') as stores
left join  version_price 
        on version_price.version_id=version.id
       and version_price.store = stores.name
group by   title.id

SQL小提琴

请注意，现在使用外部联接加入version_price是很重要的：

这是因为inner join将从存储子查询中消除不匹配的值。对于没有发生的left join，version_price的字段表示为null，而对于特定的stores.name，在version_price中没有匹配。

因此，在进行分组之前，您实际上拥有更多的记录，但是这些附加的null值并不会对第一个group_concat做出贡献。然而，他们，而且只有这些人，对第二个问题作出了贡献。

改进数据库模型

更好的设计是将存储列在引用表中，按名称进行索引。或者更好的是，也给这些商店一个id，并重新设计您的version_price表，使用store_id作为外键，而不是store (名称)。

Answer 2

子查询怎么样？

select title.id, title.name, group_concat(distinct v1.store order by v1.store), 
(select group_concat(distinct v3.store order by v3.store)
             from version_price v3 where v3.store not in 
             (select distinct v2.store from version_price v2                                                               
              where v2.version_id = v.id)
            )
from version v inner join title on title_id=title.id 
inner join version_price v1 on v1.version_id=v.id 
group by title_id

小提琴版本在这里：http://sqlfiddle.com/#!9/af9e98/7

Answer 3

http://sqlfiddle.com/#!9/e52b53/45

既然商店名称是硬编码的，为什么不做这样的事情呢？至少它没有在查询执行计划中添加额外的联接。

select 
  title.id, 
  title.name, 
  group_concat(distinct store order by store) as ins,
  concat_ws(
    ',',
    if(group_concat(distinct store order by store) like '%Amazon%', NULL, 'Amazon'),
    if(group_concat(distinct store order by store) like '%Google%', NULL, 'Google'),
    if(group_concat(distinct store order by store) like '%iTunes%', NULL, 'iTunes')
  ) as not_on
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