mysql 2使用groupby rollup设置sum

Question

我有一个问题

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

查询运行得很好。但我还需要找到更改了group by order的第二个和(小时)。

select user_id,sum(hours),date, task_id from table where used_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*/

根据您的需要，您应该只需要一个或两个with rollup。