表1
id uid
1 test
表2
UserId CityID
1 1,2,3
表3
CityCode CityName
1 XXX
其中表1 id关联 表2 UserId 表2 CityID 对应表3 CityCode 多对一
现在想找test 对应操作哪个城市的名称 此处XXX
我这样写为什么不对?select * from t_userinfo a ,t_userqx b,t_cityinfo c
where a.id=b.userid and charindex(','+ltrim(c.citycode)+',',','+b.cityid+',')>0
and a.uid='test'
id uid
1 test
表2
UserId CityID
1 1,2,3
表3
CityCode CityName
1 XXX
其中表1 id关联 表2 UserId 表2 CityID 对应表3 CityCode 多对一
现在想找test 对应操作哪个城市的名称 此处XXX
我这样写为什么不对?select * from t_userinfo a ,t_userqx b,t_cityinfo c
where a.id=b.userid and charindex(','+ltrim(c.citycode)+',',','+b.cityid+',')>0
and a.uid='test'
where a.id=b.userid and charindex(','+ltrim(c.citycode)+',',','+b.cityid+',')>0
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-25 13:06:25
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[t_userinfo]
if object_id('[t_userinfo]') is not null drop table [t_userinfo]
go
create table [t_userinfo]([id] int,[uid] varchar(4))
insert [t_userinfo]
select 1,'test'
--> 测试数据:[t_userqx]
if object_id('[t_userqx]') is not null drop table [t_userqx]
go
create table [t_userqx]([UserId] int,[CityID] varchar(5))
insert [t_userqx]
select 1,'1,2,3'
--> 测试数据:[t_cityinfo]
if object_id('[t_cityinfo]') is not null drop table [t_cityinfo]
go
create table [t_cityinfo]([CityCode] int,[CityName] varchar(3))
insert [t_cityinfo]
select 1,'XXX'
--------------开始查询--------------------------
select
*
from
t_userinfo a ,t_userqx b,t_cityinfo c
where
a.id=b.userid
and
charindex(','+ltrim(c.citycode)+',',','+b.cityid+',')>0
and
a.uid='test'----------------结果----------------------------
/* id uid UserId CityID CityCode CityName
----------- ---- ----------- ------ ----------- --------
1 test 1 1,2,3 1 XXX(1 行受影响)
*/
--> Author : 各位大大,本大俠只想要顆星星
--> Date : 2009-12-25 13:06:16
if object_id('[ta]') is not null drop table [ta]
go
create table [ta] (id int,uid nvarchar(8))
insert into [ta]
select 1,'test'
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (UserId int,CityID varchar(10))
insert into [tb]
select 1,'1,2,3'
if object_id('[tc]') is not null drop table [tc]
go
create table [tc] (CityCode int,CityName nvarchar(6))
insert into [tc]
select 1,'XXX'
select *
from ta a ,tb b,tc c
where a.id=b.userid and charindex(','+ltrim(c.citycode)+',',','+b.cityid+',')>0
and a.uid='test'
/*
id uid UserId CityID CityCode CityName
----------- -------- ----------- ---------- ----------- --------
1 test 1 1,2,3 1 XXX
*/
但t_userqx 表里面test3 确实有3 这个城市编号啊表1
1 test E866F9BEDF349304 test 051245909090 sdf
2 test1 E866F9BEDF349304 test 051245909090 sdf
9 test3 E866F9BEDF349304 test NULL NULL
表2
1 1,2,3
2 2,3
3 1
表3
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 3
4 湘潭 湖南省 2009-12-23 17:10:00.043 4
LZ检查仔细了,再好好看看