如何从sql查询中的两个表中获取多列的唯一数据

I have two tables from which I have to print unique data group by multiple column.

我有两个表,我必须通过多列打印唯一的数据组。

Table 1:BuildingSubtypeDetails                  
    File Number Building Name   Sub_type_ID         
    6           Building1       15M         
    6           Building2       45M         
    6           Building3       60M         
    7           Building4       15M         
    7           Building5       60M         

Table 2: RuleDetails                    
RID Sub_type_ID Protection  Provision   Remark  Requirement
1   15M         PR1         PO1         R1      Required
2   15M         PR2         PO2         R2      Not-required
3   15M         PR3         PO3         R3      Not-required
4   45M         PR1         PO1         R1      Not-required
5   45M         PR2         PO2         R2      Not-required
6   45M         PR3         PO3         R3      Not-required
7   60M         PR1         PO1         R1      Required
8   60M         PR2         PO2         R2      Required
9   60M         PR3         PO3         R3      Not-required

Output for file 6 should be

文件6的输出应该是

File type   Protection  Provision   Remark  Requirement 
6            PR1         PO1         R1     Building 1, Building 3 Required and Building2 Not-required  
6            PR2         PO2         R2     Building 1, Building2 Not-Required and  Building3 required  
6            PR3         PO3         R3      Not-required   

2 个解决方案

#1

Try with:

SELECT b.FILE AS 'File type'
    ,r.Protection AS Protection
    ,b.r.Provision AS Provision
    ,r.Remark AS Remark
    ,r.Requirement AS Requirement
FROM BuildingSubtypeDetails AS b
LEFT JOIN RuleDetails AS r
    ON b.Sub_type_ID = r.Sub_type_ID
WHERE b.FILE = 6

#2

I think you will need to concat row data to acheive this , used XML_path. Check this query and need to append And instead of , in the end

我认为你需要连接行数据来实现这一点,使用XML_path。检查此查询,最后需要追加And而不是

WITH STY
AS (
    SELECT DISTINCT t1.[File Number] FileType
        ,t2.Protection
        ,t2.Provision
        ,t2.Remark
        ,[Building name]
        ,ROW_NUMBER() OVER (
            PARTITION BY [File Number]
            ,Protection
            ,Remark ORDER BY [Building name]
            ) ROW
        ,REQUIREMENT
        ,CONCAT (
            [Building name]
            ,'  '
            ,requirement
            ) AS RequirementC
    FROM RuleDetails t2
    INNER JOIN BuildingSubtypeDetails t1
        ON t1.Sub_type_id = t2.Sub_type_ID
    )
    ,STY1
AS (
    SELECT DISTINCT FileType
        ,Protection
        ,Provision
        ,Remark
        ,CASE WHEN ROW = 1 THEN [Building name] ELSE RequirementC END Requirement
        ,row
    FROM STY
    )
SELECT FileType
    ,PROTECTION
    ,Provision
    ,Remark
    ,Requirement = (
        STUFF((
                SELECT ', ' + Requirement
                FROM STY1 A
                WHERE A.FileType = B.FileType
                    AND a.Protection = b.Protection
                    AND a.Provision = b.Provision
                    AND a.Remark = b.Remark
                FOR XML PATH('')
                ), 1, 1, '')
        )
FROM STY1 b
GROUP BY FileType
    ,Protection
    ,Provision
    ,Remark