日期:2014-05-18  浏览次数:20416 次

查询疑问
执行以下语句1:
SQL code
SELECT EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = 'hs'

得到结果:
SQL code
'加工中心1','加工中心2','加工中心3'



执行以下语句2:
SQL code
SELECT 
       [JO002]
      ,[JO003]

FROM [EAPS].[dbo].[APSJOB]
where
JO015 IN ('加工中心1','加工中心2','加工中心3')

可以查询出数据

执行以下语句3:
SQL code
SELECT 
       [JO002]
      ,[JO003]

FROM     [EAPS].[dbo].[APSJOB]
where
JO015 IN (SELECT EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = 'hs')

结果为空

想请教下原因



------解决方案--------------------
SELECT EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = 'hs'

--取出来的数据是 '加工中心1','加工中心2','加工中心3' 这个作为一个完整的字符串用in 肯定是不行的。

SQL code
declare @sql nvarchar(4000)

set @sql = 'SELECT 
       [JO002]
      ,[JO003]
FROM  [EAPS].[dbo].[APSJOB]
where
JO015 IN ('+SELECT EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = 'hs'+')'

exec(@sql)

------解决方案--------------------
SQL code


DECLARE @STR VARCHAR(200)
SET @STR=''
EXEC('
SELECT @STR=EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = ''''hs'''''
SELECT 
       [JO002]
      ,[JO003]

FROM     [EAPS].[dbo].[APSJOB]
where
JO015 IN ('+@STR+')'

------解决方案--------------------
SQL code
SELECT [JO002],
       [JO003]
FROM   [EAPS].[dbo].[APSJOB] TA
WHERE  EXISTS (
           SELECT 1
           FROM   [EAPS].[dbo].APSEMP TB
           WHERE  TB.EM001 = 'hs'
                  AND TA.J0015 = TB.EM007
       )

------解决方案--------------------
SQL code



DECLARE @STR VARCHAR(200)
SET @STR=''

SELECT @STR=EM007 FROM [EAPS].[dbo].APSEMP WHERE EM001 = 'hs'

EXEC('
SELECT 
       [JO002]
      ,[JO003]

FROM     [EAPS].[dbo].[APSJOB]
where
JO015 IN ('+@STR+')'