由许多表组成

I have the following table:

我有下表：

performance
  --id
  --color
  --installs
  --date

performance_groups
  --id
  --performance_id
  --group_id

I would like to have an SQL something like this:

我想要一个像这样的SQL：

 SELECT color, targeting_id, SUM(installs) as installs
 FROM performance, performance_groups
GROUP BY color, group_id

But I would like the grouping to be done for all groups.

但我希望为所有群体进行分组。

For example:

例如：

performance
id     color     installs   date
1      Blue      5          2017-07-05
2      Red       10         2017-07-04
3      Blue      10         2017-07-04
4      Blue      10         2017-07-05

performance_groups
id   performance_id   group_id
1    1                1
2    1                2
3    2                3
4    3                1
5    3                2
6    4                1
7    4                3

I would like to get results like this:

我想得到这样的结果：

color group_ids installs
Blue  1,2     15
Red   3       10
Blue  1,3     10

4 个解决方案

#1

Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

切勿在FROM子句中使用逗号。始终使用正确，明确的JOIN语法。

Your query would then seem to be a JOIN and GROUP BY:

您的查询似乎是JOIN和GROUP BY：

select p.color, string_agg(pg.group_id) as groups, 
       sum(installs) as installs
from performance p join
     performance_groups pg
     on pg.performance_id = p.id
group by color;

#2

Use array_agg and do not forget about the distinct. The join may produce duplicates.

使用array_agg并且不要忘记distinct。联接可能会产生重复。

select p.color, array_agg(distinct pg.group_id) as groups, 
       sum(distinct installs) as installs
from performance p 
join performance_groups pg
     on pg.performance_id = p.id
group by color;

sqlfiddle demo

sqlfiddle演示

#3

We are using STUFF,XML PATH and GROUP BY in SQL 2008.

Code :

代码：

    select f.color,
    (SELECT STUFF(( SELECT ',' + convert(varchar(5),j.group_id) 
    FROM (select distinct g.group_id from performance_groups g 
    where g.performance_id in (select id from performance where color= 
    f.color)) j FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, 
    '')) as group_ids,
    SUM(f.installs) installs
    from performance f group by color

OUTPUT :

输出：

color   group_ids   installs
Blue    1,2         15
Red     3           10

#4

Thanks to Gordon and Radium I got an inspiration to my answer.

感谢Gordon和Radium，我的答案得到了启发。

The final solution is:

最终的解决方案是：

with pg as(
    select performance_id, array_agg(distinct group_id) as groups from performance_groups
    group by performance_id
) 
select groups, sum(installs) from performance join pg 
on pg.performance_id = performance.id
group by groups

See working sqlfiddle

见工作sqlfiddle

#1