使用ROLLUP和ROLLUP删除冗余字段值

Question

我有4个Tables设置，我必须使用GROUP BY方法：

‘加班费’表

ot_id | user_id |       datefrom      |       dateto        
------+---------+---------------------+---------------------
  1   |     3   | 2015-12-21 17:00:00 | 2015-12-21 19:30:00 
------+---------+---------------------+---------------------
  9   |     1   | 2015-12-21 17:00:00 | 2015-12-21 19:00:00 
------+---------+---------------------+---------------------
  10  |     1   | 2015-12-21 17:00:00 | 2015-12-28 18:00:00 
------+---------+---------------------+---------------------
  11  |    45   | 2015-12-21 17:00:00 | 2015-12-21 19:00:00
------+---------+---------------------+---------------------
  12  |    45   | 2015-12-22 17:00:00 | 2015-12-22 18:30:00
------+---------+---------------------+---------------------
  13  |     4   | 2015-12-21 17:00:00 | 2015-12-21 19:00:00
------+---------+---------------------+---------------------
  14  |     4   | 2015-12-21 17:00:00 | 2015-12-21 18:00:00
------+---------+---------------------+---------------------
  15  |     1   | 2015-12-22 04:00:00 | 2015-12-22 08:00:00
------+---------+---------------------+---------------------
  16  |    55   | 2015-12-23 17:00:00 | 2015-12-23 19:00:00
------+---------+---------------------+---------------------
  17  |    56   | 2015-12-23 17:00:00 | 2015-12-23 19:00:00
------+---------+---------------------+---------------------
  18  |    56   | 2015-12-24 17:00:00 | 2015-12-24 18:30:00

'users‘Table

user_id | dept_id | loc_id
--------+---------+---------
    1   |    2    |   58
--------+---------+---------
    3   |    2    |   58
--------+---------+---------
    4   |    2    |   58
--------+---------+---------
    45  |    2    |   58
--------+---------+---------
    55  |    1    |   58
--------+---------+---------
    56  |    1    |   47

'location‘表

loc_id | location
-------+----------------
   47  | DAGUPAN
-------+----------------
   58  | ADMINISTRATION

‘部门’表

dept_id | department_name
--------+-----------------
    1   | ACCOUNTING
--------+-----------------
    2   | MIS

我的问题是：

SELECT l.location, d.department_name,
        COALESCE(l.loc_id,'TOTAL') as loc_id,

((LEFT(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')) - TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(datefrom, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')))), 2) * 60)

+

SUBSTRING(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')) - TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(datefrom, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')))), 4, 2)) / 60

AS totalHrs


FROM `overtime` AS o
INNER JOIN `users` AS u ON o.user_id=u.user_id
INNER JOIN `location` AS l ON u.loc_id=l.loc_id
INNER JOIN `department` AS d ON u.dept_id=d.dept_id

GROUP BY l.loc_id, d.dept_id WITH ROLLUP

给我这个：

​

​

我想要的是这样的：

|----------------|-----------------|-----------------|--------------------------|
|--- location ---|--- department---|--- totalHrs ----|--- over all totalHrs ----|
|----------------|-----------------|-----------------|--------------------------|
|     DAGUPAN    |   ACCOUNTING    |       3.5       |                          |
|----------------|-----------------|-----------------|--------------------------|
|      (NULL)    |     (NULL)      |       TOTAL     |            3.5           |
|----------------|-----------------|-----------------|--------------------------|
| ADMINISTRATION |   ACCOUNTING    |        2        |                          |
|----------------|-----------------|-----------------|--------------------------|
|      (NULL)    |      MIS        |        16       |                          |
|----------------|-----------------|-----------------|--------------------------|
|      (NULL)    |      (NULL)     |       TOTAL     |             18           |
|----------------|-----------------|-----------------|--------------------------|

是否可以删除location字段的冗余循环？另外，我还想创建一个名为column的新over all totalHrs，并使用WITH ROLLUP获取总价值。

提前感谢！

编辑的

找到这个SQL Fiddle，它只是一个不同的表结构。但这正是我想要的。

Answer 1

不知道你要在哪用这个。因此，我假设它用于使用编程环境创建的某种报告。进一步阅读为什么这是重要的。

1. 从查询中删除loc_id似乎没有效果，那么如果您需要它，为什么要保留它呢？
2. 移除卷取 选择((LEFT(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto，'%Y-%m-%d %H:%i:%s')，‘%Y-%m- %H:%i:%s')) (((LEFT(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto，’%Y-%m-%m-%d%H：%i：%s‘))(DATE_FORMAT(STR_TO_DATE(从日期开始，'%Y-%m-%d %H:%i:%s')，(%Y-%m-%d %H:%i:%s')) ( 2) * 60) + SUBSTRING(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto，'%Y-%m-%d %H:%i:%s')，'%Y-%m-%d %H：%i：%s‘)-TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE)，(‘%Y-%m- %H:%i:%s')，'%Y-%m-%d %H：%i：%s’)，4，2)/ 60从totalHrs作为o内连接，users作为u在o.user_id=u.user_id内连接上，location作为l在u.loc_id=l.loc_id内连接，department在u.dept_id=d.dept_id上。

使用我使用的数据(与您的数据不同)生成：

location    department_name totalHrs
Dagupan accounting  7
administration  accounting  2.5
administration  mis 5
administration  bod 5.5

因此，您想要的汇总将在每次更改位置时添加行(将它们相加在总时数行中)。

给你以下内容：

location    department_name totalHrs
Dagupan accounting  7
Dagupan accounting  7
administration  accounting  2.5
administration  mis 5
administration  bod 5.5
administration  bod 13
administration  bod 20 -----This row is the total of the extra rows added 7 and 13

如果不希望将其作为输出，则始终可以省略with rollup选项，并使用编程技术来显示由此查询生成的行，并将它们汇总到一个单独的变量中，以便稍后显示它们。

这是我用来测试的木琴。

如果您可以添加获得输出所需的重要字段和数据，如果您不使用编程环境，我可以更进一步。

在您的更新之后，我创建了以下内容：

    select case when rownum = 1 then location else null end location,
case when rownum = 1 then department_name else 'total' end department_name,totalHrs
from
(select *,
@row:=(case when @prev=loc_id and @predept=dept_id 
           then @row else 0 end) + 1 as rownum,
    @prev:=loc_id ploc_id,
    @predept:=dept_id pdept_id
from
(
SELECT l.location, d.department_name,
((LEFT(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')) - TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(datefrom, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')))), 2) * 60)
+
SUBSTRING(SEC_TO_TIME(SUM(TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(dateto, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')) - TIME_TO_SEC(DATE_FORMAT(STR_TO_DATE(datefrom, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')))), 4, 2)) / 60
AS totalHrs,u.dept_id,u.loc_id
FROM `overtime` AS o
INNER JOIN `users` AS u ON o.user_id=u.user_id
INNER JOIN `location` AS l ON u.loc_id=l.loc_id
INNER JOIN `department` AS d ON u.dept_id=d.dept_id
GROUP BY l.loc_id, d.dept_id with rollup ) src) src2

新小提琴

给你一个额外的：

替换

case when rownum = 1 then department_name else 'total' end department_name,

使用

case when rownum = 1 then department_name else concat(location,' total') end department_name,