如何将两个表连接到一个新表中以在c#中显示

本文关键字：新表中 一个 显示 连接 两个 | 更新日期: 2023-09-27 18:07:28

我有两张表(航班详细信息(，(航班日期(

航班

id  |    callsign   |    type
1   |     BAW240    |    A370
2   |     AAL767    |    B777
3   |     JTE201    |    A320

日期

ID  |       date
1   |     10/10/2016
1   |     10/12/2016
1   |     10/14/2016
1   |     10/15/2016
2   |     11/25/2016
2   |     11/29/2016
3   |     10/20/2016
3   |     10/21/2016
3   |     10/22/2016

我想加入像这样的两张桌子

id  |    callsign   |    type   | dates
1   |     BAW240    |    A370   | 10/10/2016 , 10/12/2016 , 10/14/2016 , 10/15/2016
2   |     AAL767    |    B777   | 11/25/2016 , 11/29/2016 
3   |     JTE201    |    A320   | 10/20/2016 , 10/21/2016 , 10/22/2016

最后一张表只是用于预览，并允许用户查看日期摘要
我怎样才能做到这一点？

如何将两个表连接到一个新表中以在c#中显示

Select A.*
      ,Dates=B.Dates
 From  Flights A
 Cross Apply (Select Dates=Stuff((Select Distinct ',' + cast(Date as varchar(25))
               From  Dates 
               Where ID=A.ID 
                For XML Path ('')),1,1,'') 
               ) B

返回

id  callsign    type    Dates
1   BAW240      A370    2016-10-10,2016-10-12,2016-10-14,2016-10-15
2   AAL767      B777    2016-11-25,2016-11-29
3   JTE201      A320    2016-10-20,2016-10-21,2016-10-22

我多次使用这种技术来列出与收据相关的多个发票。

USE AdventureWorks2008R2
SELECT CAT.id, CAT.callsign, CAT.type,
            STUFF((SELECT ',' + SUB.dates AS [text()]
                        — Add a comma (,) before each value
                        FROM Dates SUB
                        WHERE
                        SUB.flightid= CAT.id
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS dates
FROM  Flights CAT

来源：https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

相关文章：