select * from tb a where not exists(select 1 from tb where uname=a.uname and score>a.score)
解决方案 »
- 这段SQL在VS里执行不起来
- 一句SQL,简单,不知道应该怎么写
- 简单的sql语句
- sql行变列求最大最小
- java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data
- 有办法将一个数值随机分解成N个数值,然后再进行更新吗?
- 求助!请问怎么在一个表中用SQL语句插入多条记录?
- 想用sql跨越数据库完成插入应如何写(再线急等)
- Access中对于下面的语句为什么不能执行,有什么好的解决方法?
- Informix share momeny
- RECMODEL_70BACKCOMP 参数 一问
- 怎么样在交叉表里加入条件
-----------------------------------------
mary 40 sz thanks
kate 45 uy sorry
moss 28 sh hello
goinsert tb select 'mary' ,30, 'sh' , 'hello'
insert tb select 'mary', 40, 'sz' , 'thanks'
insert tb select 'kate' ,24, 'sd' , 'hi'
insert tb select 'kate' ,45, 'uy' , 'sorry'
insert tb select 'moss', 28, 'sh' , 'hello'
insert tb select 'moss', 0, 'sz' , 'hi'
go
select * from tb a where not exists(select 1 from tb where uname=a.uname and score>a.score)
go
drop table tb
go
/*
uname score location re
---------- ----------- ---------- ----------
mary 40 sz thanks
kate 45 uy sorry
moss 28 sh hello(所影响的行数为 3 行)*/
create table tb(uname varchar(10), score int, location varchar(10) , re varchar(10) )
goinsert tb select 'mary' ,30, 'sh' , 'hello'
insert tb select 'mary', 40, 'sz' , 'thanks'
insert tb select 'kate' ,24, 'sd' , 'hi'
insert tb select 'kate' ,45, 'uy' , 'sorry'
insert tb select 'moss', 28, 'sh' , 'hello'
insert tb select 'moss', 0, 'sz' , 'hi'
go
select * from tb a WHERE score = (SELECT MAX(score) FROM tb WHERE uname = a.uname) ORDER BY uname
go
drop table tb
go
/*
uname score location re
---------- ----------- ---------- ----------
kate 45 uy sorry
mary 40 sz thanks
moss 28 sh hello*/
insert #tb select 'mary',30,'sh','hello'
insert #tb select 'mary',40,'sz','thanks'
insert #tb select 'kate',24,'sd','hi'
insert #tb select 'kate',45,'uy','sorry'
insert #tb select 'moss',28,'sh','hello'
insert #tb select 'moss',0,'sz','hi'
go
select * from #tb a where exists(select 1 from #tb where uname=a.uname and score<a.score)uname score location re
---------- ----------- ---------- ----------
mary 40 sz thanks
kate 45 uy sorry
moss 28 sh hello(3 行受影响)