比如有一个表temp
id number siz
1 001 1
2 002 1
3 003 1
4 003 2
5 003 1
6 001 2
7 004 null取出某number最后的siz为1的比如001的siz为1-->2,那么不取
003的siz为1-->2-->1,则取
上面例子取出002、003,请问该怎么写这条语句?我是SQL菜鸟,请见谅...
id number siz
1 001 1
2 002 1
3 003 1
4 003 2
5 003 1
6 001 2
7 004 null取出某number最后的siz为1的比如001的siz为1-->2,那么不取
003的siz为1-->2-->1,则取
上面例子取出002、003,请问该怎么写这条语句?我是SQL菜鸟,请见谅...
select number from temp group by number having max(isnull(id,1))<>1
select * from temp where id in (select max(id) from temp group by number)
where isnull(siz,0)=1
if object_id('[temp]') is not null drop table [temp]
go
create table [temp]([id] int,[number] varchar(3),[siz] int)
insert [temp]
select 1,'001',1 union all
select 2,'002',1 union all
select 3,'003',1 union all
select 4,'003',2 union all
select 5,'003',1 union all
select 6,'001',2 union all
select 7,'004',null
---查询---
select number
from temp t
where not exists(select 1 from temp where number=t.number and id>t.id)
and t.siz=1---结果---
number
------
002
003
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([id] int,[number] varchar(3),[siz] int)
insert #tb
select 1,'001',1 union all
select 2,'002',1 union all
select 3,'003',1 union all
select 4,'003',2 union all
select 5,'003',1 union all
select 6,'001',2 union all
select 7,'004',null--------------------------------查询开始------------------------------select * from #tb t where
not exists(select 1 from #tb
where id=(select max([id]) from #tb where [number]=t.[number]) and [siz]=2 and [number]=t.[number])
and [siz] is not null
/*
id number siz
----------- ------ -----------
2 002 1
3 003 1
4 003 2
5 003 1(4 行受影响)
*/
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb([id] int,[number] varchar(3),[siz] int)
insert #tb
select 1,'001',1 union all
select 2,'002',1 union all
select 3,'003',1 union all
select 4,'003',2 union all
select 5,'003',1 union all
select 6,'001',2 union all
select 7,'004',null--------------------------------查询开始------------------------------select * from #tb t where
not exists(select 1 from #tb where [number]=t.[number] and id>t.id)
and [siz] =1
/*
id number siz
----------- ------ -----------
2 002 1
5 003 1(2 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[number] [nvarchar](10),[siz] [int])
INSERT INTO [tb]
SELECT '1','001','1' UNION ALL
SELECT '2','002','1' UNION ALL
SELECT '3','003','1' UNION ALL
SELECT '4','003','2' UNION ALL
SELECT '5','003','1' UNION ALL
SELECT '6','001','2' UNION ALL
SELECT '7','004',NULL--SELECT * FROM [tb]-->SQL查询如下:
SELECT number
FROM (
SELECT rn=ROW_NUMBER()OVER(PARTITION BY number ORDER BY id DESC),*
FROM tb
) t
WHERE rn=1 AND siz=1
/*
number
----------
002
003(2 行受影响)
*/SQL2005的一种方法
select * from tb
where siz = 1 and id in(select max(id)
from tb
group by number)