字段A 字段B 字段C 字段D
A 结束 3 a
A 开始 2 b
A 开始 1 c
B 结束 7 a
B 开始 6 b
B 开始 5 c
B 开始 4 d
B 开始 3 e
B 开始 2 f
B 开始 1 g
C 开始 2 a
C 开始 1 b
D 开始 3 a
D 开始 2 b
D 开始 1 c提取字段A对应的字段C的最大值。并且不包括结束!
C 开始 2 a
D 开始 3 a
结果如下:
select distinct 字段A,字段B,MAX(字段C) from LoanInf where LoanType='个人客户' where 字段D<>'结束'
为什么这样不行呢?
A 结束 3 a
A 开始 2 b
A 开始 1 c
B 结束 7 a
B 开始 6 b
B 开始 5 c
B 开始 4 d
B 开始 3 e
B 开始 2 f
B 开始 1 g
C 开始 2 a
C 开始 1 b
D 开始 3 a
D 开始 2 b
D 开始 1 c提取字段A对应的字段C的最大值。并且不包括结束!
C 开始 2 a
D 开始 3 a
结果如下:
select distinct 字段A,字段B,MAX(字段C) from LoanInf where LoanType='个人客户' where 字段D<>'结束'
为什么这样不行呢?
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test](
[字段A] varchar(1),
[字段B] varchar(4),
[字段C] int,
[字段D] varchar(1)
)
insert [test]
select 'A','结束',3,'a' union all
select 'A','开始',2,'b' union all
select 'A','开始',1,'c' union all
select 'B','结束',7,'a' union all
select 'B','开始',6,'b' union all
select 'B','开始',5,'c' union all
select 'B','开始',4,'d' union all
select 'B','开始',3,'e' union all
select 'B','开始',2,'f' union all
select 'B','开始',1,'g' union all
select 'C','开始',2,'a' union all
select 'C','开始',1,'b' union all
select 'D','开始',3,'a' union all
select 'D','开始',2,'b' union all
select 'D','开始',1,'c'select distinct [字段A],[字段B],
(select MAX([字段C]) from test c where c.字段A=a.字段A)[字段C],
(select [字段D] from test d where [字段C]=
(select MAX([字段C]) from test c where c.字段A=a.字段A) and d.字段A=a.字段A)[字段D] from test a
where not exists(select 1 from test b
where a.字段A=b.字段A and b.字段B='结束')
where exists
(select *
from (select 字段A,max(字段C) as maxc
from test
group by 字段A) a
where b.字段A = a.字段A
and b.字段C = a.maxc)
and 字段B <> '结束'
where exists(select * from(select 字段A,max(字段C) as 字段C from TEST a where not exists
(SELECT * FROM test where 字段B='结束' and a.字段A=test.字段A)
group by 字段A )b where a.字段A=b.字段A and a.字段C=b.字段C)也用了一楼的建表数据
create table t1
(
a varchar(1),
b varchar(10),
c int,
d varchar(1)
)
insert into t1
select 'A', '结束', 3, 'a' union all
select 'A', '开始', 2, 'b' union all
select 'A', '开始', 1, 'c' union all
select 'B', '结束', 7, 'a' union all
select 'B', '开始', 6, 'b' union all
select 'B', '开始', 5, 'c' union all
select 'B', '开始', 4, 'd' union all
select 'B', '开始', 3, 'e' union all
select 'B', '开始', 2, 'f' union all
select 'B', '开始', 1, 'g' union all
select 'C', '开始', 2, 'a' union all
select 'C', '开始', 1, 'b' union all
select 'D', '开始', 3, 'a' union all
select 'D', '开始', 2, 'b' union all
select 'D', '开始', 1, 'c'
select * from t1;with aaa as
(
select * from t1 where a in
(
select a
from t1
group by a
having SUM(case when b='结束' then 1 else 0 end)=0
)
)
select * from aaa as a1 where not exists (select 1 from aaa as b1 where b1.a=a1.a and b1.c>a1.c)---------------------------
a b c d
C 开始 2 a
D 开始 3 a