数据库关联表查询

有问有答 2017-11-01 09:54:38

其中a表的mtp字段中的值分别关联的是b表的vl的值，要求查询的时候将a表的mtp值全部转换成内容(例：100, '折,戟,沉,沙')

a表 id mtp
100 1,2,3,4

b表 id vl ct
99 1 折
100 2 戟
101 3 沉
102 4 沙
103 5 莫
这个应该怎么写sql？求大神帮助

...全文

643 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

jinhx 2017-11-01

打赏
举报

题主应该是要把mtp直接转为汉字，而不是一般的联合查询，需要自定义函数处理mtp字段，最后用select id,dbo.自定义(mtp) from a

0与1之间 2017-11-01

打赏
举报

with t1(id,mtp) as ( select 100,'1,2,3' ),t2(id,vl,ct) as ( select 99,1,N'折' union all select 100,2,N'戟' union all select 101,3,N'沉' union all select 102,4,N'沙' union all select 103,5,N'莫' ) select id,stuff(( select ','+ct from t2 where vl in ( select id from ( select [value]=convert(XML,'<v>'+replace('1,2,3,4',',','</v><v>')+'</v>') ) A cross apply (select id=N.v.value('.','nvarchar(200)') from A.value.nodes('v')N(v))B ) for xml path('')),1,1,'') as mtp from t1

听雨停了 2017-11-01

打赏
举报



#测试数据

create table tabA(

id int,

mtp varchar(200)

);

insert into tabA VALUES(100,'1,2,3,4');



create table tabB(

id int,

vl varchar(20),

ct varchar(20)

);

insert into tabB VALUES(99,'1','折');

insert into tabB VALUES(100,'2','戟');

insert into tabB VALUES(101,'3','沉');

insert into tabB VALUES(102,'4','沙');

insert into tabB VALUES(103,'5','莫');

#测试数据结束





SELECT

	b.id,

	b.mtp,

	a.ct

FROM

	tabB a

JOIN (

	SELECT

		t.id,

		substring_index(

			substring_index(

				t.mtp,

				',',

				b.help_topic_id + 1

			),

			',',

			- 1

		) AS mtp

	FROM

		test_db.tabA t

	JOIN mysql.help_topic b ON b.help_topic_id < (

		LENGTH(t.mtp) - LENGTH(REPLACE(t.mtp, ',', '')) + 1

	)

) b ON a.vl = b.mtp

二月十六版主 2017-11-01

打赏
举报

CREATE FUNCTION dbo.F_Split

 (

     @SplitString nvarchar(max),  --源字符串

     @Separator nvarchar(10)=' '  --分隔符号，默认为空格

 )

 RETURNS @SplitStringsTable TABLE  --输出的数据表

 (

     [id] int identity(1,1),

     [value] nvarchar(max)

 )

 AS

 BEGIN

     DECLARE @CurrentIndex int;

     DECLARE @NextIndex int;

     DECLARE @ReturnText nvarchar(max);



     SELECT @CurrentIndex=1;

     WHILE(@CurrentIndex<=len(@SplitString))

         BEGIN

             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);

             IF(@NextIndex=0 OR @NextIndex IS NULL)

                 SELECT @NextIndex=len(@SplitString)+1;

                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);

                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);

                 SELECT @CurrentIndex=@NextIndex+1;

             END

     RETURN;

 END

GO

--测试数据

if not object_id(N'Tempdb..#a') is null

	drop table #a

Go

Create table #a([id] int,[mtp] nvarchar(27))

Insert #a

select 100,N'1,2,3,4'

GO

if not object_id(N'Tempdb..#b') is null

	drop table #b

Go

Create table #b([id] int,[vl] int,[ct] nvarchar(21))

Insert #b

select 99,1,N'折' union all

select 100,2,N'戟' union all

select 101,3,N'沉' union all

select 102,4,N'沙' union all

select 103,5,N'莫'

Go

--测试数据结束

SELECT  id ,

        STUFF(( SELECT  ',' + ct

                FROM    #b

                        JOIN ( SELECT   value

                               FROM     dbo.F_Split(mtp, ',') t

                             ) t1 ON vl = t1.value

              FOR

                XML PATH('')

              ), 1, 1, '') AS mtp

FROM    #a