加入后选择汇总 [英] Selecting rollup after join
问题描述
我有2个查询,每个查询都有汇总.
I have 2 queries with roll up in each one..
SELECT DATE(date) AS day,
COUNT(IF(name = 'red', 1, NULL)) AS "red",
COUNT(IF(name = 'blue', 1, NULL)) AS "blue",
COUNT(IF(name = 'yellow', 1, NULL)) AS "yellow"
FROM test1
GROUP BY day with rollup
SELECT DATE(date) AS day,
COUNT(*) AS total
FROM test2
GROUP BY day with rollup
加入它们时,rollup
行将被删除,因此我通过使用另一个查询计算了rollup
,并在结果的末尾union
对其进行了解决
When joining them the rollup
row gets removed so I solved it by using another query calculating the rollup
and union
it to the end of the result
这是一个实时演示 [sqlfiddle] ,关于我的方式的任何想法我这样做(例如最佳实践或性能),如果有一种方法可以在加入后保留rollup
,我不知道.任何指针表示赞赏.
Here's a live demo [sqlfiddle], any thoughts about the way I'm doing this (like best practice or performance), if there's a way to keep the rollup
after join, that I don't know of. Any pointers appreciated.
我想要的正确结果集是小提琴中的结果.每行包含一天的摘要,从第一个查询的项目/颜色数开始,到第二个查询,从当天的请求数开始.汇总将是每个项目/颜色和请求的分类总数.希望我说清楚了:)
the correct result set I want is the result in the fiddle. each row contains a summary of one day, from the 1st query number of items/colors and from the 2nd query a number of requests on that day. the rollup would be the categorized total of each item/color and requests. hope I made it clear :)
推荐答案
我不认为汇总"会被删除.结果行根本不符合join
条件.您可以尝试以下方法:
I don't think the "rollup" gets removed. The resulting rows simply don't match the join
conditions. You could try this:
select t1.*, t2.total
from (SELECT date(date) as day, count(IF(name = 'red', 1, NULL)) AS "red",
sum(name = 'blue') AS blue,
sum(name = 'yellow') AS yellow
FROM test1
group by day with rollup
) t1 join
(select date(date) as day, count(*) as total
from test2
group by day with rollup
) t2
on t1.day = t2.day or t1.day is null and t2.day is null;
这篇关于加入后选择汇总的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!