保留表中salary数值最小的。

2013-05-30 04:14:14 209 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›



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

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

...全文

冰糖_adam

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

MrYangkang 2013-05-30

打赏
举报


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

冰糖_adam 2013-05-30

打赏
举报

引用 9 楼 xiaoqi7456 的回复:

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

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

冰糖_adam 2013-05-30

打赏
举报

谢谢各位都弄明白了。

唐诗三百首 2013-05-30

打赏
举报

引用 4 楼 tang285377470 的回复:

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


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

md5e 2013-05-30

打赏
举报

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

冰糖_adam 2013-05-30

打赏
举报

引用 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 的话更容易明白。

唐诗三百首 2013-05-30

打赏
举报


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)
*/

昵称被占用了 2013-05-30

打赏
举报

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

xiaoqi7456 2013-05-30

打赏
举报

引用 4 楼 tang285377470 的回复:

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

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

冰糖_adam 2013-05-30

打赏
举报

引用 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 测试结果还是有问题

KevinLiu 2013-05-30

打赏
举报

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

昵称被占用了 2013-05-30

打赏
举报

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

xiaoqi7456 2013-05-30

打赏
举报

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]

冰糖_adam 2013-05-30

打赏
举报

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

MrYangkang 2013-05-30

打赏
举报


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

冰糖_adam 2013-05-30

打赏
举报

引用 1 楼 xiaoqi7456 的回复:

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

各个名字都要取一个

xiaoqi7456 2013-05-30

打赏
举报

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