1 select * from t_userinfo
2 select * from t_userqx
3 select * from t_cityinfo
4 select c.cityName,c.cityCode ,b.cityid as cityid,c.citycode from t_userinfo a,t_userqx b,t_cityinfo c
where a.id=b.userid and c.ShengF='湖南省' and a.uid='test'得到的结果集分别是:1 :
id uid upwd uname tel memo
--- ------------- -------------------- ----------------------
1 test E866F9BEDF349304 test 051245909090 sdf
2 test1 test test 051245909090 sdf2 :
UserId CityID
----------- ------
1 1,2,33 :
CityCode CityName ShengF AddTime AddUser
-------- ---------
1 长沙 湖南省 2009-12-23 10:12:26.717 1
2 深圳 广东省 2009-12-23 10:12:41.500 2
3 上海 上海市 2009-12-23 10:12:52.420 34:
cityName cityCode cityid citycode
-------- -------- ------ --------
长沙 1 1,2,3 1现在问题是如果第4条加个条件:
and c.citycode in (b.cityid)就没有数据了 问题是此时(结果4)b.cityid 为 1,2,3
我直接写成and c.citycode in (1,2,3)
就和结果4 一样
怎样解决?谢谢
,2,
,3,,1,2,3,这样能看明白吗?这样也行.
','+b.cityid+',' like '%,'+ltrim(c.citycode)+',%'
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-12-23 15:31:24
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[t_userinfo]') IS NOT NULL
DROP TABLE [t_userinfo]
go
CREATE TABLE [t_userinfo] (id INT,uid VARCHAR(5),upwd VARCHAR(16),uname VARCHAR(4),tel VARCHAR(12),memo VARCHAR(3))
INSERT INTO [t_userinfo]
SELECT 1,'test','E866F9BEDF349304','test','051245909090','sdf' UNION ALL
SELECT 2,'test1','test','test','051245909090','sdf'select * from [t_userinfo]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-12-23 15:31:48
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[t_userqx]') IS NOT NULL
DROP TABLE [t_userqx]
go
CREATE TABLE [t_userqx] (UserId INT,CityID INT,c3 INT,c4 INT)
INSERT INTO [t_userqx]
SELECT 1,1,2,3select * from [t_userqx]
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-12-23 15:32:11
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[t_cityinfo]') IS NOT NULL
DROP TABLE [t_cityinfo]
go
CREATE TABLE [t_cityinfo] (CityCode INT,CityName VARCHAR(4),ShengF VARCHAR(6),AddTime DATETIME,AddUser INT)
INSERT INTO [t_cityinfo]
SELECT 1,'长沙','湖南省','2009-12-23 10:12:26.717',1 UNION ALL
SELECT 2,'深圳','广东省','2009-12-23 10:12:41.500',2 UNION ALL
SELECT 3,'上海','上海市','2009-12-23 10:12:52.420',3select * from [t_cityinfo]select c.cityName,c.cityCode ,b.cityid as cityid,c.citycode from t_userinfo a,t_userqx b,t_cityinfo c
where a.id=b.userid and c.ShengF='湖南省' and a.uid='test' and c.citycode in (b.cityid)cityName cityCode cityid citycode
长沙 1 1 1
有数据的,楼主在仔细检查下