1. 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
2. sql 多条数据各自的最大日期
以sCardNum分组，取出最大时间的记录
select * from cards where srcvouchdate in (select MAX(srcvouchdate) from UserList group by sCardNum)
3. 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;
4. sql分组后取最大日期的记录
select a.f_SPBM,a.f_kcsl
from tempTest a,
(
select f_SPBM,max(f_rq) f_rq
from tempTest
group by f_SPBM
) b
where a. f_SPBM = b. f_SPBM
and a.f_rq = b.f_rq
5. SQL中如何选择表中日期最大值
给你个思路：
WITHMessages(ID,ToEmpID,FromEmpID,SendDate,Detail)AS
(
SELECT1,'张三','李四','2014/06/05','a'UNIONALL
SELECT2,'王五','张三','2014/06/03','b'UNIONALL
SELECT3,'刘六','张三','2014/06/02','b'UNIONALL
SELECT4,'张三','李四','2014/05/05','a'UNIONALL
SELECT5,'王五','张三','2014/05/03','b'UNIONALL
SELECT6,'刘六','张三','2014/05/02','b'
)
SELECTID,ToEmpID,FromEmpID,SendDateFROMMessagesa
WHEREIDin
(
SELECTTOP1ID
FROMMessages
whereToEmpID=a.ToEmpIDANDFromEmpID=a.FromEmpID
ORDERBYSendDateDESC
)
6. 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>
7. SQL两个表联合查询记录中怎么取最大值
1、在打开的ie浏览器窗口右上方点击齿轮图标，选择“Internet选项”，如下图所示：
8. 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.更新时间
(8)sql数据按日期分组取最大值扩展阅读：
关于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
9. sql 分组求和，取各组极值，求高手
select max(时刻值)
,min(时刻值)
from (select 日期 时刻 名称 类型 等级 sum(时刻值)
from TO
group by 日期 时刻 名称 类型 等级)
10. sql server分组求最大值(100分)
select 编号,姓名,max(合同日期)
from table
group by 编号,姓名
order by 合同日期;
--测试数据：
SQL> select * from a;
NO NAME HT
---------- -------------------- -----------
SQL> select * from a;
NO NAME HT
---------- -------------------- -----------
100 张三 1985-5-1
100 张三 1986-5-1
101 李四 1985-5-1
101 李四 1999-5-1
SQL>
SQL> select no,name,max(ht)
2 from a
3 group by no,name
4 ;
NO NAME MAX(HT)
---------- -------------------- -----------
100 张三 1986-5-1
101 李四 1999-5-1
SQL> insert into a values(100,'张三',to_date('1999-5-1','yyyy-mm-dd'));
1 row inserted
SQL> select * from a;
NO NAME HT
---------- -------------------- -----------
100 张三 1985-5-1
100 张三 1986-5-1
101 李四 1985-5-1
101 李四 1999-5-1
100 张三 1999-5-1
SQL>
SQL> select no,name,max(ht)
2 from a
3 group by no,name;
NO NAME MAX(HT)
---------- -------------------- -----------
100 张三 1999-5-1
101 李四 1999-5-1
新插入了一条数据之后，使用该sql查询语句即把刚插入的合同日期最大的那条数据显示出来了