代码之家 › 专栏 › 技术社区 › Md Kamran Azam

创建过程以生成连接不同表的表,其中表可以有多行重复?

procedure join sql-server sql

Md Kamran Azam · 技术社区 · 6 年前

我有三张桌子 T1 ,则, T2 和 T3 每张桌子都是 bpm_no 在所有表格中都很常见。桌子 T1级 是主表。 T1级 使每一行都具有唯一性 bpm\u编号 (不重复)。桌子 T2级 包括两列,第一列是 bpm\u编号 另一个是 user ,这里有一个 bpm\u编号 不同的用户在同一个服务器上工作时可能会发生多次 bpm\u编号 桌子 T3 包括两列,第一列是 bpm\u编号 另一个是 total_outstanding ,这里还有一个 bpm\u编号 可能多次发生在不同的 总计未付金额 ,作为一个 bpm\u编号 不同的银行可以有不同的未偿款。

现在,我必须编写一个过程,该过程将使用上述所有表(内部联接)构造一个表,它必须包括三列,其中一列将包括 bpm\u编号 (每行都是唯一的),另一行中的每个用户都用逗号分隔 bpm\u编号 ,最后一列的和为 总计未付金额 .想法是让结束表 bpm\u编号 其结果值以逗号分隔,loan为sum。

一、尝试使用视图,但它不会工作,因为在这种情况下,我将不得不创建许多视图。请建议其他方式。

以下是便于理解的表格结构:

Table T1:
|---------------------|------------------|
|      **bpm_no**     |     **name**     |
|---------------------|------------------|
|      abc_0011       |      john        |
|---------------------|------------------|

Table T2:
|---------------------|------------------|
|      **bpm_no**     |     **user**     |
|---------------------|------------------|
|      abc_0011       |      abc         |
|---------------------|------------------|
|      abc_0011       |      bcd         |
|---------------------|------------------|
|      abc_0011       |      lmn         |
|---------------------|------------------|

Table T3:
|---------------------|------------------|
|      **bpm_no**     |     **loan_os**  |
|---------------------|------------------|
|      abc_0011       |      14,500      |
|---------------------|------------------|
|      abc_0011       |      4000        |
|---------------------|------------------|
|      abc_0011       |      5000        |
|---------------------|------------------|

Final Table required:
|---------------------|------------------|------------------|
|      **bpm_no**     |     **user**     |     **loan_os**  |
|---------------------|------------------|------------------|
|     abc_0011        |   abc,bcd,lmn    |     23,500       |
|---------------------|------------------|------------------|

2 回复 | 直到 6 年前

sticky bit 6 年前

如果您已经使用SQL Server 2017,则可以使用 string_agg() 要获取用户列表,请执行以下操作:

SELECT [T3].[bpm_no],
       [T2].[user],
       [T3].[loan_os]
       FROM (SELECT [T3].[bpm_no],
                    sum([T3].[loan_os]) [loan_os]
                    FROM [T3]
                    GROUP BY [T3].[bpm_no]) T3
            LEFT JOIN (SELECT [T2].[bpm_no],
                              string_agg([T2].[user], ',') [user]
                              FROM [T2]
                              GROUP BY [T2].[bpm_no]) [T2]
                      ON [T2].[bpm_no] = [T3].[bpm_no];

DxTx 6 年前

试试这个。。。

表脚本和示例数据

CREATE TABLE [T1](
    [bpm_no] [nvarchar](50) NULL,
    [name] [nvarchar](50) NULL
) 


CREATE TABLE [T2](
    [bpm_no] [nvarchar](50) NULL,
    [user] [nvarchar](50) NULL
) 


CREATE TABLE [T3](
    [bpm_no] [nvarchar](50) NULL,
    [loan_os] [decimal](18, 0) NULL
) 

INSERT [T1] ([bpm_no], [name]) VALUES (N'abc_0011', N'john')

INSERT [T2] ([bpm_no], [user]) VALUES (N'abc_0011', N'abc')
INSERT [T2] ([bpm_no], [user]) VALUES (N'abc_0011', N'bcd')
INSERT [T2] ([bpm_no], [user]) VALUES (N'abc_0011', N'lmn')

INSERT [T3] ([bpm_no], [loan_os]) VALUES (N'abc_0011', CAST(14500 AS Decimal(18, 0)))
INSERT [T3] ([bpm_no], [loan_os]) VALUES (N'abc_0011', CAST(4000 AS Decimal(18, 0)))
INSERT [T3] ([bpm_no], [loan_os]) VALUES (N'abc_0011', CAST(5000 AS Decimal(18, 0)))

查询

SELECT t1.bpm_no, 
       sq1.[user], 
       sq2.loan_os 
FROM   t1 
       INNER JOIN (SELECT bpm_no, 
                          Stuff((SELECT ', ' + [user] 
                                 FROM   t2 t21 
                                 WHERE  t21.bpm_no = t22.bpm_no 
                                 FOR xml path('')), 1, 2, '') AS [user] 
                   FROM   t2 t22 
                   GROUP  BY bpm_no) sq1 
               ON t1.bpm_no = sq1.bpm_no 
       INNER JOIN (SELECT bpm_no, 
                          Sum(loan_os) AS loan_os 
                   FROM   t3 
                   GROUP  BY bpm_no) sq2 
               ON t1.bpm_no = sq2.bpm_no

注意:如果要获取所有 bpm_no 在里面 T1 主表中,使用左联接而不是内部联接。

查询(没有T1主表的相同预期结果)

SELECT sq1.bpm_no, 
       sq1.[user], 
       sq2.loan_os 
FROM   (SELECT bpm_no, 
               Stuff((SELECT ', ' + [user] 
                      FROM   t2 t21 
                      WHERE  t21.bpm_no = t22.bpm_no 
                      FOR xml path('')), 1, 2, '') AS [user] 
        FROM   t2 t22 
        GROUP  BY bpm_no) sq1 
       INNER JOIN (SELECT bpm_no, Sum(loan_os) AS loan_os 
                   FROM   t3 
                   GROUP  BY bpm_no) sq2 
               ON sq1.bpm_no = sq2.bpm_no

输出

+----------+---------------+---------+
|  bpm_no  |     user      | loan_os |
+----------+---------------+---------+
| abc_0011 | abc, bcd, lmn |   23500 |
+----------+---------------+---------+

演示: http://www.sqlfiddle.com/#!18/b6362/2/0

如果我被误解了,请告诉我。