抢分题，在线等

[vsstudio05]

数据库有2张表：
表A
字段oth
;rssid=72;edis=1
;edis=5;rssid=62
;rssid=2;edis=2;
;edis=6;rssid=3;
;edis=9;rssid=3;


表B
rssid
1
2
3
62
===============================================
现在想通过一句sql语句联合查询A,B两表，查询条件为A.oth内的rssid的值等于表B的rssid

[bancxc]

select * from 
A join 
(
select rssid=';rssid='+ltrim(rssid)
from B
)B on charindex(B.rssid,A.oth)>0

[guguda2008]

SELECT * 
FROM A
INNER JOIN B ON CHARINDEX(CAST(B.RSSID AS VARCHAR(50))+';'
,A.OTH+';'
)>0

[jwdream2008]

select A.*,B.* from  from 表A  A inner join 表B B 
ON Charindex(';rssid='+B.rssid+';',A.oth+';')>0

[SQL77]

SELECT * FROM A ,
(SELECT B =';rssid='+ltrim(rssid)+';' FROM B )AS B 
WHERE CHARINDEX(B.B,A.oth+';')>0

[bancxc]

create table A (oth varchar(50))
insert into A select
';rssid=72;edis=1' union all select
';edis=5;rssid=62' union all select
';rssid=2;edis=2;' union all select
';edis=6;rssid=3;' union all select
';edis=9;rssid=3;' 
go

create table B (rssid int)
insert into B
select 1 union all
select 2 union all
select 3 union all
select 62

select * from 
A join 
(
select rssid=';rssid='+ltrim(rssid)
from B
)B on charindex(B.rssid,A.oth)>0

oth                                                rssid
-------------------------------------------------- -------------------
;rssid=2;edis=2;                                   ;rssid=2
;edis=6;rssid=3;                                   ;rssid=3
;edis=9;rssid=3;                                   ;rssid=3
;edis=5;rssid=62                                   ;rssid=62

(4 行受影响)


drop table A,B
 

[ws_hgo]

抢分
人齐结贴！~

[bancxc]

create table A (oth varchar(50))
insert into A select
';rssid=72;edis=1' union all select
';edis=5;rssid=62' union all select
';rssid=2;edis=2;' union all select
';edis=6;rssid=3;' union all select
';edis=9;rssid=3;' 
go

create table B (rssid int)
insert into B
select 1 union all
select 2 union all
select 3 union all
select 62

select * from 
A join  B on charindex(';rssid='+ltrim(B.rssid),A.oth)>0

oth                                                rssid
-------------------------------------------------- -----------
;rssid=2;edis=2;                                   2
;edis=6;rssid=3;                                   3
;edis=9;rssid=3;                                   3
;edis=5;rssid=62                                   62

(4 行受影响)


drop table A,B

[guguda2008]

IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(OTH VARCHAR(100))
INSERT INTO A
SELECT ';rssid=72;edis=1' UNION ALL
SELECT ';edis=5;rssid=62' UNION ALL
SELECT ';rssid=2;edis=2;' UNION ALL
SELECT ';edis=6;rssid=3;' UNION ALL
SELECT ';edis=9;rssid=3;'
CREATE TABLE B(rssid INT)
INSERT INTO B
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 62  
SELECT * FROM A 
INNER JOIN B ON 
CHARINDEX('='+CAST(B.RSSID AS VARCHAR(50))+';' 
,A.OTH+';' )>0
/*
;rssid=72;edis=1	1
;rssid=2;edis=2;	2
;edis=6;rssid=3;	3
;edis=9;rssid=3;	3
;edis=5;rssid=62	62
*/

[Mr_Nice]

--> 测试数据：[表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A]([oth] varchar(16))
insert [表A]
select ';rssid=72;edis=1' union all
select ';edis=5;rssid=62' union all
select ';rssid=2;edis=2;' union all
select ';edis=6;rssid=3;' union all
select ';edis=9;rssid=3;'

select * from [表A]



--> 测试数据：[表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B]([rssid] int)
insert [表B]
select 1 union all
select 2 union all
select 3 union all
select 62

select * from [表B]



--====================================

select [表A].* from 
[表A] join 
(select rssid=';rssid='+ltrim(rssid)
from [表B]
)TB on charindex(TB.rssid,[表A].oth)>0


---结果===============================
;rssid=2;edis=2;
;edis=6;rssid=3;
;edis=9;rssid=3;
;edis=5;rssid=62

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2010-01-14 15:53:24
-- 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)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([oth] varchar(16))
insert [A]
select ';rssid=72;edis=1' union all
select ';edis=5;rssid=62' union all
select ';rssid=2;edis=2;' union all
select ';edis=6;rssid=3;' union all
select ';edis=9;rssid=3;'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([rssid] int)
insert [B]
select 1 union all
select 2 union all
select 3 union all
select 62
--------------开始查询--------------------------
select
 * 
from 
 a 
join 
(select rssid=';rssid='+ltrim(rssid) from B)B
on
 charindex(B.rssid,A.oth)>0
----------------结果----------------------------
/* oth              rssid
---------------- -------------------
;rssid=2;edis=2; ;rssid=2
;edis=6;rssid=3; ;rssid=3
;edis=9;rssid=3; ;rssid=3
;edis=5;rssid=62 ;rssid=62

(4 行受影响)

*/

[andysun88]

1楼经典，简洁

[借我那把枪吧]

1楼经典