SQL Server 需要帮助尝试合并2个sql表并连接到每行匹配数量可变的第三个表

jhdbpxl9 于 2023-01-08 发布在其他

关注(0)|答案(2)|浏览(110)

我有3个SQL Server表

INC-事故单
INT-交互票证
知识库-知识文章视图

所有3个表都有用户ID、票证编号和时间戳列。我正在尝试开发报告，以确定KB中何时存在一行或多行与INC或INT中的一行具有相同的用户ID和日期。理想情况下，我的输出将是INC和INT的并集，其中有一个新列，该列将在逗号分隔的字段中列出每个匹配行的KB票证编号。例如，给定每个表中的以下行：
| INC票据编号|INC用户ID| INC日期|
| - ------| - ------| - ------|
| INC1234| ID123|十二月二十二日|
| INC2345| ID123|十二月二十二日|
| KB票证编号|KB用户ID| KB日期|
| - ------| - ------| - ------|
| KB1234| ID123|十二月二十二日|
| KB2345| ID123|十二月二十二日|
我将得到以下输出：
| INC票据编号|INC用户ID| INC日期|KB门票|
| - ------| - ------| - ------| - ------|
| INC1234| ID123|十二月二十二日|KB1234、KB2345|
| INC2345| ID123|十二月二十二日|KB1234、KB2345|
输出的最终目的地将是PowerBI。我最初试图在Power Query中解决这个问题，但当我创建了一个成功生成我所需输出的公式时，它是令人难以置信的时间和资源密集型，因为每个表将有1，000，000行或更多行，它花费了48个小时，从未完成。我正在尝试在SQL查询中处理比较，但我对SQL还是个新手，似乎搞不懂它。
我得到了下面的查询，它将成功地组合3个表，但只输出一个匹配的每行：

select 
    inc.TicketNumber, inc.OpenTime, inc.Contact,
    kb.KBTicketNumber, kb.UpdateTime, kb.ViewedMMID
from 
    MMITMetrics.dbo.INC_IncidentTickets inc
full join  
    MMITMetrics.dbo.KB_Use kb on inc.Contact = kb.ViewedMMID 
                              and cast(inc.OpenTime as date) = cast(kb.UpdateTime as date)
where 
    inc.OpenTime > '2021-01-01 12:00:00.000' 
    or kb.UpdateTime > '2021-01-01 12:00:00.000'

union 

select 
    int.TicketNumber, int.OpenTime,int.Contact,
    kb.KBTicketNumber, kb.UpdateTime, kb.ViewedMMID
from 
    MMITMetrics.dbo.INT_InteractionTickets int 
full join  
    MMITMetrics.dbo.KB_Use kb on int.Contact = kb.ViewedMMID 
                              and cast(int.OpenTime as date) = cast(kb.UpdateTime as date)
where 
    int.OpenTime > '2021-01-01 12:00:00.000' 
    or kb.UpdateTime > '2021-01-01 12:00:00.000'

我正在使用Microsoft SQL Server Management Studio 18，因此我认为我需要使用string_agg函数，但无法使其正常工作。

sql-server

来源：https://stackoverflow.com/questions/75008312/need-help-trying-to-combine-2-sql-tables-and-join-to-a-3rd-with-a-variable-numbe

2条答案

按热度按时间

rjee0c151#

您是对的，您需要string_agg。您可以执行以下操作：

select 
inc.TicketNumber, inc.OpenTime, inc.Contact,
kb.UpdateTime, STRING_AGG(kb.KBTicketNumber,',') AS KBTicketNumber

from inc
full join  kb on inc.Contact = kb.ViewedMMID and CAST(inc.OpenTime as date) = CAST(kb.UpdateTime as date)
where inc.OpenTime > '2021-01-01 12:00:00.000' or kb.UpdateTime > '2021-01-01 12:00:00.000'

GROUP BY inc.TicketNumber, inc.OpenTime, inc.Contact, kb.UpdateTime

如果您使用的是较旧版本的SQL Server，那么这个方法就不起作用了，因为您不支持该函数。这可能不是最好的方法，但在过去，我曾经做过类似这样的事情来达到预期的效果：

SELECT n.TicketNumber, n.OpenTime, n.Contact,
n.UpdateTime, 
MAX( CASE WHEN n.rnk = 1 THEN [KBTicketNumber] ELSE '' END ) + 
MAX( CASE WHEN n.rnk = 2 THEN ',' + [KBTicketNumber] ELSE '' END ) + 
MAX( CASE WHEN n.rnk = 3 THEN ',' + [KBTicketNumber] ELSE '' END ) + 
MAX( CASE WHEN n.rnk = 4 THEN ',' + [KBTicketNumber] ELSE '' END ) + 
MAX( CASE WHEN n.rnk = 5 THEN ',' + [KBTicketNumber] ELSE '' END ) AS [KBTicketNumber]
FROM (
select 
inc.TicketNumber, inc.OpenTime, inc.Contact,
kb.UpdateTime, kb.KBTicketNumber,
ROW_NUMBER() OVER (PARTITION BY inc.TicketNumber, inc.OpenTime, inc.Contact,
kb.UpdateTime ORDER BY kb.UpdateTime) AS rnk
from inc
full join  kb on inc.Contact = kb.ViewedMMID and CAST(inc.OpenTime as date) = CAST(kb.UpdateTime as date)
where inc.OpenTime > '2021-01-01 12:00:00.000' or kb.UpdateTime > '2021-01-01 12:00:00.000'
) AS n

GROUP BY n.TicketNumber, n.OpenTime, n.Contact,
n.UpdateTime

赞(0）回复(0）举报 2023-01-08

8wigbo562#

WITH INC_DATA(INC_TICKET_NUMBER,INC_USER_ID,INC_DATE)AS
(
  SELECT 'INC1234','id123','2022-12-22' UNION ALL
  SELECT 'INC2345','id123','2022-12-22'
),
KB_DATA(KB_TICKET_NUMBER,KB_USER_ID,KB_DATE)AS
(
   SELECT 'KB1234','id123','2022-12-22'UNION ALL
   SELECT 'KB2345','id123','2022-12-22'
)
SELECT I.INC_TICKET_NUMBER,I.INC_USER_ID,I.INC_DATE,STRING_AGG(K.KB_TICKET_NUMBER,',')KB
FROM INC_DATA AS I
JOIN KB_DATA AS K ON I.INC_USER_ID=K.KB_USER_ID AND I.INC_DATE=K.KB_DATE
GROUP BY I.INC_TICKET_NUMBER,I.INC_USER_ID,I.INC_DATE

微软SQL服务器2017（RTM-CU 31）（KB 5016884）- 14.0.3456.2（X64）2022年9月2日11：01：50
根据您的示例数据和描述，您可以尝试类似上面的操作（SQL Server 2017+）

赞(0）回复(0）举报 2023-01-08

我来回答

SQL Server 需要帮助尝试合并2个sql表并连接到每行匹配数量可变的第三个表

2条答案

相关问题

热门标签

最新问答