mysql - 使用 GROUP BY 和 WITH ROLLUP 删除冗余字段值

Question

我有 4 个 Table 设置，我必须使用 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

'用户'表:

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

'位置'表:

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字段的冗余循环？我还想创建一个名为 over all totalHrs 的新 column 并使用 WITH ROLLUP

获取总值

提前致谢!

EDITED

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

Answer 1

不确定您要在哪里使用它。所以我假设它是针对您将使用编程环境创建的某种报告。进一步阅读为什么这很重要。

1. 从您的查询中删除 loc_id 似乎不会影响它，所以如果您需要它，为什么要保留它呢？

2. 删除汇总

   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
   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
   

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

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 选项并使用您的编程技术来显示此查询生成的行并将它们汇总到一个单独的变量中以便稍后显示它们。

这是 sqlfiddle我曾经测试过。

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

在你更新后我创建了这个:

    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

new fiddle

给你一点额外的:

替换

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,