将 group_concat 与其他选择标准结合使用 - mySQL

发布于 2024-09-25 14:02:30 字数 936 浏览 5 评论 0原文

我有两个表，arrc_PurchActivity 和arrc_Voucher。购买活动表包含每次购买的多个记录，这些记录通过信用卡授权绑定在一起。我需要在每次购买时返回一行，并从凭证表中提取一个字段。如果我只是从购买活动表中获取一些字段，如下所示：

SELECT group_concat( VoucherID ) , CcAuthCode FROM arrc_PurchaseActivity GROUP BY CcAuthCode

它工作正常，返回如下内容：

group_concat( VoucherID )  | CcAuthCode
=========================================
610643,611139,610642       | 8LUPDN

我需要做的是拉入另一个连接字段 (VoucherNbr)，这次是从 arrc_Voucher 表中提取，其中凭证表的 VoucherID 等于购买表的 VoucherID。在本例中，由于 VoucherID 是串联，因此我需要为串联列中的每个 VoucherID 返回 VoucherNbr 的串联列。清澈如泥，对吧？我需要的是这样的：

group_concat( VoucherID )  | group_concat( VoucherNbr)  |  CcAuthCode
===========================|============================|=============
610643,611139,610642       | 123,456,789                |  8LUPDN

换句话说，VoucherID 610643 的 VoucherNbr 是 123，611139 的 VoucherNbr 是 456，等等。

有人能帮我吗？这太超出我的想象了...

原文

I have two tables, arrc_PurchActivity and arrc_Voucher. The purchase activity table contains multiple records per purchase, tied together by the credit card authorization. I need to return a single row per purchase, plus pull in a field from the voucher table.
If I just grab a few fields from the purchase activity table, like this:

SELECT group_concat( VoucherID ) , CcAuthCode FROM arrc_PurchaseActivity GROUP BY CcAuthCode

it works fine, returning something like this:

group_concat( VoucherID )  | CcAuthCode
=========================================
610643,611139,610642       | 8LUPDN

What I need to do is pull in another contatenated field (VoucherNbr), this time from the arrc_Voucher table, where the voucher table's VoucherID is equal to the purchase table's VoucherID. In this case, because VoucherID is a concatenation, I need to return a concatenated column of VoucherNbr for each VoucherID in the concatenated column. Clear as mud, right? What I need would look like this:

group_concat( VoucherID )  | group_concat( VoucherNbr)  |  CcAuthCode
===========================|============================|=============
610643,611139,610642       | 123,456,789                |  8LUPDN

In other words, the VoucherNbr for VoucherID 610643 is 123, VoucherNbr for 611139 is 456, etc.

Can anyone help me out? This is way over my head...

分享到QQ

分享到微博

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

发布评论

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

情何以堪。 2024-10-02 14:02:30

使用：

   SELECT pa.ccauthcode,
          GROUP_CONCAT(DISTINCT pa.voucherid) AS voucherids,
          GROUP_CONCAT(v.vouchernbr) AS vouchernbrs
     FROM ARRC_PURCHASEACTIVITY pa 
LEFT JOIN ARRC_VOUCHER v ON v.voucherid = pa.voucherid
 GROUP BY pa.ccauthcode

我在 GROUP_CONCAT 中为凭证 ID 指定了 DISTINCT，因为一个凭证 ID 可能有多个凭证。如果没有，请删除 DISTINCT。

LEFT JOIN 可确保您获得 ARRC_VOUCHER 中没有支持记录的 ARRC_PURCHASEACTIVITY 记录。如果您不希望出现此行为，请将“LEFT JOIN”更改为“JOIN”。

Use:

   SELECT pa.ccauthcode,
          GROUP_CONCAT(DISTINCT pa.voucherid) AS voucherids,
          GROUP_CONCAT(v.vouchernbr) AS vouchernbrs
     FROM ARRC_PURCHASEACTIVITY pa 
LEFT JOIN ARRC_VOUCHER v ON v.voucherid = pa.voucherid
 GROUP BY pa.ccauthcode

I specified the DISTINCT in the GROUP_CONCAT for the voucherid's because it's possible you'd have multiple vouchernbr's to a voucherid. If not, remove the DISTINCT.

The LEFT JOIN ensures you'll get ARRC_PURCHASEACTIVITY records that do not have supporting records in ARRC_VOUCHER. Change "LEFT JOIN" to "JOIN" if you don't want this behavior.

回复收藏 0 原文

~没有更多了~