我有一个表结构图下
A B c
----------------------------
1 2010-1-1 0
1 2010-1-2 0
1 2010-2-3 0
2 2010-3-1 0
2 2010-3-5 0
2 2020-3-6 1
3 2010-4-5 1
------------------------------
大概结构如上,我想搜索c=0并且distinct(a)的b字段最大值。结果应该如下A B
-----------------------
1 2010-2-3
2 2010-3-5
----------------------------
请教一下查询语句该怎么写,谢谢大家了。
A B c
----------------------------
1 2010-1-1 0
1 2010-1-2 0
1 2010-2-3 0
2 2010-3-1 0
2 2010-3-5 0
2 2020-3-6 1
3 2010-4-5 1
------------------------------
大概结构如上,我想搜索c=0并且distinct(a)的b字段最大值。结果应该如下A B
-----------------------
1 2010-2-3
2 2010-3-5
----------------------------
请教一下查询语句该怎么写,谢谢大家了。
GO
CREATE TABLE TB
(
a int,
b datetime,
c int
)
INSERT INTO TB
SELECT 1,'2010-1-1',0 union all
SELECT 1,'2010-1-2',0 union all
SELECT 1,'2010-2-3',0 union all
SELECT 2,'2010-3-1',0 union all
SELECT 2,'2010-3-5',0 union all
SELECT 2,'2020-3-6',1 union all
SELECT 3,'2010-4-5',1select * from TB a where not exists(select * from tb b where a.a=b.a and a.b<b.b and a.c=b.c) and a.c='0'-----------
a b c
1 2010-02-03 00:00:00.000 0
2 2010-03-05 00:00:00.000 0