22,199
社区成员
发帖
与我相关
我的任务
分享
declare @tb table(id int ,pno varchar(20),tid varchar(10),num int)
insert into @tb
select 173 ,'Y001234', 'AA', 23 union all
select 174 ,'Y001234', 'BB', 3 union all
select 175 ,'Y001234', 'CC', 3 union all
select 176 ,'Y001234', 'DD', 20 union all
select 180 ,'Y280001', 'BB', 41 union all
select 181 ,'Y280001', 'CC', 24 union all
select 182 ,'Y280001', 'AA', 16 union all
select 186 ,'Y280001', 'EE', 1 union all
select 187 ,'Y280001', 'FF', 1 union all
select 188 ,'Y290002', 'CC', 32 union all
select 189 ,'Y290002', 'BB', 25 union all
select 190 ,'Y290002', 'AA', 12 union all
select 191 ,'Y290008', 'BB', 32 union all
select 192 ,'Y290008', 'AA', 25 union all
select 193 ,'Y290008', 'CC', 12
-- select * from @tb
select * from
(
select *,rn=ROW_NUMBER() over(partition by pno order by id) from @tb
)x where x.rn=1
/*
id pno tid num rn
----------- -------------------- ---------- ----------- --------------------
173 Y001234 AA 23 1
180 Y280001 BB 41 1
188 Y290002 CC 32 1
191 Y290008 BB 32 1
*/
;with cte as
(
select *,row_number() over (partition by pno order by id)as row_id from t1
)
select pno from cte where row_id = 1 and tid = 'BB'
select pno
from(
select min(id) as id,pno
from tb
group by pno) t,tb
where t.id = tb.id
and tb.tid = 'BB'
create table [TB]([ID] int,[PNO] varchar(7),[TID] varchar(2),[Num] int)
insert [TB]
select 173,'Y001234','AA',23 union all
select 174,'Y001234','BB',3 union all
select 175,'Y001234','CC',3 union all
select 176,'Y001234','DD',20 union all
select 180,'Y280001','BB',41 union all
select 181,'Y280001','CC',24 union all
select 182,'Y280001','AA',16 union all
select 186,'Y280001','EE',1 union all
select 187,'Y280001','FF',1 union all
select 188,'Y290002','CC',32 union all
select 189,'Y290002','BB',25 union all
select 190,'Y290002','AA',12 union all
select 191,'Y290008','BB',32 union all
select 192,'Y290008','AA',25 union all
select 193,'Y290008','CC',12
select PNO from tb a where ID in (select min(id) from tb where PNO=a.PNO) and a.TID = 'BB'
PNO
-------
Y280001
Y290008
create table t1(id int,pno varchar(10),tid varchar(10),num int)
insert into t1
select 173,'Y001234','AA',23 union all
select 174,'Y001234','BB',3 union all
select 175,'Y001234','CC',3 union all
select 176,'Y001234','DD',20 union all
select 180,'Y280001','BB',41 union all
select 181,'Y280001','CC',24 union all
select 182,'Y280001','AA',16 union all
select 186,'Y280001','EE',1 union all
select 187,'Y280001','FF',1 union all
select 188,'Y290002','CC',32 union all
select 189,'Y290002','BB',25 union all
select 190,'Y290002','AA',12 union all
select 191,'Y290008','BB',32 union all
select 192,'Y290008','AA',25 union all
select 193,'Y290008','CC',12
go
with cte as
(
select *,row_number() over (partition by pno order by id)i from t1
)
select pno
from cte
where i = 1 and tid = 'BB'
--drop test table
drop table t1
pno
----------
Y280001
Y290008
(2 行受影响)
select PNO from tb a where ID in (select min(id) and PNO=a.PNO) and a.TID = BB
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U')) --U 代表你查询的是表
DROP TABLE [TB]
GO
---->建表
create table [TB]([ID] int,[PNO] varchar(7),[TID] varchar(2),[Num] int)
insert [TB]
select 173,'Y001234','AA',23 union all
select 174,'Y001234','BB',3 union all
select 175,'Y001234','CC',3 union all
select 176,'Y001234','DD',20 union all
select 180,'Y280001','BB',41 union all
select 181,'Y280001','CC',24 union all
select 182,'Y280001','AA',16 union all
select 186,'Y280001','EE',1 union all
select 187,'Y280001','FF',1 union all
select 188,'Y290002','CC',32 union all
select 189,'Y290002','BB',25 union all
select 190,'Y290002','AA',12 union all
select 191,'Y290008','BB',32 union all
select 192,'Y290008','AA',25 union all
select 193,'Y290008','CC',12
GO
--> 查询结果
SELECT * FROM [TB] t where
not exists (select * from [TB] where TID=t.TID and [Num]>t.[Num])
and
TID = 'CC'
SELECT * FROM [TB] t where
not exists (select * from [TB] where TID=t.TID and [Num]>t.[Num])
and
TID = 'BB'
--你的答案有问题
--> 删除表格
--DROP TABLE [TB]