SQL：连接与给定键匹配的所有字段？

发布于 2024-11-03 13:01:07 字数 922 浏览 0 评论 0原文

假设我有一个像这样的 SQL 查询：

    SELECT
    tickets.TicketNumber, history.remarks
    FROM
    AT_DeviceReplacement_Tickets tickets
    INNER JOIN
    AT_DeviceReplacement_Tickets_History history
    ON tickets.TicketNumber = history.TicketNumber;

我在回复中得到一个这样的表：

ticketNumber | remarks
-------------+------------
           1 | "Hello, there is a problem."
           1 | "Did you check the power cable?
           1 | "We plugged it in and now it works.  Thank you!"
           2 | "Hello, this is a new ticket."

假设我想编写一个查询来连接每张票证的备注并返回这样的表：

ticketNumber | remarks
-------------+------------
           1 | "Hello, there is a problem.Did you check the power cable?We plugged it in and now it works.  Thank you!"
           2 | "Hello, this is a new ticket."

是的，在真实的代码中，我'实际上，除其他外，我们已经按日期排序了这些内容，但为了讨论，我将如何编辑上面的查询以获得我描述的结果？

原文

Suppose I have a SQL query like this:

    SELECT
    tickets.TicketNumber, history.remarks
    FROM
    AT_DeviceReplacement_Tickets tickets
    INNER JOIN
    AT_DeviceReplacement_Tickets_History history
    ON tickets.TicketNumber = history.TicketNumber;

I get a table like this in repsonse:

ticketNumber | remarks
-------------+------------
           1 | "Hello, there is a problem."
           1 | "Did you check the power cable?
           1 | "We plugged it in and now it works.  Thank you!"
           2 | "Hello, this is a new ticket."

Suppose that I want to write a query that will concatenate the remarks for each ticket and return a table like this:

ticketNumber | remarks
-------------+------------
           1 | "Hello, there is a problem.Did you check the power cable?We plugged it in and now it works.  Thank you!"
           2 | "Hello, this is a new ticket."

Yes, in the real code, I've actually got these sorted by date, among other things, but just for the sake of discussion, how would I edit the above query to get the result I described?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

摘星┃星的人 2024-11-10 13:01:07

查看以下问题：

我可以吗逗号将多行分隔成一列？

是否可以使用 CTE 将列值连接成字符串？

回复收藏 0 原文

屋顶上的小猫咪 2024-11-10 13:01:07

此问题最干净的解决方案是依赖于数据库的。 Lentine 的链接显示了 Oracle 和 SQL Server 的非常丑陋的解决方案以及 MySQL 的干净解决方案。 PostgreSQL 中的答案也非常简短和简单。

SELECT ticket_number, string_agg(remarks, ', ')
FROM 
AT_DeviceReplacement_Tickets tickets
INNER JOIN
AT_DeviceReplacement_Tickets_History history
ON tickets.Ticket_Number = history.Ticket_Number
GROUP BY tickets.ticket_number;

（请注意，示例代码中同时包含 Ticket_number 和 TicketNumber。）

我的猜测是，Oracle 和 SQL Server 要么 (1) 具有类似的聚合函数，要么 (2) 具有定义您自己的聚合函数的能力。 [对于 MySQL，等效的聚合称为 GROUP_CONCAT。] 您使用什么数据库？

The cleanest solution to this problem is DB dependent. Lentine's links show very ugly solutions for Oracle and SQL Server and a clean one for MySQL. The answer in PostgreSQL is also very short and easy.

SELECT ticket_number, string_agg(remarks, ', ')
FROM 
AT_DeviceReplacement_Tickets tickets
INNER JOIN
AT_DeviceReplacement_Tickets_History history
ON tickets.Ticket_Number = history.Ticket_Number
GROUP BY tickets.ticket_number;

(Note you have both ticket_number and TicketNumber in your sample code.)

My guess is that Oracle and SQL Server either (1) have a similar aggregate function or (2) have the capability of defining your own aggregate functions. [For MySQL the equivalent aggregate is called GROUP_CONCAT.] What DB are you using?

回复收藏 0 原文

~没有更多了~