Mysql将标签设置为Rollup或类似的GROUPING函数，如sql server

I have a problem with the ROLLUP, I have rows with null values, and the ROLLUP also returns null, how do I difference between the null values of the ROLLUP and the null values of the row?

我有一个ROLLUP的问题,我有空值的行,ROLLUP也返回null,我如何区分ROLLUP的空值和行的空值?

The null in the rows exist because the column (group_name) is associated with a left join.

存在行中的null,因为列(group_name)与左连接相关联。

Here is my query:

这是我的查询:

SELECT gr.info,  
  HOUR(cdr.calldate) AS hour,     
  DATE(cdr.calldate) AS date,     
  COUNT(DISTINCT cdr.uniqueid) AS count,  
  pl.number,  
  IFNULL(ugr.group_name, "") AS group_name 
FROM cdr 
INNER JOIN callinfo AS ci 
  ON ci.uniqueid = cdr.uniqueid 
LEFT JOIN users AS usr 
  ON usr.username = ci.agent 
LEFT JOIN groups AS ugr 
  ON ugr.group_id = usr.group_id 
INNER JOIN pstnline AS pl 
  ON ci.line = pl.number 
INNER JOIN hunt_line AS gri 
  ON gri.pstnline_id = pl.pstnline_id 
INNER JOIN hunt AS gr 
  ON gri.hunt_number = gr.number 
WHERE cdr.calldate >='2012-12-01 00:00'
  AND cdr.calldate <='2013-01-24 10:45'
GROUP BY group_name WITH ROLLUP

I see that in SQL Server exist a function called GROUPING, but in MySql doesn't exist, how can i achieve the same effect?

我看到在SQL Server中存在一个名为GROUPING的函数,但是在MySql中不存在,我怎样才能达到同样的效果?

1 个解决方案

#1

I think you can also do this in the query that you have, by changing the group by argument to:

我认为您也可以在您拥有的查询中执行此操作,方法是将group by参数更改为:

group by ifnull(ugr.group_name, '')

Now, blanks will indicate NULLs from the outer join and NULLs will indicate rollup.

现在,空格将从外部联接指示NULL,而NULL将指示汇总。

#1