MySQL 返回未包含在group by中的列

痛定思痛。 2022-10-27 13:45 1阅读 0赞

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

# 一.需求 #

正在执行一个group by查询，并希望返回哪些属于选择列表而不包含于group by子句的列。  
通常，这是不可能的，因为对于这样的非组列，并不时每行都包含唯一值。

假设要找到每个部门中工资最高和最低的员工，以及每种职位中工资最高和最低的员工，要查看这些人的姓名、所在部门、职位名称以及工资。

希望返回的结果集如下:

±-------±-------±----------±--------±----------------±---------------+  
| deptno | ename | job | sal | dept\_status | job\_status |  
±-------±-------±----------±--------±----------------±---------------+  
| 20 | SCOTT | ANALYST | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |  
| 20 | FORD | ANALYST | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |  
| 10 | MILLER | CLERK | 1300.00 | LOW SAL IN DEPT | TOP SAL IN JOB |  
| 20 | SMITH | CLERK | 800.00 | LOW SAL IN DEPT | LOW SAL IN JOB |  
| 30 | JAMES | CLERK | 950.00 | LOW SAL IN DEPT | NULL |  
| 10 | CLARK | MANAGER | 2450.00 | NULL | LOW SAL IN JOB |  
| 20 | JONES | MANAGER | 2975.00 | NULL | TOP SAL IN JOB |  
| 30 | BLAKE | MANAGER | 2850.00 | TOP SAL IN DEPT | NULL |  
| 10 | KING | PRESIDENT | 5000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |  
| 30 | ALLEN | SALESMAN | 1600.00 | NULL | TOP SAL IN JOB |  
| 30 | WARD | SALESMAN | 1250.00 | NULL | LOW SAL IN JOB |  
| 30 | MARTIN | SALESMAN | 1250.00 | NULL | LOW SAL IN JOB |  
±-------±-------±----------±--------±----------------±---------------+

# 二.解决方案 #

使用内敛视图，按deptno和job找到最高工资和最低工资。然后，只保留工资最高或工资最低的员工。

## 2.1 子查询方法 ##

select  deptno,ename,job,sal,
            case when sal = max_by_dept
                 then 'TOP SAL IN DEPT'
                 when  sal = min_by_dept
                 then 'LOW SAL IN DEPT'
             end as dept_status,
            case when sal = max_by_job
                 then 'TOP SAL IN JOB'
                 when sal = min_by_job
                 then 'LOW SAL IN JOB'
            end as job_status
      from  (
    select  e.deptno,e.ename,e.job,e.sal,
            (select max(sal) from emp d
              where d.deptno = e.deptno) as max_by_dept,
            (select max(sal) from emp d
              where d.job = e.job) as max_by_job,
            (select min(sal) from emp d
              where d.deptno = e.deptno) as min_by_dept,
             (select min(sal) from emp d
               where d.job = e.job) as min_by_job
      from  emp e
            ) x
    where  sal in (max_by_dept,max_by_job,
                   min_by_dept,min_by_job);

测试记录:

