发信人: leezile (乐·ISEE.DK0201 FOREVER), 板面: Database
标 题: 问一道面试题。数据库方面
发信站: 飘渺水云间 (Sun May 27 23:42:44 2007), 转信
数据库中表的内容为:(KeyName, KeyValue是主键)
ID KeyName KeyValue
1 A 1
1 A 2
1 B 2
1 C 3
2 A 1
2 B 2
2 C 3
4 A 1
4 B 2
5 A 1
6 B 2
要求输入<KeyName, KeyValue>[],是KeyName和Value匹配对的数组,
得到查询结果为具有这些数组对的ID。
如:
输入(KeyName, KeyValue)
A 1
B 2
结果(ID)
1
2
4
写SQL语句。
PS:我可以把输入存成临时表,然后要怎么用呢?
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [ dbo ] . [ ShowId ]
(
@inputString varchar ( max )
)
AS
DECLARE @PointerPrev int
DECLARE @PointerCurr int
DECLARE @spaceIndex int
DECLARE @subString varchar ( 50 )
DECLARE @keyWord varchar ( 50 )
DECLARE @value int
Set @PointerPrev = 0
set @PointerCurr = 0
-- SET @inputString = 'A,1;B,2'
CREATE TABLE # temp
(
name varchar ( 50 ),
value int
)
begin transaction
SET NOCOUNT ON ;
truncate table # temp -- 清空临时表的所有数据和约束
while ( LEN ( @inputString ) > @PointerCurr )
begin
Set @PointerCurr = CharIndex ( ' ; ' , @inputString , @PointerPrev + 1 )
if ( @PointerCurr <= 0 )
break
else
begin
set @subString = SUBSTRING ( @inputString , @PointerPrev + 1 , @PointerCurr - @PointerPrev - 1 )
set @spaceIndex = CharIndex ( ' , ' , @subString , 1 )
set @keyWord = SUBSTRING ( @subString , 1 , @spaceIndex - 1 )
set @value = CAST ( SUBSTRING ( @subString , @spaceIndex + 1 , LEN ( @subString ) - @spaceIndex ) as int )
INSERT INTO # temp VALUES ( @keyWord , @value )
set @PointerPrev = @PointerCurr ;
end
end
set @subString = SUBSTRING ( @inputString , @PointerPrev + 1 , LEN ( @inputString ) - @PointerPrev )
set @spaceIndex = CharIndex ( ' , ' , @subString , 1 )
set @keyWord = SUBSTRING ( @subString , 1 , @spaceIndex - 1 )
set @value = CAST ( SUBSTRING ( @subString , @spaceIndex + 1 , LEN ( @subString ) - @spaceIndex ) as int )
INSERT INTO # temp VALUES ( @keyWord , @value )
Set NoCount OFF
if @@error = 0
begin
commit transaction
end
else
begin
rollback transaction
end
-- --------------------------这里开始进入正题
select distinct id from dbo.Key_Value kv
where not exists
(
select name,value from # temp
except
select Keyword,value from dbo.Key_Value where id = kv.id
);
select name,value from #temp
except
select Keyword,value from dbo.Key_Value where id=5
结果为:
name value
-------------------------------------------------- -----------
B 2