select if(@dept = d.department, '', d.department) as department, d.group_name , sum(d.score) totalScore from db_scan_score d where d.iterate = '20230420' group by d.department,d.group_name order by d.department ,d.group_name desc; 这条sql第二行相同的department 没有显示为空,请帮忙修改下
时间: 2024-02-06 20:10:22 浏览: 25
可以将第二行的 IF 函数中的空字符串 '' 修改为 NULL,如下所示:
```
select if(@dept = d.department, NULL, d.department) as department, d.group_name, sum(d.score) totalScore
from db_scan_score d
where d.iterate = '20230420'
group by d.department, d.group_name
order by d.department, d.group_name desc;
```
这样,当当前行的 department 和上一行的 department 相同时,IF 函数会返回 NULL,从而不会在结果集中显示。
相关问题
SELECT a.s_id, @i:=@i+1 AS i, -- @k:=(case when @score=a.sum_score then @k else @i end) as rank, a.sum_score AS score FROM (SELECT s_id,SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC)a, (SELECT @k:=0,@i:=0,@score:=0)s
This is a MySQL query that selects the student ID (s_id), assigns a sequential number to each row (i), and calculates the rank of each student based on their sum of scores (sum_score).
The query uses a subquery to first group the scores by student ID and calculate the sum of scores for each student. This subquery is then joined with a variable initialization subquery that sets the initial values of @k, @i, and @score to 0.
The variable @k is used to keep track of the current rank while iterating over the rows. The variable @i is used to assign a sequential number to each row. The variable @score is used to compare the sum_score of the current row with the sum_score of the previous row.
The CASE statement is used to check if the sum_score of the current row is equal to the sum_score of the previous row. If they are equal, then the rank remains the same. If they are not equal, then the rank is updated to the current sequential number.
Here is a breakdown of the query:
```
SELECT
a.s_id, -- Select the student ID
@i:=@i+1 AS i, -- Assign a sequential number to each row
@k:=(case when @score=a.sum_score then @k else @i end) as rank, -- Calculate the rank
a.sum_score AS score -- Select the sum of scores for each student
FROM
(SELECT s_id,SUM(s_score) AS sum_score FROM score GROUP BY s_id ORDER BY sum_score DESC) a, -- Subquery to calculate sum of scores for each student
(SELECT @k:=0,@i:=0,@score:=0) s -- Subquery to initialize variables
```
Note that the use of variables in this query is not recommended, as it can lead to unexpected results if the variables are not reset properly. It is better to use a subquery or a window function to calculate the rank.
Oracle 下SQL语句怎么写 SELECT @ROWNUM := @ROWNUM + 1 AS TEMPRANK, @INCRNUM := CASE WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM END AS FILERANK, OBJ.* FROM ( SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE, U.USER_NAME AS AUTHORNAME, CONCAT( U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END ) AS USERNAME, IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) AS YLCOUNT FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru WHERE DOCINFO.VALID_FLAG = '1' AND IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) != 0 AND U.USER_ID = DOCINFO.AUTHOR_ID AND sys_stru.STRU_ID = U.DEPARTMENT_ID AND O.ORGAN_ID = sys_stru.ORGAN_ID GROUP BY DOCINFO.DOC_ID, U.USER_NAME ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC ) OBJ, ( SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0 ) R
在 Oracle 中,可以将该 SQL 语句写成如下形式:
```
SELECT TEMPRANK, FILERANK, TITLE, DOCID, DOCTYPE, AUTHORNAME, USERNAME, YLCOUNT
FROM (
SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE,
U.USER_NAME AS AUTHORNAME, CONCAT(U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END) AS USERNAME,
IFNULL(DOCINFO.DOWNLOAD_NUM, 0) AS YLCOUNT,
@ROWNUM := @ROWNUM + 1 AS TEMPRANK,
@INCRNUM := CASE
WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM
WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM
END AS FILERANK
FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru
WHERE DOCINFO.VALID_FLAG = '1'
AND IFNULL(DOCINFO.DOWNLOAD_NUM, 0) != 0
AND U.USER_ID = DOCINFO.AUTHOR_ID
AND sys_stru.STRU_ID = U.DEPARTMENT_ID
AND O.ORGAN_ID = sys_stru.ORGAN_ID
GROUP BY DOCINFO.DOC_ID, U.USER_NAME
ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC
) OBJ, (SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0) R;
```
在 Oracle 中,变量需要在 SQL 语句外面进行声明和初始化。这里使用了 `@ROWNUM`、`@ROWTOTAL` 和 `@INCRNUM` 三个变量,它们都在 SQL 语句的最后一行进行了声明和初始化。
此外,由于 Oracle 对于 SQL 语句的执行顺序与 MySQL 略有不同,因此需要将临时变量的计算放在子查询中,并使用外层查询来获取这些变量的值。最终的结果集是从子查询中返回的。