联接两个没有任何匹配列的表

本文介绍了联接两个没有任何匹配列的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个如下表. 表1:Matrix表 表2:Transaction表

Hi, I have two tables like as follows. Table 1 : Matrix Table Table 2 : Transaction Table

-- Matrix Table CREATE TABLE [matrixtable] ( [prkey] [int] IDENTITY (1, 1) NOT NULL , [polid] [int] NULL , [clmtypeid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [insid] [int] NULL , CONSTRAINT [PK_matrixtable] PRIMARY KEY CLUSTERED ( [prkey] ) ON [PRIMARY] ) ON [PRIMARY] GO -- Transaction table CREATE TABLE [ClaimTable] ( [claimid] [int] NOT NULL , [polid] [int] NOT NULL , [insid] [int] NOT NULL , [clmtypeid] [int] NULL ) ON [PRIMARY] GO --Values for Matrx Table INSERT INTO [ClaimTable](claimid,polid,insid,clmtypeid) SELECT 101,1000,1,1 UNION ALL SELECT 102,1000,1,2 UNION ALL SELECT 103,1000,1,6 -- Value for Transaction Table INSERT INTO matrixtable (polid,clmtypeid,insid) SELECT 1000,'1,2,6',1 UNION ALL SELECT 1001,'3',1

我需要基于"clmtypeid"加入这两个表. 提前谢谢. 帮助我解决此问题.

I need to join this two table based on "clmtypeid". Advance Thanks. Help me to solve this problem.

推荐答案

在这里是: Here it is : select * from ( select prkey, claimid from ( SELECT prkey, CAST('<r>' + REPLACE(clmtypeid, ',', '</r><r>') + '</r>' AS XML) claimXml FROM matrixtable ) newmat CROSS APPLY ( SELECT CId.value('.', 'int') ClaimId FROM newmat.claimXml.nodes('r') AS ClaimCodes(CId) ) Splited ) s inner join claimtable c on s.ClaimId = c.clmtypeid

希望对您有所帮助.

Hope it helps.

您好， 终于找到了解决方法， Hi, At last I find the Solution, -- Actual Query SELECT C.claimid,C.polid,C.insid,C.clmtypeid FROM ClaimTable C INNER JOIN matrixtable M ON M.polid=C.polid AND C.clmtypeid IN (SELECT Value FROM dbo.fnSplitString (M.clmtypeid,',')) -- Split Function CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max)) RETURNS TABLE AS RETURN WITH a AS( SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2 UNION ALL SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1) FROM a WHERE idx2>0 ) SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value FROM a

要添加到先前的答案，请考虑在两个表之间添加外键:外键约束 [ ^ ] To add to previous answers, consider adding a foreign key between the two tables: FOREIGN KEY Constraints[^]