SQL Server：从同一结果中的2个其他表中进行选择

I have a problem with a little complicated query. I have to select data from 2 tables which looks like this:

我有一个复杂的查询问题。我必须从2个表中选择数据,如下所示:

Table1:

 ID     Prob  Cost  Visible    
-------------------------
12345    100   50    1
12346    90    333   1

Table2:

 ID     Item1    Count
-----------------------
12345    555       3
12345    666       5
12345    777       20
12345    888       2
12346    111       5
12346    222       6
12346    333       2

I need to make output like this:

我需要像这样输出:

 ID    Prob  Cost Itm1 Cnt1 Itm2 Cnt2 Itm3 Cnt3  Itm4 Cnt4  Visible
--------------------------------------------------------------------
12345   100   50   555  3    666  5    777  20    888  2      1
12346   90   333   111  2    222  5    333  2     0    0      1

So all needed I think is some simple loop with count of items from table 2 and add columns with count and item number to result. I've tried a lot of solutions but cannot find a suitable one.

因此,我认为所有需要的是一个简单的循环,其中包含表2中的项目数,并添加具有计数和项目编号的列到结果。我尝试了很多解决方案,但找不到合适的解决方案。

Heres correct data:

这是正确的数据:

well so thats this query in my case:

那么这就是我的情况下的查询:

DECLARE 
@sql1   VARCHAR(4000) = '',
@sql2   VARCHAR(4000) = '',
@sql3   VARCHAR(4000) = ''

SELECT @sql1 =
'SELECT
t1.UniqueID
,t1.TargetItemNum
,t1.MixingProbability
,t1.MixingCost' + CHAR(10)

SELECT @sql2 = @sql2 +
'   ,ISNULL(MAX(CASE WHEN RN = '+ CONVERT(VARCHAR(10), RN) +' THEN SourceItemNum  END), 0) AS [SourceItemNum' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10) +
'   ,ISNULL(MAX(CASE WHEN RN = '+ CONVERT(VARCHAR(10), RN) +' THEN SourceItemCount  END), 0) AS [SourceItemCount' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10)
FROM (
SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY UniqueID ORDER BY TargetItemNum) AS RN
FROM ti_ItemMixingInfo
)t

SELECT @sql3 =
'   ,Visible
FROM ti_ItemMixingInfo t1
LEFT JOIN(
SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY UniqueID ORDER BY SourceItemNum)
FROM ti_ItemMixingElement
)t2
ON t2.UniqueID = t1.UniqueID
GROUP BY t1.UniqueID,t1.TargetItemNum, t1.MixingCost, t1.MixingProbability, t1.Visible
'

PRINT @sql1 + @sql2 + @sql3
EXEC(@sql1 + @sql2 + @sql3)

and thats the result:

这就是结果:

1   7000630 10000   5230    7000780 3   1
2   7000640 10000   5230    7000780 3   1
3   7000650 10000   5038    7000780 3   1
4   7000660 10000   5038    7000780 3   1

first table[ti_ItemMixingInfo]:

UniqueID TargetItemNum MixingProbability MixingCost Visible
1      7000630          10000          5230        1
2      7000640          10000          5230        1
3      7000650          10000          5038        1
4      7000660          10000          5038        1

second table[ti_ItemMixingElement]:

UniqueID SourceItemNum SourceItemCount
1      7000780         3
1      7000800         5
1      7001350         10
1      7008410         1
1      7008420         1
2      7000780         3
2      7000800         5
2      7001420         10
2      7008410         1
2      7008430         1
3      7000780         3
3      7000800         5
3      7001360         10
3      7008380         1
3      7008390         2
4      7000780         3
4      7000800         5
4      7001380         10
4      7008380         1
4      7008410         2

Any ideas?

1 个解决方案

#1

You could do this using a dynamic crosstab:

您可以使用动态交叉表执行此操作:

DECLARE 
    @sql1   VARCHAR(4000) = '',
    @sql2   VARCHAR(4000) = '',
    @sql3   VARCHAR(4000) = ''

SELECT @sql1 =
'SELECT
    t1.ID
    ,t1.Prob
    ,t1.Cost' + CHAR(10)

SELECT @sql2 = @sql2 +
'   ,ISNULL(MAX(CASE WHEN RN = '+ CONVERT(VARCHAR(10), RN) +' THEN Item1  END), 0) AS [Itm' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10) +
'   ,ISNULL(MAX(CASE WHEN RN = '+ CONVERT(VARCHAR(10), RN) +' THEN Count  END), 0) AS [Cnt' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10)
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Item1) AS RN
    FROM Table2
)t

SELECT @sql3 =
'   ,Visible
FROM Table1 t1
LEFT JOIN(
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Item1)
    FROM Table2
)t2
    ON t2.ID = t1.ID
GROUP BY t1.ID, t1.Cost, t1.Prob, t1.Visible
'

PRINT @sql1 + @sql2 + @sql3
EXEC(@sql1 + @sql2 + @sql3)

RESULT

ID          Prob        Cost        Itm1        Cnt1        Itm2        Cnt2        Itm3        Cnt3        Itm4        Cnt4        Visible
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -------
12345       100         50          555         3           666         5           777         20          888         2           1
12346       90          333         111         5           222         6           333         2           0           0           1

See SQL Fiddle

请参阅SQL小提琴

EDIT: From you update, your @sql2 is wrong:

编辑:从你更新,你的@ sql2错了:

DECLARE 
    @sql1   VARCHAR(4000) = '',
    @sql2   VARCHAR(4000) = '',
    @sql3   VARCHAR(4000) = ''

SELECT @sql1 =
'SELECT
    t1.UniqueID
    ,t1.TargetItemNum
    ,t1.MixingProbability
    ,t1.MixingCost' + CHAR(10)

SELECT @sql2 = @sql2 +
'   ,ISNULL(MAX(CASE WHEN RN = '+ CONVERT(VARCHAR(10), RN) +' THEN SourceItemNum END), 0) AS [Itm' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10) +
'   ,ISNULL(MAX(CASE WHEN RN = '+ CONVERT(VARCHAR(10), RN) +' THEN SourceItemCount  END), 0) AS [Cnt' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10)
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY UniqueID ORDER BY SourceItemNum) AS RN
    FROM ti_ItemMixingElement
)t

SELECT @sql3 =
'   ,Visible
FROM ti_ItemMixingInfo t1
LEFT JOIN(
    SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY UniqueID ORDER BY SourceItemNum )
    FROM ti_ItemMixingElement
)t2
    ON t2.UniqueID = t1.UniqueID
GROUP BY t1.UniqueID,t1.TargetItemNum, t1.MixingCost, t1.MixingProbability, t1.Visible
'

PRINT @sql1 + @sql2 + @sql3
EXEC(@sql1 + @sql2 + @sql3)

See SQL FIDDLE

请参见SQL FIDDLE

#1