select * from tb t where not exists(select 1 from tb where 编号=t.编号 and A列>t.A列)
select 编号,MAX(A列) A列 from table group by 编号
--> 测试时间:2009-07-08 10:11:53 --> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab] create table [tab]([A] int,[B] int) insert [tab] select 1,1 union all select 1,19 union all select 1,2 union all select 2,1 union all select 2,18 union all select 3,0 union all select 3,10select * from [tab] t where not exists (select 1 from tab where t.A=A and T.B<B) /* A B ----------- ----------- 1 19 2 18 3 10(所影响的行数为 3 行) */ drop table tab
- -不行。 SELECT w.ID AS 申报编号, w.WorkTitle AS 申报标题, w.WorkCont AS 申报内容, e.FullName AS 申报员工, w.WorkDate AS 申报日期, s.State AS 申报状态, a.State AS 状态编号 FROM WorkApply w,Approve a,WorkState s,Employee e WHERE a.ApplyID = w.ID AND a.State = s.iD AND w.WorkEmpID = e.ID 状态编号有,1,2,3。 我的目的是:取出状态值最高的行。
select 1 from tab where t.A=A and T.B<B这句是是什么意思?为什么会投影“1”?
select 编号,max(a) from table group by 编号
select * from #a t where not exists (select 3 from #a where t.code=code and T.lie<lie) 语句的意思是,查询表T (这是别名T。)不等于(中编号列和#a编号列相等,并且 T 的lie值小于#a的列)
if object_id(N'test','U') is not null drop table testcreate table test ([A] int,[B] int) insert test select 1,1 union all select 1,19 union all select 1,2 union all select 2,1 union all select 2,18 union all select 3,0 union all select 3,10select MAX(B) as M from test group by A /* M 1 19 2 18 3 10 */
create table tx(id int, amt numeric(12,2)) go select * from ( select id,amt,rank() over(partition by id order by amt desc) rk from tx ) t where rk=1
select 编号,max(a) from table
group by 编号
from table
group by 编号
--> 测试时间:2009-07-08 10:11:53
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([A] int,[B] int)
insert [tab]
select 1,1 union all
select 1,19 union all
select 1,2 union all
select 2,1 union all
select 2,18 union all
select 3,0 union all
select 3,10select * from [tab] t where not exists (select 1 from tab where t.A=A and T.B<B)
/*
A B
----------- -----------
1 19
2 18
3 10(所影响的行数为 3 行)
*/
drop table tab
- -不行。
SELECT w.ID AS 申报编号,
w.WorkTitle AS 申报标题,
w.WorkCont AS 申报内容,
e.FullName AS 申报员工,
w.WorkDate AS 申报日期,
s.State AS 申报状态,
a.State AS 状态编号
FROM WorkApply w,Approve a,WorkState s,Employee e
WHERE a.ApplyID = w.ID
AND a.State = s.iD
AND w.WorkEmpID = e.ID 状态编号有,1,2,3。
我的目的是:取出状态值最高的行。
select 编号,max(a) from table
group by 编号
select * from #a t
where not exists (select 3 from #a where t.code=code and T.lie<lie)
语句的意思是,查询表T (这是别名T。)不等于(中编号列和#a编号列相等,并且 T 的lie值小于#a的列)
select 编号,max(a) from table
group by 编号
写过非常完美的命令
等我找找发出来。
insert test
select 1,1 union all
select 1,19 union all
select 1,2 union all
select 2,1 union all
select 2,18 union all
select 3,0 union all
select 3,10select MAX(B) as M from test group by A
/*
M
1 19
2 18
3 10
*/
go
select * from (
select id,amt,rank() over(partition by id order by amt desc) rk from tx
) t where rk=1