联接三个表，并在单独的列中为每个DISTINCT行的多行聚合数据 [英] JOIN three tables and aggregate data from multiple rows for every DISTINCT row in separate column

问题描述

加入三个表，并在单独的列中为每个DISTINCT行的多行聚合数据

JOIN three tables and aggregate data from multiple rows for every DISTINCT row in separate column

我有一张表，其中一个项目与多个项目映射.

i have a table where one item is mapped with multiple items.

Key 1 | Key 2
1       2
1       5
1       6
1       4
1       8

我有另一个这样的桌子

Key 1 | ShortKey1Desc
1       'Desc short'

我还有一个表，其中有这样的数据

i have one more table where i have data like this

Key 1 | Description
1       'Desc a'
1       'Desc c'
1       'Desc aa'
1       'Desc tt'

我需要为我的视图编写一个sql查询，以便像这样生成表

i need to write a sql query for my view where table would be generated like this

Key 1 | AllKeys2ForKey1 | AllDescriptionsForKey1           | ShortKey1Desc
1     | 2;5;6;4;8       | Desc a; Desc c; Desc aa; Desc tt | Desc short

键1是一个字符串类型字段，因此我需要使用该字符串键将它们加入表中

Key 1 is a string type field so i need to join them table using that string key

我正在尝试的是创建用于舒适数据访问的视图.需要创建一个查询将不会花费很多时间.我已经尝试过使用Function来做到这一点，但是需要花一些时间才能加载.

what i'm trying is to create a view for comfortable data access. need to create a query what will not take ages. i already tried to do it with Functions but it takes ages for load.

在此方面的任何帮助将不胜感激.非常感谢

any help on this one would be highly appreciated. thanks a lot

推荐答案

假设您无法更改数据结构以进行更有效的查询，则可以使用:

Assuming that you are unable to change the data structures to make a more efficient query, this will work:

--Populate sample data
SELECT 1 as key1,       2  as key2 INTO #tbl1
UNION ALL SELECT 1,       5 
UNION ALL SELECT 1,       6 
UNION ALL SELECT 1,       4 
UNION ALL SELECT 1,       8 

SELECT 1  as key1,     'Desc short' as shortkeydesc INTO #tbl2

SELECT 1   as key1,    'Desc a'  as [description] INTO #tbl3
UNION ALL SELECT 1,       'Desc c' 
UNION ALL SELECT 1,       'Desc aa' 
UNION ALL SELECT 1,       'Desc tt' 

--Combine data into semi-colon separated lists
SELECT 
key1
,STUFF(
    (
    SELECT
      ';' + CAST(t2.key2 AS VARCHAR(10))
    FROM #tbl1 t2
    WHERE t2.key1 = tbl1.key1
    FOR XML PATH('')
    ), 1, 1, ''
  )
,STUFF(
    (
    SELECT
      ';' + tbl2.shortkeydesc
    FROM #tbl2 tbl2
    WHERE tbl2.key1 = tbl1.key1
    FOR XML PATH('')
    ), 1, 1, ''
  )
,STUFF(
    (
    SELECT
      ';' + tbl3.[description]
    FROM #tbl3 tbl3
    WHERE tbl3.key1 = tbl1.key1
    FOR XML PATH('')
    ), 1, 1, ''
  )
FROM #tbl1 tbl1
GROUP BY tbl1.key1

这篇关于联接三个表，并在单独的列中为每个DISTINCT行的多行聚合数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！