SQL查询，求解

[wingtech5323]

表A
ck_imei ck_model
5323 GQ728

表B
inbox_mobile inbox_msg
15842569863 a,0,333,22,0,V53B73,5323

怎么 根据 ck_model查出表B 的所有内容呢？

注：ck_imei与inbox_msg中最后一个逗号后的字符一样

[linguojin11]

set nocount on
go
if object_id('testa') is not null drop table testa
create table testa (ck_imei int,ck_model varchar(5))
insert into testa
select 5323,'GQ728'

--> 测试数据: testb
if object_id('testb') is not null drop table testb
create table testb (inbox_mobile bigint,inbox_msg varchar(100))
insert into testb
select 15842569863,'a,0,333,22,0,V53B73,5323'
union
select 15842569863,'a,0,333,5323,0,V53B73,532'
go
select testb.*
from testa inner join testb on ','+ltrim(testa.ck_imei)=right(testb.inbox_msg,len(testa.ck_imei)+1)
-------------
/*
inbox_mobile         inbox_msg                                                                                            
-------------------- ---------------------------------------------------------------------------------------------------- 
15842569863          a,0,333,22,0,V53B73,5323


*/

[百年树人]

select b.*
from a,b
where chaindex(','+ltrim(a.ck_imei)+',',','+b.inbox_msg+',')>0
and a.ck_model='GQ728'

[百年树人]

[Quote=引用 1 楼 josy 的回复:]
SQL code
select b.*
from a,b
where chaindex(','+ltrim(a.ck_imei)+',',','+b.inbox_msg+',')>0
and a.ck_model='GQ728'
[/Quote]
修正笔误

select b.*
from a,b
where charindex(','+ltrim(a.ck_imei)+',',','+b.inbox_msg+',')>0
and a.ck_model='GQ728'

[abuying]

select * from B where exists(select 1 from A where right(b.inbox_msg,len(A.ck_imei))=A.ck_imei)

[fpzgm]

--> 测试数据: [表A]
if object_id('[表A]') is not null drop table [表A]
create table [表A] (ck_imei int,ck_model varchar(5))
insert into [表A]
select 5323,'GQ728'
--> 测试数据: [表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B] (inbox_mobile bigint,inbox_msg varchar(100))
insert into [表B]
select 15842569863,'a,0,333,22,0,V53B73,5323'
union
select 15842569863,'a,0,333,5323,0,V53B73,532'        --中间有，最后不是

select t2.* from [表A] t1,[表B] t2
where t1.ck_imei=reverse(left(reverse(t2.inbox_msg),CHARINDEX(',',reverse(t2.inbox_msg))-1)) --保证最后一个，而中间有的不会出现
and  t1.ck_model='GQ728'
  

/*
inbox_mobile	inbox_msg
15842569863	a,0,333,22,0,V53B73,5323
*/

[csyue007]

要西 正好 我不会

[jiemo587]

create table #表A (ck_imei int,ck_model varchar(5))
insert into #表A
select 5323,'GQ728'

create table #表B (inbox_mobile bigint,inbox_msg varchar(100))
insert into #表B
select 15842569863,'a,0,333,22,0,V53B73,5323'

select t2.* from #表A t1,#表B t2
where charindex(convert(nvarchar,ck_imei),inbox_msg)>0
and t1.ck_model='GQ728'

CHARINDEX
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX(<’substring_expression’>，<expression>)
其中substring_expression是所要查找的字符表达式，expression可为字符串也可为列名表达式。如果没有发现子串，则返回0值。
此函数不能用于TEXT和IMAGE数据类型。