表jieyue duzhebianhao shuji1 shuji2 shuji3
1 qwe zxc aaa
表book tushubianhao tushumingcheng shuliang
1 qwe 10
2 asd 5
3 zxc 10
4 aaa 6 查询读者编号 获得获得图书名称 再从book表中查出qwe zxc aaa 3本书的所有信息语句怎么写啊?
1 qwe zxc aaa
表book tushubianhao tushumingcheng shuliang
1 qwe 10
2 asd 5
3 zxc 10
4 aaa 6 查询读者编号 获得获得图书名称 再从book表中查出qwe zxc aaa 3本书的所有信息语句怎么写啊?
B.*
FROM (
SELECT duzhebianhao,shuji1 AS shuji FROM jieyue WHERE shuji1 IS NOT NULL
UNION ALL
SELECT duzhebianhao,shuji2 FROM jieyue WHERE shuji2 IS NOT NULL
UNION ALL
SELECT duzhebianhao,shuji3 FROM jieyue WHERE shuji3 IS NOT NULL
) AS A
JOIN book AS B
ON A.shuji=B.tushumingcheng
*
from
book
where
tushumingcheng in
(
select shuji
from
(
select duzhebianhao,shuji1 as shuji from jieyue
union all select duzhebianhao,shuji2 from jieyue
union all select duzhebianhao,shuji3 from jieyue
) t
where duzhebianhao=1 --在这里输入读者编号
)
select b.duzhebianhao,a.tushumingcheng
from jieyue a
inner join book b on a.tushubianhao=b.tushubianhao
再从book表中查出qwe zxc aaa 3本书的所有信息
select *
from book
where tushumingcheng in('qwe','zxc','aaa')
;with cte as
(
SELECT duzhebianhao,shuji1 AS shuji FROM jieyue WHERE shuji1 IS NOT NULL
UNION ALL
SELECT duzhebianhao,shuji2 FROM jieyue WHERE shuji2 IS NOT NULL
UNION ALL
SELECT duzhebianhao,shuji3 FROM jieyue WHERE shuji3 IS NOT NULL)
SELECT
B.*
FROM cte AS A
JOIN book AS B
ON A.shuji=B.tushumingcheng
(select shuji1 from
(select duzhebianhao,shuji1 from jieyue
union all
select duzhebianhao,shuji2 from jieyue
union all
select duzhebianhao,shuji3 from jieyue) a where duzhebianhao=@读者编号)
from book a
where charindex(','+tushumingcheng +',',(select ','+shuji1+','+ shuji2+',' shuji3+',' from jieye where duzhebianhao = @i ))>0
where a.tushumingcheng=b.shuji1 or a.tushumingcheng=b.shuji2 or a.tushumingcheng=b.shuji3
-- Author: happyflystone
-- Date : 2009-04-25 21:12:43
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: book
IF OBJECT_ID('book') IS NOT NULL
DROP TABLE book
Go
CREATE TABLE book(tushubianhao INT,tushumingcheng NVARCHAR(3),shuliang INT)
Go
INSERT INTO book
SELECT 1,'qwe',10 UNION ALL
SELECT 2,'asd',5 UNION ALL
SELECT 3,'zxc',10 UNION ALL
SELECT 4,'aaa',6
GO
-- Test Data: jieyue
IF OBJECT_ID('jieyue') IS NOT NULL
DROP TABLE jieyue
Go
CREATE TABLE jieyue(duzhebianhao INT,shuji1 NVARCHAR(3),shuji2 NVARCHAR(3),shuji3 NVARCHAR(3))
Go
INSERT INTO jieyue
SELECT 1,'qwe','zxc','aaa'
GO
--Start
select *
from book a
where charindex(','+tushumingcheng +',',(select ','+shuji1+','+ shuji2+','+ shuji3+',' from jieyue where duzhebianhao = 1 ))>0
--Result:
/*
tushubianhao tushumingcheng shuliang
------------ -------------- -----------
1 qwe 10
3 zxc 10
4 aaa 6
*/
--End