有两个表:UserClass和UserFile,数据分别如下:
UserClass:
userClassID userID userClassNo
---------------------------------------
1 1 0001
2 1 00010001
3 1 000100010001
4 1 000100010002
5 1 000100010003
6 1 00010002
7 1 000100020001
8 1 000100020002
9 2 00010002
10 2 000100020001
11 2 000100020002UserFile:
userFileID userID userClassNo
---------------------------------------------
1 1 000100020002
2 2 000100020001现在我想根据UserFile表,获得UserClass表的记录集合,关键是两个表userID相同,然后UserClass中显示包含UserFile的所有父分类,即:
返回UserClass:
userClassID userID userClassNo
---------------------------------------
1 1 0001
6 1 00010002
8 1 000100020002
9 2 00010002
10 2 000100020001这样的语句怎么写?分不够再加啊~~~
UserClass:
userClassID userID userClassNo
---------------------------------------
1 1 0001
2 1 00010001
3 1 000100010001
4 1 000100010002
5 1 000100010003
6 1 00010002
7 1 000100020001
8 1 000100020002
9 2 00010002
10 2 000100020001
11 2 000100020002UserFile:
userFileID userID userClassNo
---------------------------------------------
1 1 000100020002
2 2 000100020001现在我想根据UserFile表,获得UserClass表的记录集合,关键是两个表userID相同,然后UserClass中显示包含UserFile的所有父分类,即:
返回UserClass:
userClassID userID userClassNo
---------------------------------------
1 1 0001
6 1 00010002
8 1 000100020002
9 2 00010002
10 2 000100020001这样的语句怎么写?分不够再加啊~~~
FROM UserClass uc LEFT OUTER JOIN
UserFile uf ON uc.userID = uf.userID AND uc.userClassNo IN(
SELECT * FROM uf_GetParentClassList(uf.userClassNo))其中,uf_GetParentClassList()是一个用户定义函数,可以根据传入的编号返回一个内嵌表,比如输入000100020002返回下列表:
000100020002
00010002
0001
定义如下:
CREATE FUNCTION uf_GetParentClassList (@strClassNo VARCHAR(100))
RETURNS @tblClass TABLE (classNo VARCHAR(100)) AS
BEGIN IF LEN(@strClassNo)<4 RETURN INSERT @tblClass VALUES(@strClassNo) WHILE(LEN(@strClassNo)>4)
BEGIN
SET @strClassNo=SUBSTRING(@strClassNo,1,LEN(@strClassNo)-4)
INSERT @tblClass VALUES(@strClassNo)
END RETURN
END
然而上面的语句缺无法执行,返回下列错误信息:
服务器: 消息 155,级别 15,状态 1,行 14
'grpClassNo' 不是可以识别的 OPTIMIZER LOCK HINTS 选项。
where exists (
select 1 from UserFile b
where b.userID=a.userID
and b.userClassNo like rtrim(userClassNo)+'%'
)
From UserClass A
Inner Join UserFile B
On A.userID = B.userID And B.userClassNo Like '%' + A.userClassNo + '%'
(userClassID Int,
userID Int,
userClassNo Varchar(50))
Insert UserClass Select 1,1,'0001'
Union All Select 2,1,'00010001'
Union All Select 3, 1,'000100010001'
Union All Select 4,1,'000100010002'
Union All Select 5,1,'000100010003'
Union All Select 6,1,'00010002'
Union All Select 7,1,'000100020001'
Union All Select 8,1,'000100020002'
Union All Select 9,2,'00010002'
Union All Select 10, 2,'000100020001'
Union All Select 11, 2,'000100020002'Create Table UserFile
(userFileID Int,
userID Int,
userClassNo Varchar(50))
Insert UserFile Select 1,1,'000100020002'
Union All Select 2,2,'000100020001'
GO
Select A.*
From UserClass A
Inner Join UserFile B
On A.userID = B.userID And B.userClassNo Like A.userClassNo + '%'
GO
Drop Table UserFile, UserClass
--Result
/*
userClassID userID userClassNo
1 1 0001
6 1 00010002
8 1 000100020002
9 2 00010002
10 2 000100020001
*/
declare @UserClass table (
userClassID int,
userID int,
userClassNo varchar(30)
)insert @UserClass
select
1,1,'0001'
union all select
2,1,'00010001'
union all select
3,1, '000100010001'
union all select
4,1,'000100010002'
union all select
5,1,'000100010003'
union all select
6,1,'00010002'
union all select
7,1,'000100020001'
union all select
8,1,'000100020002'
union all select
9,2,'00010002'
union all select
10, 2,'000100020001'
union all select
11, 2,'000100020002'
declare @UserFile table(
userFileID int,
userID int,
userClassNo varchar(30)
)insert @UserFile
select
1,1,'000100020002'
union all select
2,2,'000100020001'select * from @UserClass a
where exists (
select 1 from @UserFile b
where b.userID=a.userID
and b.userClassNo like rtrim(a.userClassNo)+'%'
)--结果
userClassID userID userClassNo
----------- ----------- ------------------------------
1 1 0001
6 1 00010002
8 1 000100020002
9 2 00010002
10 2 000100020001(所影响的行数为 5 行)
create table UserClass(userClassID int,userID int,userClassNo varchar(50))
create table UserFile(userFileID int,userID int,userClassNo varchar(50))
--插入测试数据
insert UserClass(userClassID,userID,userClassNo)
select '1','1','0001' union all
select '2','1','00010001' union all
select '3','1','000100010001' union all
select '4','1','000100010002' union all
select '5','1','000100010003' union all
select '6','1','00010002' union all
select '7','1','000100020001' union all
select '8','1','000100020002' union all
select '9','2','00010002' union all
select '10','2','000100020001' union all
select '11','2','000100020002'insert UserFile(userFileID,userID,userClassNo)
select '1','1','000100020002' union all
select '2','2','000100020001'--求解过程
Select _uc.*
From UserClass _uc
Join UserFile _uf
On _uc.userID = _uf.userID And _uf.userClassNo Like _uc.userClassNo + '%'
--删除测试环境
drop table UserClass
drop table UserFile
/*--测试结果
userClassID userID userClassNo
----------- ----------- --------------------------------------------------
1 1 0001
6 1 00010002
8 1 000100020002
9 2 00010002
10 2 000100020001(所影响的行数为 5 行)
*/