SQL根据图中包含的部分相同内容查询对应数据

[Carrie_spinfo]

如何实现上述图中结果！

[学海无涯-回头是岸]

用sss 表left join aaa表就可以了 SELECT SSS.MATCHID,SELECT '是' AS AAA.COUNT FROM SSS LEFT JOIN AAA ON AAA.MATCHID=SSS.MATCHID

[道素]

如果serial 长度固定就不会存在部分匹配，可以用前后加逗号

;with AAA(matchId,serialNo,Amount)AS(
    select 705,'4097513',1 union all
    select 4157,'5048222',1 union all
    select 403,'4356888',1 
),SSS(MatchId,openSerialsNums)AS(
    select 705,'4097513,4680864,6030953' union all
    select 785,'4097513,4680864,6030953' union all
    select 531,'4097513,4680864,6030953' union all
    select 385,'4097513,4680864,6030953' union all
    select 917,'5048222,5210240,5380259' union all
    select 785,'5048222,5210240,5380259' union all
    select 4157,'5048222,5210240,5380259' union all
    select 385,'5048222,5210240,5380259'
)
select case when AAA.matchId is not NULL then N'是' else '' end as Amout, sss.* from sss
left join AAA on SSS.MatchId=AAA.MatchId and charindex(','+AAA.serialNo+',',','+SSS.openSerialsNums+',')>0

  	Amout	MatchId	openSerialsNums
1	是	705	4097513,4680864,6030953
2		785	4097513,4680864,6030953
3		531	4097513,4680864,6030953
4		385	4097513,4680864,6030953
5		917	5048222,5210240,5380259
6		785	5048222,5210240,5380259
7	是	4157	5048222,5210240,5380259
8		385	5048222,5210240,5380259

[二月十六]

SELECT  CASE WHEN SSS.MatchId = AAA.matchId THEN '是'
             ELSE ''
        END ,
        *
FROM    SSS
        LEFT JOIN AAA ON CHARINDEX(',' + AAA.serialNo + ',',
                                   ',' + SSS.openSerialsNums + ',') > 0;

[Carrie_spinfo]

引用 2 楼 ch21st 的回复:

如果serial 长度固定就不会存在部分匹配，可以用前后加逗号

;with AAA(matchId,serialNo,Amount)AS(
    select 705,'4097513',1 union all
    select 4157,'5048222',1 union all
    select 403,'4356888',1 
),SSS(MatchId,openSerialsNums)AS(
    select 705,'4097513,4680864,6030953' union all
    select 785,'4097513,4680864,6030953' union all
    select 531,'4097513,4680864,6030953' union all
    select 385,'4097513,4680864,6030953' union all
    select 917,'5048222,5210240,5380259' union all
    select 785,'5048222,5210240,5380259' union all
    select 4157,'5048222,5210240,5380259' union all
    select 385,'5048222,5210240,5380259'
)
select case when AAA.matchId is not NULL then N'是' else '' end as Amout, sss.* from sss
left join AAA on SSS.MatchId=AAA.MatchId and charindex(','+AAA.serialNo+',',','+SSS.openSerialsNums+',')>0

  	Amout	MatchId	openSerialsNums
1	是	705	4097513,4680864,6030953
2		785	4097513,4680864,6030953
3		531	4097513,4680864,6030953
4		385	4097513,4680864,6030953
5		917	5048222,5210240,5380259
6		785	5048222,5210240,5380259
7	是	4157	5048222,5210240,5380259
8		385	5048222,5210240,5380259

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''是'  else ''  end as  Amount,   sss.* ' at line 3 报错了，不知道是什么原因

[Carrie_spinfo]

引用 3 楼 sinat_28984567 的回复:

SELECT  CASE WHEN SSS.MatchId = AAA.matchId THEN '是'
             ELSE ''
        END ,
        *
FROM    SSS
        LEFT JOIN AAA ON CHARINDEX(',' + AAA.serialNo + ',',
                                   ',' + SSS.openSerialsNums + ',') > 0;

报错 [Err] 1305 - FUNCTION platform_data.charindex does not exist 数据库版本 mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper

[二月十六]

SELECT  (CASE WHEN SSS.MatchId = AAA.matchId THEN '是'
             ELSE ''
        END )as Amount,
        SSS.*
FROM    SSS
        LEFT JOIN AAA ON instr(CONCAT(CONCAT(',' ,SSS.openSeriesNums) , ','),CONCAT(CONCAT(',', AAA.serilaNo),',')) > 0;

[二月十六]

mysql啊……下次提问的时候记得带上数据库，这里是mssql数据库版，大家肯定是按照mssql的语法写

SELECT  CASE WHEN SSS.MatchId = AAA.matchId THEN '是'
             ELSE ''
        END ,
        *
FROM    SSS
        LEFT JOIN AAA ON instr(CONCAT(,CONCAT(',' ,SSS.openSerialsNums) , ','),CONCAT(CONCAT(',', AAA.serialNo),',')) > 0;

[Carrie_spinfo]

引用 6 楼 sinat_28984567 的回复:

mysql啊……下次提问的时候记得带上数据库，这里是mssql数据库版，大家肯定是按照mssql的语法写

SELECT  CASE WHEN SSS.MatchId = AAA.matchId THEN '是'
             ELSE ''
        END ,
        *
FROM    SSS
        LEFT JOIN AAA ON instr(CONCAT(,CONCAT(',' ,SSS.openSerialsNums) , ','),CONCAT(CONCAT(',', AAA.serialNo),',')) > 0;

报错 [Err] 1054 - Unknown column 'SSS.openSerialsNums' in 'on clause'

[Carrie_spinfo]

没有报错了，但是查询结果Amount空的

[Carrie_spinfo]

表AAA matchId和serilaNo和 表SSS 的 openSeriesNums以及matchid能对应上且在前者查询对应结果没错的同时表AAA的字段Amount等于1才会等于“是”

[道素]

其实问题楼上已经解答了，如果是MYSQL换成对应的函数就行

drop table if exists AAA;
drop table if exists SSS;
create table AAA(matchId int,serialNo varchar(100),Amount int);
insert into AAA
    select 705,'4097513',1 union all
    select 4157,'5048222',1 union all
    select 403,'4356888',1;
create table SSS(MatchId int,openSerialsNums varchar(1000));
insert into SSS
    select 705,'4097513,4680864,6030953' union all
    select 785,'4097513,4680864,6030953' union all
    select 531,'4097513,4680864,6030953' union all
    select 385,'4097513,4680864,6030953' union all
    select 917,'5048222,5210240,5380259' union all
    select 785,'5048222,5210240,5380259' union all
    select 4157,'5048222,5210240,5380259' union all
    select 385,'5048222,5210240,5380259';

select case when AAA.matchId is not NULL then N'是' else '' end as Amout, sss.* from sss
left join AAA on SSS.MatchId=AAA.MatchId and INSTR(','+AAA.serialNo+',',','+SSS.openSerialsNums+',')>0;