22,301
社区成员




--方式一:
SELECT UserID FROM UserTable WHERE ClientIp in (SELECT ClientIp FROM LoginTable WHERE USERID=1234)
UNION ALL
SELECT UserID FROM UserTable1 WHERE ClientIp in (SELECT ClientIp FROM LoginTable WHERE USERID=1234)
UNION ALL
SELECT UserID FROM UserTable2 WHERE ClientIp in (SELECT ClientIp FROM LoginTable WHERE USERID=1234)
UNION ALL
SELECT UserID FROM UserTable3 WHERE ClientIp in (SELECT ClientIp FROM LoginTable WHERE USERID=1234)
UNION ALL
SELECT UserID FROM UserTable4 WHERE ClientIp in (SELECT ClientIp FROM LoginTable WHERE USERID=1234)
--方式二:
DECLARE @ClientIp VARCHAR(max)
SELECT @ClientIp =@ClientIp + QUOTENAME(ClientIp,'''')+'',''FROM LoginTable WHERE USERID=1234
--把所有的IP地址都拼接成一个字符串,然后动态执行SQL
SELECT UserID FROM UserTable WHERE ClientIp in @ClientIp
UNION ALL
SELECT UserID FROM UserTable1 WHERE ClientIp in @ClientIp
UNION ALL
SELECT UserID FROM UserTable2 WHERE ClientIp in @ClientIp
UNION ALL
SELECT UserID FROM UserTable3 WHERE ClientIp in @ClientIp
UNION ALL
SELECT UserID FROM UserTable4 WHERE ClientIp in @ClientIp
SELECT UserID FROM UserTable , 临时表 WHERE UserTable.ClientIp=临时表.ClientIp
嗯..不好意思,为了简洁,我只是写了个大概
DECLARE @ClientIp VARCHAR(max)
SELECT @ClientIp =@ClientIp + QUOTENAME(ClientIp,'''')+'',''FROM LoginTable WHERE USERID=1234
SELECT @ClientIp='('+SUBSTRING(@ClientIp,1,LEN(@ClientIp)-1)+')'
--把所有的IP地址都拼接成一个字符串,然后动态执行SQL
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL=
'SELECT UserID FROM UserTable WHERE ClientIp in ' +@ClientIp +
'UNION ALL '
'SELECT UserID FROM UserTable1 WHERE ClientIp in '+ @ClientIp +
'UNION ALL '
'SELECT UserID FROM UserTable2 WHERE ClientIp in '+ @ClientIp +
'UNION ALL '
'SELECT UserID FROM UserTable3 WHERE ClientIp in ' + @ClientIp+
'UNION ALL '
'SELECT UserID FROM UserTable4 WHERE ClientIp in '+ @ClientIp
SELECT UserID FROM UserTable a,(SELECT ClientIp FROM LoginTable WHERE USERID=1234) b WHERE a.ClientIp =b.ClientIp
UNION ALL
SELECT UserID FROM UserTable1 a (SELECT ClientIp FROM LoginTable WHERE USERID=1234) b WHERE a.ClientIp =b.ClientIp
UNION ALL
SELECT UserID FROM UserTable2 a (SELECT ClientIp FROM LoginTable WHERE USERID=1234) b WHERE a.ClientIp =b.ClientIp
UNION ALL
SELECT UserID FROM UserTable3 a (SELECT ClientIp FROM LoginTable WHERE USERID=1234) b WHERE a.ClientIp =b.ClientIp
UNION ALL
SELECT UserID FROM UserTable4 a (SELECT ClientIp FROM LoginTable WHERE USERID=1234) b WHERE a.ClientIp =b.ClientIp