mysql 2组使用groupby rollup求和

Question

我有一个查询

select user_id,sum(hours),date, task_id from table whereused_id = 'x' and date >='' and date<= '' group by user_id, date, task_id with roll up< /code>

查询工作正常。但我还需要找到第二个总和（小时），其中按顺序分组发生了变化。

select user_id,sum(hours),date,task_id from table whereused_id = 'x' group by user_id,task_id

（实际的where条件要长得多。）

是否有可能同时获得总和单个查询自where条件几乎相同？

Answer 1

SELECT * FROM (
  SELECT 1 AS list_id  
    , user_id
    , sum(hours) AS total_hours
    , `date`
    , task_id 
  FROM table WHERE used_id = 'x' AND `date` BETWEEN @thisdate AND @thatdate 
  GROUP BY user_id, `date`, task_id /*WITH ROLLUP*/
UNION ALL
  SELECT 2 AS list_id 
    , user_id
    , sum(hours) AS total_hours
    , `date`
    , task_id 
  FROM table 
  WHERE used_id = 'x' 
  GROUP BY user_id,task_id WITH ROLLUP ) q
/*ORDER BY q.list_id, q.user_id, q.`date`, q.task_id*/

根据您的需求，您应该只需要一个或两个带有汇总的。