⑴ sql查询取分组最大值
selectt1.分类,t1.商品名称,table.库存,t1.更新时间
from (
select 分类,商品名称,max(更新时间) 更新时间 from table
group by 分类,商品名称
) t1
inner join table on t1.分类=table.分类 and t1.商品名称=table.商品名称 and t1.更新时间
=table.更新时间
(1)sql语句分组查询前n个最大值扩展阅读：
关于SQL最大值查询语句的记录
SELECT
bsnum,
STATE AS FILE_STATE
FROM
FILE_RECORD r
WHERE
STATE =(
SELECT
MAX(STATE)
FROM
FILE_RECORD
WHERE
bsnum = r.bsnum
);
小技巧：有时需要测试sql语法但又觉得创建表麻烦，可以使用创建一个临时表，如下：
select id, count(t.id) from
(
select 1 as id, 2 as age, 'F' as sex from al union
select 2 as id, 2 as age, 'M' as sex from al union
select 3 as id, 3 as age, 'F' as sex from al union
select 4 as id, 4 as age, 'M' as sex from al
)t
⑵ SQL分组查询最大值最小值
查询语句参考如下：
select
UserName,--
Convert(char(8),dealTm,112) as date,--日期
min(dealTm) as earliestTime ,--最大
max(dealTm) as latestTime--最小
from t2
group by Convert(char(8),dealTm,112) ,UserName
⑶ 求一SQL语句:如何查询最大的前3个值
查询最大的前3个值的方法及编写方式
⑷ sql语言 怎么求每组最大,就是用group by 分组后，求每组某列最大
在分组中使用max()函数即可。
例图中表格：
如果是最小，则可以使用 min()函数
⑸ sql如何求分组计数之后计数的最大值
插入测试记录：
[sql] view plain
insertintostudentsvalues('数学','Jack','Tianjin',80)
insertintostudentsvalues('数学','Jordan','Tianjin',80)
insertintostudentsvalues('数学','James','Beijing',55)
insertintostudentsvalues('英语','Jack','Tianjin',90)
insertintostudentsvalues('英语','Jordan','Tianjin',60)
insertintostudentsvalues('英语','James','Beijing',100)
insertintostudentsvalues('语文','Jack','Tianjin',60)
insertintostudentsvalues('语文','Tom','Tianjin',77)
insertintostudentsvalues('语文','Jordan','Tianjin',68)
想要抓取每个科目第一名的整条信息，可以使用Row_number()函数：
select*
from(
selectcourse,stu_name,city,score,ROW_NUMBER()over()asrn
fromstudents
)a
wherea.rn<=1;
⑹ SQL Server中如何分组查询最大值
SQL> with a as (select 1 a,0 b,'c1' c,'d1' d from al
2 union
3 select 1 a,1 b,'c2' c,'d2' d from al
4 union
5 select 1 a,3 b,'c3' c,'d3' d from al
6 union
7 select 4 a,0 b,'c4' c,'d4' d from al
8 union
9 select 5 a,1 b,'c5' c,'d5' d from al
10 union
11 select 5 a,2 b,'c6' c,'d6' d from al
12 union
13 select 6 a,1 b,'c7' c,'d7' d from al
14 union
15 select 6 a,4 b,'c8' c,'d8' d from al
16 )
17 SELECT A,B,C,D FROM
18 (select A,B,C,D,ROW_NUMBER()OVER(PARTITION BY A ORDER BY A,B DESC) RN from A)
19 WHERE RN=1
20 /
A B C D
---------- ---------- -- --
1 3 c3 d3
4 0 c4 d4
5 2 c6 d6
6 4 c8 d8
SQL>
⑺ SQL如何取得一个列中前几个最大值
select top n * from 表 order by 列名 desc
取几n为几