一个SQL面试题

cooolchen 2011-01-19 03:25:07
ID PNO TID Num
173 Y001234 AA 23
174 Y001234 BB 3
175 Y001234 CC 3
176 Y001234 DD 20

180 Y280001 BB 41
181 Y280001 CC 24
182 Y280001 AA 16
186 Y280001 EE 1
187 Y280001 FF 1

188 Y290002 CC 32
189 Y290002 BB 25
190 Y290002 AA 12

191 Y290008 BB 32
192 Y290008 AA 25
193 Y290008 CC 12

表T(ID,PNO,TID,Num)中以上数据,现在要根据传入的TID,取得PNO,要求TID在PNO中是排第一的。
需要通过以下测试用例:
Test Case 1:如果TID = CC,那么取得的PNO应该为Y290002
Test Case 2:如果TID = BB,那么取得的PNO应该为Y280001,Y290008

使用T-SQL写出结果。


...全文
143 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
gw6328 2011-01-19
  • 打赏
  • 举报
回复



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
*/
黄_瓜 2011-01-19
  • 打赏
  • 举报
回复
;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'


想不到其他高效的了
快溜 2011-01-19
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 cooolchen 的回复:]
色狼和小三,你们有没有注意到性能的问题,这个数据量是100+W,我刚刚忘记说这个了。
[/Quote]
建索引,数据量大,子查询,建临时表都会影响性能,或者等高手吧。
黄_瓜 2011-01-19
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 cooolchen 的回复:]
色狼和小三,你们有没有注意到性能的问题,这个数据量是100+W,我刚刚忘记说这个了。
[/Quote]
色狼和小三
哇咔咔 2011-01-19
  • 打赏
  • 举报
回复
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'
快溜 2011-01-19
  • 打赏
  • 举报
回复

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
AcHerat 2011-01-19
  • 打赏
  • 举报
回复

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 行受影响)
快溜 2011-01-19
  • 打赏
  • 举报
回复

select PNO from tb a where ID in (select min(id) and PNO=a.PNO) and a.TID = BB
王向飞 2011-01-19
  • 打赏
  • 举报
回复

--> 数据库版本:
--> 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]

王向飞 2011-01-19
  • 打赏
  • 举报
回复
Test Case 2:如果TID = BB,那么取得的PNO应该为Y280001,Y290008 为什么是俩?
Mr-Jee 2011-01-19
  • 打赏
  • 举报
回复
要求TID在PNO中是排第一的。 是神马意思

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