保留表中salary数值最小的。

[冰糖_adam]

create table test
(
userName varchar(30),
[month]  varchar(20),
salary   numeric(15,4)
)

insert into test values('adam','201001',4500)
insert into test values('adam','201002',3500)
insert into test values('adam','201003',2500)
go
insert into test values('alice','201001',4500)
insert into test values('alice','201002',3500)
insert into test values('alice','201003',5500)
go
insert into test values('lucy','201001',4500)
insert into test values('lucy','201002',3500)
insert into test values('lucy','201003',9500)
go
insert into test values('emily','201001',10500)
go

有没有好的删除语句，或者这种文档 学习下。

[xiaoqi7456]

delete from test where salary>(select min(salary) from test)

[冰糖_adam]

引用 1 楼 xiaoqi7456 的回复:

delete from test where salary>(select min(salary) from test)

各个名字都要取一个

[MrYangkang]

delete from test where salary not in
(
select min(salary) from test group by userName 
)

[冰糖_adam]

还有1个问题是：取出各个名字出现的次数并汇总 salary

[xiaoqi7456]

delete a from test a, (select userName  min(salary) as salary from test group by username) b where a.username=b.username and a.salary>b.salary

引用 2 楼 tang285377470 的回复:

[quote=引用 1 楼 xiaoqi7456 的回复:] delete from test where salary>(select min(salary) from test)

各个名字都要取一个[/quote]

[昵称被占用了]

delete a from test a where exists ( select 1 from test where username = a.username and salary < a.salary )

[KevinLiu]

delete test from test t1 inner join (select username,min(salary) as salary from test group by username ) t on t.userName = t1.userName and t1.salary > t.salary

[冰糖_adam]

引用 3 楼 ZaoLianBuXiQi 的回复:

delete from test where salary not in
(
select min(salary) from test group by userName 
)

adam 201002 3500.0000 adam 201003 2500.0000 alice 201002 3500.0000 lucy 201002 3500.0000 emily 201001 10500.0000 alice 201002 3500.0000 lucy 201002 3500.0000 emily 201001 10500.0000 测试结果还是有问题

[xiaoqi7456]

引用 4 楼 tang285377470 的回复:

还有1个问题是：取出各个名字出现的次数并汇总 salary

select username ,sum(salary) from test group by username 这些基础题目建议自己多想想。才会有提高，自己动手多练练 可以上网多看看什么经典50题目什么的。有答案有环境自己先动手练 别看答案

[昵称被占用了]

select username ,count(1) as cnt ,sum(salary) as sumsalary from test group by username

[唐诗三百首]

create table test
(
userName varchar(30),
[month]  varchar(20),
salary   numeric(15,4)
)
 
insert into test values('adam','201001',4500)
insert into test values('adam','201002',3500)
insert into test values('adam','201003',2500)
go
insert into test values('alice','201001',4500)
insert into test values('alice','201002',3500)
insert into test values('alice','201003',5500)
go
insert into test values('lucy','201001',4500)
insert into test values('lucy','201002',3500)
insert into test values('lucy','201003',9500)
go
insert into test values('emily','201001',10500)
go


with t as
(select userName,[month],salary,
        row_number() over(partition by userName order by salary) 'rn' 
 from test
)
delete from t where rn>1


select userName,[month],salary from test

/*
userName                       month                salary
------------------------------ -------------------- ---------------------------------------
adam                           201003               2500.0000
alice                          201002               3500.0000
lucy                           201002               3500.0000
emily                          201001               10500.0000

(4 row(s) affected)
*/

[冰糖_adam]

引用 5 楼 xiaoqi7456 的回复:

delete a from test a, (select userName  min(salary) as salary from test group by username) b where a.username=b.username and a.salary>b.salary

没想到delete 后面跟个 table， 这个from 为什么不是where 感觉where 的话更容易明白。

[md5e]

Delete From test From test as t INNER JOIN ( Select *,row_number() over(PARTITION BY userName Order by salary) as num From test ) as t2 on t.username=t2.username and t.month=t2.month and t.salary=t2.salary WHere t2.num>1

[唐诗三百首]

引用 4 楼 tang285377470 的回复:

还有1个问题是：取出各个名字出现的次数并汇总 salary

select userName,
       count(1) '次数',
       sum(salary) '汇总'
 from test
 group by userName

[冰糖_adam]

谢谢各位 都弄明白了。

[冰糖_adam]

引用 9 楼 xiaoqi7456 的回复:

[quote=引用 4 楼 tang285377470 的回复:] 还有1个问题是：取出各个名字出现的次数并汇总 salary

select username ,sum(salary) from test group by username 这些基础题目建议自己多想想。才会有提高，自己动手多练练 可以上网多看看什么经典50题目什么的。有答案有环境自己先动手练 别看答案[/quote] 多谢建议，后面就会看。

[MrYangkang]

delete test from test inner join
(
select userName,min(salary)salary from test group by userName 
)d
on test.userName = d.userName and test.salary > d.salary