SQL分组取最大/最小的问题

SQL操作:按名分组获取特定值
本文介绍如何使用SQL查询语句按名称分组后获取最大值、最小值、首次出现值、随机值及特定数量的值。通过五种方法详细解释了SQL查询的实现方式。

--首先在数据库建立表名为tb,代码如下:

--建表
create table tb(name varchar(10),val int,memo varchar(20))
--插入值
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')

1、按name分组取val最大的值所在行的数据,代码如下:

--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name

    注:个人觉得方法3、方法4好理解

2、按name分组取val最小的值所在行的数据,代码如下:

--方法1:
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3:
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name

3、按name分组取第一次出现的行所在的数据,代码如下:

select a.* from tb a where val = (select top 1 val from tb where name = a.name) 
order by a.name

4、按name分组随机取一条数据,代码如下:

select a.* from tb a where val = (select top 1 val from tb where name = a.name 
order by newid()) order by a.name

5、按name分组取最小的两个(N个)val,代码如下:

select a.* from tb a where 2 > (select count(*) from tb where name = 
a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name 
order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = 
a.name and val < a.val having Count(*) < 2) order by a.name

6、按name分组取最大的两个(N个)val,代码如下:

select a.* from tb a where 2 > (select count(*) from tb where name = 
a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name 
order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = 
a.name and val > a.val having Count(*) < 2) order by a.name

 

 

转载于:https://my.oschina.net/u/1426828/blog/188060

SQL查询中,如果需要获每个分组中的最大值,通常会结合`GROUP BY`、聚合函数`MAX()`以及子查询等技术手段完成。 ### 示例场景 假设有一张表 `sales`,包含字段 `region`(地区) 和 `amount`(销售额),我们希望找到每个地区的最高销售额及对应的记录信息。 #### 查询步骤说明 1. **直接求每组的最大值** 如果仅需找出各分组下的最大值而不关心具体的行数据,则可以使用简单的 SQL 结构。 ```sql SELECT region, MAX(amount) AS max_amount FROM sales GROUP BY region; ``` 这段代码将返回每一区域(`region`)里最大的金额数(`max_amount`)。 2. **连带其他列一起选出完整信息的记录** 当想要进一步提出对应“最大值”的整条记录(例如日期或其他关联项),需要用到更复杂的结构如子查询或窗口函数: - 使用**子查询+JOIN** 先通过内部查询确定每组的最大值,再利用外层连接找回完整的原始数据: ```sql SELECT s.* FROM sales s INNER JOIN ( SELECT region, MAX(amount) as max_amount FROM sales GROUP BY region ) sm ON s.region = sm.region AND s.amount = sm.max_amount; ``` - 利用**窗口函数 ROW_NUMBER()** 窗口函数能够一次性解决问题,并且支持处理存在重复最大值的情况。下面的例子按照分区排序标记序号,“第一名”即为我们所需结果: ```sql WITH RankedSales AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) rn FROM sales ) SELECT * FROM RankedSales WHERE rn = 1; ``` 以上就是如何从数据库的不同分组内查找各自最大值的基本思路及其应用实例!
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值