mysql> select  deptno,ename,job,sal,
        ->         case when sal = max_by_dept
        ->              then 'TOP SAL IN DEPT'
        ->              when  sal = min_by_dept
        ->              then 'LOW SAL IN DEPT'
        ->          end as dept_status,
        ->         case when sal = max_by_job
        ->              then 'TOP SAL IN JOB'
        ->              when sal = min_by_job
        ->              then 'LOW SAL IN JOB'
        ->         end as job_status
        ->   from  (
        -> select  e.deptno,e.ename,e.job,e.sal,
        ->         (select max(sal) from emp d
        ->           where d.deptno = e.deptno) as max_by_dept,
        ->         (select max(sal) from emp d
        ->           where d.job = e.job) as max_by_job,
        ->         (select min(sal) from emp d
        ->           where d.deptno = e.deptno) as min_by_dept,
        ->          (select min(sal) from emp d
        ->            where d.job = e.job) as min_by_job
        ->   from  emp e
        ->         ) x
        -> where  sal in (max_by_dept,max_by_job,
        ->                min_by_dept,min_by_job);
    +--------+--------+-----------+---------+-----------------+----------------+
    | deptno | ename  | job       | sal     | dept_status     | job_status     |
    +--------+--------+-----------+---------+-----------------+----------------+
    |     20 | SMITH  | CLERK     |  800.00 | LOW SAL IN DEPT | LOW SAL IN JOB |
    |     30 | ALLEN  | SALESMAN  | 1600.00 | NULL            | TOP SAL IN JOB |
    |     30 | WARD   | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
    |     20 | JONES  | MANAGER   | 2975.00 | NULL            | TOP SAL IN JOB |
    |     30 | MARTIN | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
    |     30 | BLAKE  | MANAGER   | 2850.00 | TOP SAL IN DEPT | NULL           |
    |     10 | CLARK  | MANAGER   | 2450.00 | NULL            | LOW SAL IN JOB |
    |     20 | SCOTT  | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
    |     10 | KING   | PRESIDENT | 5000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
    |     30 | JAMES  | CLERK     |  950.00 | LOW SAL IN DEPT | NULL           |
    |     20 | FORD   | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
    |     10 | MILLER | CLERK     | 1300.00 | LOW SAL IN DEPT | TOP SAL IN JOB |
    +--------+--------+-----------+---------+-----------------+----------------+
    12 rows in set (0.00 sec)

## 2.2 MySQL 8.0 窗口函数方法 ##

select  deptno,ename,job,sal,
            case when sal = max_by_dept
                 then 'TOP SAL IN DEPT'
                 when  sal = min_by_dept
                 then 'LOW SAL IN DEPT'
             end as dept_status,
            case when sal = max_by_job
                 then 'TOP SAL IN JOB'
                 when sal = min_by_job
                 then 'LOW SAL IN JOB'
            end as job_status
      from  (
    select  deptno,ename,job,sal,
            max(sal) over w1 as 'max_by_dept',
            max(sal) over w2 as 'max_by_job',
            min(sal) over w1 as 'min_by_dept',
            min(sal) over w2 as 'min_by_job'
      from  emp
     window w1 as (partition by deptno),
            w2 as (partition by job)
            ) x
    where  sal in (max_by_dept,max_by_job,
                   min_by_dept,min_by_job);

测试记录:

mysql> select  deptno,ename,job,sal,
        ->         case when sal = max_by_dept
        ->              then 'TOP SAL IN DEPT'
        ->              when  sal = min_by_dept
        ->              then 'LOW SAL IN DEPT'
        ->          end as dept_status,
        ->         case when sal = max_by_job
        ->              then 'TOP SAL IN JOB'
        ->              when sal = min_by_job
        ->              then 'LOW SAL IN JOB'
        ->         end as job_status
        ->   from  (
        -> select  deptno,ename,job,sal,
        ->         max(sal) over w1 as 'max_by_dept',
        ->         max(sal) over w2 as 'max_by_job',
        ->         min(sal) over w1 as 'min_by_dept',
        ->         min(sal) over w2 as 'min_by_job'
        ->   from  emp
        ->  window w1 as (partition by deptno),
        ->         w2 as (partition by job)
        ->         ) x
        -> where  sal in (max_by_dept,max_by_job,
        ->                min_by_dept,min_by_job);
    +--------+--------+-----------+---------+-----------------+----------------+
    | deptno | ename  | job       | sal     | dept_status     | job_status     |
    +--------+--------+-----------+---------+-----------------+----------------+
    |     20 | SCOTT  | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
    |     20 | FORD   | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
    |     10 | MILLER | CLERK     | 1300.00 | LOW SAL IN DEPT | TOP SAL IN JOB |
    |     20 | SMITH  | CLERK     |  800.00 | LOW SAL IN DEPT | LOW SAL IN JOB |
    |     30 | JAMES  | CLERK     |  950.00 | LOW SAL IN DEPT | NULL           |
    |     10 | CLARK  | MANAGER   | 2450.00 | NULL            | LOW SAL IN JOB |
    |     20 | JONES  | MANAGER   | 2975.00 | NULL            | TOP SAL IN JOB |
    |     30 | BLAKE  | MANAGER   | 2850.00 | TOP SAL IN DEPT | NULL           |
    |     10 | KING   | PRESIDENT | 5000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
    |     30 | ALLEN  | SALESMAN  | 1600.00 | NULL            | TOP SAL IN JOB |
    |     30 | WARD   | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
    |     30 | MARTIN | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
    +--------+--------+-----------+---------+-----------------+----------------+
    12 rows in set (0.00 sec)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb

