select d.department, d.group_name , totalScore from db_scan_score d , 如果department相同的,只有第一行显示department内容,其它行的department显示为空
时间: 2024-02-05 16:14:25 浏览: 21
你可以使用以下语句实现这个需求:
```
SELECT
CASE WHEN
@prev_dept = d.department
THEN ''
ELSE d.department
END AS department,
d.group_name,
d.totalScore,
@prev_dept := d.department
FROM
db_scan_score d,
(SELECT @prev_dept := '') vars
ORDER BY
d.department,
d.group_name;
```
这里使用了一个变量 `@prev_dept` 来保存前一行的部门名称,如果当前行的部门名称与前一行相同,则将 `department` 列的值设为空字符串,否则将 `department` 列的值设为当前行的部门名称。这样就可以只在第一行显示部门名称。注意要根据部门名称和分组名称进行排序。
相关问题
select d.department, d.group_name , totalScore from db_scan_score d , 如果department相同的,只有第一行显示department内容,其它行的department显示为空
您可以使用 `CASE WHEN` 语句来实现您的需求,示例如下:
```
SELECT CASE
WHEN @prevDept = d.department THEN ''
ELSE d.department
END AS department,
d.group_name,
totalScore
FROM db_scan_score d
ORDER BY d.department, d.group_name
```
其中,`@prevDept` 是一个变量,用于存储上一行的 department 值。在查询结果中,如果当前行的 department 值与上一行不同,则显示当前行的 department 值,否则显示空值。同时,根据 department 和 group_name 字段进行排序,以保证相同 department 的行在一起显示。
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 没有显示为空,请帮忙修改下
可以将第二行的 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,从而不会在结果集中显示。