加入后选择汇总 [英] 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屋！