我们知道无论是oracle,还是mysql,只要对某列分组,就只能查询分组列或者分组函数列,而对于分组后的整体数据单单靠一个分组函数查询不出来。
在以前开发时,使用的是oracle数据库,比如有很多年的数据,根据年限分组,获取每组最大值,在oracle中可以这样实现:
select *
from (select t.*,
row_number() over(partition by t.dqdm order by t.nf desc) cn --这么理解,按dqdm分组,每组按nf降序,这样row_number() 这列(别名cn) 会按照分好的组,每组都1、2、3,1、2、3的排
from t_sjk_dqmjxx t
where t.scbj = '0')
where cn = '1'--这时取第一个就是按dqdm排序,取每组年份最大的字段值了
但mysql没有类似的函数,需要我们使用用户变量来模拟实现类似的功能:
1.测试数据表结构:
CREATE TABLE `stud` (
`id` varchar(30) NOT NULL,
`name` varchar(30) NOT NULL,
`score` int(11) DEFAULT NULL,
`subject` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.插入测试数据
insert into `stud` (`id`, `name`, `score`, `subject`) values('1001','张三','98','语文');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1002','张三','86','数学');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1003','张三','73','英语');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1004','李四','85','语文');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1005','李四','92','数学');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1006','李四','79','英语');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1007','王五','68','语文');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1008','王五','79','数学');
insert into `stud` (`id`, `name`, `score`, `subject`) values('1009','王五','92','英语');
整理完是这样的表结构:
3.使用sql查询分组
这里有几个说明的地方:
A:一定要自己手动排好序,因为我本意是按照科目分组,查出每个科目分数最高的学员信息,因此先按照subject排序,再按照score降序(asc查询的就是每科中成绩最低的学员信息了)
B:仿照这个改sql时,除了自己的表字段以外,B处是要手动替换的字段,因为我需要科目分组,所以需要写成subject,按照每个科目的rank进行1、2、3...这样的展示
C:rank <=1,1代表了选取每组第一行的数据
完成sql如下展示:
SELECT id, `name`, score, `subject`, rank FROM (
SELECT b.id, b.name, b.score, b.subject, IF(@pdept=b.subject, @rank:=@rank+1, @rank:=1) AS rank, @pdept:=b.subject FROM (
SELECT id, `name`, score, `subject` FROM stud ORDER BY `subject`, score DESC
) b, (SELECT @pdept:=NULL, @rank:=0) c
) result HAVING rank <= 1;
分享到:
相关推荐
- Oracle的窗口函数`ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)`, MySQL无直接对应,可使用用户变量模拟实现。 - Oracle的`ROWNUM`行号在MySQL中需要通过用户变量或自连接实现分页效果。 转换时,需要...
SELECT row_number() over(partition by col1 order by col2) as num FROM table1 MySQL: SET @rank = 0; SET @pdept = ''; SELECT num1 AS num FROM ( SELECT IF(@pdept=col1, @rank:=@rank+1, @rank:=1) AS num1...
本文将详细介绍如何在MySQL中实现类似Oracle中`row_number() over (partition by)`的功能,即按照指定列进行分组,并在每个分组内进行排序。 #### 实现步骤详解 ##### 1. 创建示例表与数据 首先,我们需要创建一...
- 序列化函数:Oracle的`ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)`在MySQL中需使用用户变量模拟,如示例所示。 - 行号`ROWNUM`:Oracle的行号在MySQL中需通过用户变量实现,与序列化函数的转换类似。 6...
在Oracle数据库中,可以使用`ROW_NUMBER()`窗口函数来实现这一目标,但MySQL并不直接支持此功能。然而,我们可以使用子查询或者变量来达到类似的效果。标题和描述中的内容就是在模拟Oracle的`ROW_NUMBER()`功能,以...
- Oracle的窗口函数`ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)`在MySQL中无法直接使用,需要借助用户变量来模拟。 - Oracle的行号`ROWNUM`在MySQL中没有直接等价物,同样需要利用用户变量来模拟。 在...
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank FROM emp ) t WHERE t.rank ; ``` - **解析**: - 使用 `ROW_NUMBER()` 函数对每个部门(`deptno`)中的员工按照工资(`sal`)进行降序...
- 分页查询通常使用`LIMIT`和`OFFSET`(MySQL语法)或者`ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)`(Oracle、SQL Server等)。对于Oracle,可以使用子查询结合`ROWNUM`实现分页。例如: ```sql SELECT ...
- **窗口函数**:介绍OVER子句的使用,包括RANK、ROW_NUMBER等功能强大的窗口函数。 - **集合操作**:学习UNION、INTERSECT、MINUS等集合操作符的应用,用于组合多个查询的结果集。 - **分区函数**:讲解如何使用...
SELECT year, amount, ROW_NUMBER() OVER(PARTITION BY year ORDER BY month) AS rn FROM aaa ) subquery GROUP BY year ``` 这些查询将数据转换为指定的格式,每个年份对应4个月份的金额,使用了`CASE WHEN`...
例如,`ROW_NUMBER()`、`RANK()`、`DENSE_RANK()` 可用于排序和生成序列号,`LEAD()` 和 `LAG()` 可以获取当前行的前一行或后一行数据,`AVG() OVER (PARTITION BY ...)` 可以计算分组内的平均值。 2. **子查询**:...