select * from t2 where charindex(id,select t2_id from t1 where name like '%成都店%') > 0
select * from t2 where charindex(id,select t2_id from t1 where name like '%成都店%') > 0
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-28 11:57: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.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[T1] if object_id('[T1]') is not null drop table [T1] go create table [T1]([ID] int,[Name] varchar(6),[T2_ID] varchar(5)) insert [T1] select 1,'成都店','1,2,3' union all select 2,'北京店','2,3' --> 测试数据:[T2] if object_id('[T2]') is not null drop table [T2] go create table [T2]([ID] int,[Name] varchar(7)) insert [T2] select 1,'豪华型' union all select 2,'标准型' union all select 3,'VIP专用' --------------开始查询-------------------------- select b.name from t1 a,t2 b where charindex(','+ltrim(b.id)+',',','+a.T2_ID+',')>0 and a.Name='成都店' ----------------结果---------------------------- /*name ------- 豪华型 标准型 VIP专用(3 行受影响)
*/
select a.name from t2 a join t1 b on charindex(','+ltrim(a.id)+',',','+b.T2_ID+',')>0
--> 测试数据:[T1] if object_id('[T1]') is not null drop table [T1] create table [T1]([ID] int,[Name] varchar(6),[T2_ID] varchar(5)) go insert [T1] select 1,'成都店','1,2,3' union all select 2,'北京店','2,3' --> 测试数据:[T2] if object_id('[T2]') is not null drop table [T2] create table [T2]([ID] int,[Name] varchar(7)) go insert [T2] select 1,'豪华型' union all select 2,'标准型' union all select 3,'VIP专用'select t.id,t.name,r.name from [T1] t join [T2] r on charindex(','+ltrim(r.id)+',',','+t.t2_id+',') > 0 where t.name = '成都店'id name name ----------- ------ ------- 1 成都店 豪华型 1 成都店 标准型 1 成都店 VIP专用(3 行受影响)
select a.name from t2 a join t1 b on charindex(','+ltrim(a.id)+',',','+b.T2_ID+',')>0 and b.name='成都店'补个条件
select t2.name from t2,t1 WHERE charindex(','+ltrim(t2.id)+',',','+t2_id+',')>0 AND t1.name ='成都店'
if object_id('[T1]') is not null drop table [T1] go create table [T1]([ID] int,[Name] varchar(6),[T2_ID] varchar(5)) insert [T1] select 1,'成都店','1,2,3' union all select 2,'北京店','2,3' --> 测试数据:[T2] if object_id('[T2]') is not null drop table [T2] go create table [T2]([ID] int,[Name] varchar(7)) insert [T2] select 1,'豪华型' union all select 2,'标准型' union all select 3,'VIP专用'select * from t2 where charindex(cast(id as varchar(10)),(select t2_id from t1 where name like '%成都店%')) > 0 ----------- ID Name ----------- ------- 1 豪华型 2 标准型 3 VIP专用(3 行受影响)
select * from t2 where charindex(id,select t2_id from t1 where name like '%成都店%') > 0
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-28 11:57: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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([ID] int,[Name] varchar(6),[T2_ID] varchar(5))
insert [T1]
select 1,'成都店','1,2,3' union all
select 2,'北京店','2,3'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([ID] int,[Name] varchar(7))
insert [T2]
select 1,'豪华型' union all
select 2,'标准型' union all
select 3,'VIP专用'
--------------开始查询--------------------------
select
b.name
from
t1 a,t2 b
where
charindex(','+ltrim(b.id)+',',','+a.T2_ID+',')>0
and
a.Name='成都店'
----------------结果----------------------------
/*name
-------
豪华型
标准型
VIP专用(3 行受影响)
*/
if object_id('[T1]') is not null drop table [T1]
create table [T1]([ID] int,[Name] varchar(6),[T2_ID] varchar(5))
go
insert [T1]
select 1,'成都店','1,2,3' union all
select 2,'北京店','2,3'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
create table [T2]([ID] int,[Name] varchar(7))
go
insert [T2]
select 1,'豪华型' union all
select 2,'标准型' union all
select 3,'VIP专用'select t.id,t.name,r.name
from [T1] t join [T2] r
on charindex(','+ltrim(r.id)+',',','+t.t2_id+',') > 0
where t.name = '成都店'id name name
----------- ------ -------
1 成都店 豪华型
1 成都店 标准型
1 成都店 VIP专用(3 行受影响)
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([ID] int,[Name] varchar(6),[T2_ID] varchar(5))
insert [T1]
select 1,'成都店','1,2,3' union all
select 2,'北京店','2,3'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([ID] int,[Name] varchar(7))
insert [T2]
select 1,'豪华型' union all
select 2,'标准型' union all
select 3,'VIP专用'select * from t2
where charindex(cast(id as varchar(10)),(select t2_id from t1 where name like '%成都店%')) > 0
-----------
ID Name
----------- -------
1 豪华型
2 标准型
3 VIP专用(3 行受影响)