发表评论取消回复

表情：

评论列表（有 0 条评论，1人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL解决group by分组后未排序问题

MySQL解决group by分组后未排序问题一、遇见问题 1、错误SQL 2、正确SQL --------------------

╰半橙微兮°/ 2024年04月06日 14:20/ 0 赞/ 81 阅读

相关 Sqlserver 在 SELECT 语句中显示来自 GROUP BY 子句中未涉及的列

![在这里插入图片描述][871c6abf292842f2adb4c24b44b0f1d3.jpeg_pic_center] 在SQL Server中，如果您在GROUP B

逃离我推掉我的手/ 2023年10月15日 17:09/ 0 赞/ 34 阅读

相关 MySQL GROUP BY

1. 只会返回分组后的一条结果，但是实际上里面含有的结果不仅有一条 \\![加粗样式][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow

忘是亡心i/ 2023年06月19日 09:44/ 0 赞/ 11 阅读

相关 mysql返回列中值相同的列_如何返回在MySQL中具有相同列值的行

![78a611bf07e5c2f664cab2e38915dd45.png][] 慕村9548890 select Scorefrom tbl awhere a.ID =

小咪咪/ 2023年01月12日 07:49/ 0 赞/ 173 阅读

相关 MySQL 返回未包含在group by中的列

备注:测试数据库版本为MySQL 8.0 如需要scott用户下建表及录入数据语句，可参考: [scott建表及录入数据sql脚本][scott_sql] 一.需求

痛定思痛。/ 2022年10月27日 13:45/ 0 赞/ 2 阅读

相关 MySQL8报SELECT列表的表达式#2不在GROUP BY子句中，并且包含非聚合列

解决办法 vi /etc/my.cnf 在\[mysqld\]后面添加以下内容 [mysqld] sql_mode='STRICT_TRANS_

川长思鸟来/ 2022年10月23日 12:39/ 0 赞/ 52 阅读

相关 mysql中order by与group by的顺序

mysql 中order by 与group by的顺序是： select from where group by order by 注意：group b

本是古典何须时尚/ 2022年08月07日 06:34/ 0 赞/ 5 阅读

相关 MySql中Group By的理解

文章出处：[理解group by和聚合函数][group by] 　　先来看下表1，表名为test： ![162344381367279.jpg][] 表1

爱被打了一巴掌/ 2022年05月20日 09:59/ 0 赞/ 154 阅读

相关 mysql group by order by

这两天让一个数据查询难了。主要是对group by 理解的不够深入。才出现这样的情况这种需求，我想很多人都遇到过。下面是我模拟我的内容表复制代码代码如下:

矫情吗；*/ 2022年04月15日 01:40/ 0 赞/ 208 阅读

相关选择列表中的列 '***' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

今天用SQL Server尝试实现一个SQL语句的时候，报了如标题所示的错误，通过在百度里面搜索，并亲自动手实现，终于发现问题所在，现在把它记录下来。语句如

深藏阁楼爱情的钟/ 2021年11月04日 12:56/ 0 赞/ 194 阅读