求达人一句sql语句 谢谢
表结构大概是这样子的
zh name flag sd zj
1 zhang 1 23 26
1 zhang 2 26 33
1 zhang 3 33 36
.
.
2 li 1 56 89
2 li 2 89 103
2 li 3 103 106
2 li 4 106 108
.
.
.
3 wang 1 225 228
3 wang 2 228 230
....
具体是这样子的。flag 字段在增 ， 然后求 flag 最大的那列 sd 和zj的值然后把 其他的列删除了。
比如上面的要求的结果是：
zh name flag sd zj
1 zhang 3 33 36
2 li 4 106 108
3 wang 2 228 230
请问这句sql如何写？
[解决办法]
delete a from tb a
where exists(select 1 from tb where zh=a.zh and flag>a.flag)
[解决办法]
select * from Table t where flag = (select max(flag) from Table ts where t.zh = ts.zh)
select * from Table where id in (select id from Table where flag in (select max(flag) from Table group by zh))
select * from Table a where not exists(select 1 from Table where zh=a.zh and flag>a.flag)
create table tb1
(
zh int not null,
name nvarchar(10),
flag int not null,
sd int,
zj int
)
insert into tb1
select 1,'zhang',1,23,26
union all
select 1,'zhang',2,26,33
union all
select 1,'zhang',3,33,36
union all
select 2,'li',1,56,89
union all
select 2,'li',2,89,103
union all
select 2,'li',3,103,106
union all
select 2,'li',4,106,108
union all
select 3,'wang',1,225,228
union all
select 3,'wang',2,228,230
select zh,name,flag,sd,zj,sd+zj as summ from (
select zh,name,flag,sd,zj,row_number() over(partition by zh order by flag desc) as rn from tb1) a
where rn=1
select zh,name,flag,sd,zj,row_number() over(partition by zh,name order by flag desc) as rn from tb1from
