复杂联接中的问题

Question

我有四张桌子

tbLicenceTypesX (2个字段)

• LicenceTypes
• LicenceTypesX

tbLicenceTypesX (包含类似的数据)

• 1-医疗牌照
• 2-财产
• 3-伤亡
• 4-培训执照

tbProduct (三个飞碟)

• 产品
• ProductX
• CompanyId (F.K)
• LicenceTypes(F.K)

tbProduct (包含类似的数据)

• 1- T.V - 10 -2
• 2- A.C - 30 -3
• 3-移动电话- 40 -4

tbLicence (三个飞碟)

• 牌照
• LicenceTypesNames
• AgentId

tbLicence (包含类似的数据)

• 1-财产、伤亡- 23
• 2-训练牌照，意外事故- 34

现在我不得不从tbProduct那里取产品和tbProduct，它的LicenceTypes与公司tbLicence的代理许可证相匹配。

例如:我必须获取牌照类型为2(“属性”)和公司Id为10的T.V，它们应该分配给代理，其中代理Id为23，其LicenceTypesNames也应包含“属性”

我想拿些类似的东西

        @CompanyId int, 
        @AgentId int
        As
        SELECT p.ProductX,p.Product
        from tbProduct p
        inner join tbLicence l on p.LicenceTypes = l.LicenceTypesNames<its corresponding Id>
        inner  join tbProduct c on c.Product =p.Product 
        where
        c.CompanyId=@CompanyId
        and l.AgentId=@AgentId

请帮帮我！

Answer 1

您可以使用XML和CROSS APPLY将comma分隔的值和JOIN与tbProduct分开。如果LTRIM和RTRIM函数有过多的空空间，则用于修剪逗号分隔的值。下面的代码为您提供了所需的输出。

DECLARE @CompanyId int = 30, @AgentId int = 23

 ;WITH CTE AS
 ( 
    SELECT AgentId, TCT.LicenceTypes FROM
    (
        SELECT AgentId, LTRIM(RTRIM(Split.XMLData.value('.', 'VARCHAR(100)'))) LicenceTypesNames FROM
        (
            SELECT AgentID, Cast ('<M>' + REPLACE(LicenceTypesNames, ',', '</M><M>') + '</M>' AS XML) AS Data
            FROM   tbLicence 
        ) AS XMLData
            CROSS APPLY Data.nodes ('/M') AS Split(XMLData) 
    )
    AS LTN 
    JOIN tbLicenceTypesX TCT  ON LTN.LicenceTypesNames = tct.LicenceTypesX
)

SELECT  p.ProductX,p.Product 
FROM tbProduct P
JOIN CTE c on p.LicenceTypes = c.LicenceTypes
WHERE CompanyId = @CompanyId
AND AgentId = @AgentId

Sql Fiddle演示