Join 2 tables - Multiple FK to one PK

Question

我有一个表与标题数据和第二个表与测量数据。它们是这样的:

Table1                            Table2 
| headID   |   date   |           | headID   |   data1  |   data2  | 
|----------|----------|           |----------|----------|----------| 
| 1        | 08.10.21 |           | 1        |   'abc'  |  'xyz'   |
| 2        | 09.10.21 |           | 1        |   '123'  |  '999'   |
| 3        | 14.10.21 |           | 1        |   '456'  |  '888'   |
| 2        |   '222'  |  '000'   |
| 3        |   '111'  |  '333'   |

是否可以连接两个表并将表2中的每个条目放入一行中?

:

HeadID, date, data1, data2, data1, data2, data1, data2
1, 08.10.21, 'abc', 'xyz', '123', '999', '456', '888'

Answer 1

您可以使用select来获取每条记录的详细信息。

select t1.HeadID, t1.date, t2.data1, t2.data2 from Table1 t1,Table2 t2 where t1.HeadID=t2.HeadID;

返回表2的所有记录以及表1的详细信息。

或使用右连接

select t1.*,t2.date1,t2.date2 from Table_1 t1 right join table2 t2 on t1.HeadID=t2.HeadID;

Answer 2

-- POSSIBLE SOLUTION FOR MS SQLSEVER -- THIS IS A VERY LIMITED DATA SET...DOES YOU SAMPLE COVER ALL POSSIBILITIES  ??
-- PLEASE POST SET UP SCRIPT FOR DATA....YOU WILL GET BETTER AND MORE ACCURATE RESPONSE  
USE [tempdb]
GO
CREATE TABLE [dbo].[Table1] (
[headId] [int] NOT NULL,
[date] [date] NOT NULL
)
GO
CREATE TABLE [dbo].[Table2] (
[headId] [int] NOT NULL,
[data1] [varchar](50) NOT NULL,
[data2] [varchar](50) NOT NULL
)
GO
INSERT [dbo].[Table1] ([headId], [date]) VALUES(1, CAST(N'2021-10-08' AS date))
INSERT [dbo].[Table1] ([headId], [date]) VALUES(2, CAST(N'2021-10-09' AS date))
INSERT [dbo].[Table1] ([headId], [date]) VALUES(3, CAST(N'2021-10-14' AS date))
INSERT [dbo].[Table2] ([headId], [data1], [data2]) VALUES(1, N'abc', N'xyz')
INSERT [dbo].[Table2] ([headId], [data1], [data2]) VALUES(1, N'123', N'999')
INSERT [dbo].[Table2] ([headId], [data1], [data2]) VALUES(1, N'456', N'888')
INSERT [dbo].[Table2] ([headId], [data1], [data2]) VALUES(2, N'222', N'000')
INSERT [dbo].[Table2] ([headId], [data1], [data2]) VALUES(3, N'111', N'333');
--  MAYBE A SOLUTION (ms sql )
WITH c
AS (SELECT
headId,
STRING_AGG(data1 + ', ' + data2, ', ') AS datarow
FROM table2
GROUP BY headID)
SELECT
t.headId,
t.date,
c.datarow
FROM Table1 t
INNER JOIN c
ON t.headId = c.headId
DROP TABLE Table1
DROP TABLE Table2