MySQL 将行透视为相似记录的列

Question

我有两个表：

1. tbl_sms
2. tbl_bids

上面两个表包含类似这样的内容：

tbl_sms:

Post_ID | User_ID | SMS_Sent_DT
---------------------------------
123     |   007   |   2010-05-14 10:15:25
123     |   008   |   2010-05-14 10:18:32
123     |   009   |   2010-05-14 10:23:05
123     |   010   |   2010-05-14 10:23:06

tbl_bids:

Post_ID | User_ID | Bid_DT
--------------------------
123     |   010   |   2010-05-14 10:27:25
123     |   008   |   2010-05-14 10:28:32
123     |   009   |  2010-05-14 10:28:47
123     |   007   |   2010-05-14 10:35:06

通过这两个表，我试图实现以下目标：

Post_ID | First_BID_Time | First_BID_SMS_TIME | Second_BID_Time | Second_BID_SMS_Time | Third_BID_Time | Third_BID_SMS_Time
-----------------------------------------------------------------------------------------------------------------------------------
123     | 2010-05-14 10:27:25 | 2010-05-14 10:23:06 | 2010-05-14 10:28:32 | 2010-05-14 10:18:32 | 2010-05-14 10:28:47 | 2010-05-14 10:23:05

---

我编写的查询是：

   SELECT b.post_id,
          sms.message_sent_at as notif_sent1,
          b.message_sent_at as notif_accepted1,
          DATEDIFF(b.message_sent_at, sms.message_sent_at) AS delay1
     FROM tbl_bids b
LEFT JOIN tbl_sms_status sms ON (sms.jobid = b.post_id AND b.user_id = sms.userid)
    WHERE b.post_id = sms.jobid
 ORDER BY b.post_id ASC

这给了我正确的结果，但它们是没有按照我想要的方式旋转。

请有人帮我解决这个问题。我欢迎任何解决方案，无论是完整的冗长查询还是过程。

Answer 1

在 SQL Server 中，使用 ROW_NUMBER() 函数或 CROSS APPLY 构造可以轻松实现这一点。在 MySQL 中，这更难。

一种解决方案是在 MySQL 中模拟 ROW_NUMBER()使用变量。这样就可以返回按出价时间排名的每个帖子 ID 的出价，并获取 user_ids。从那时起，将 SMS 时间左连接到 post_id/user_id 组合就很容易了。按照链接中的示例，代码将类似于：

SELECT tmp.Post_ID, tmp.ranking, tmp.user_ID, tmp.Bid_DT, s.SMS_DT
FROM ( 
  SELECT 
    b.Post_ID, b.user_ID, b.Bid_DT, 
    IF( @prev <> ID, @rownum := 1, @rownum := @rownum+1 ) AS ranking, 
    @prev := ID 
  FROM tbl_bids b 
  JOIN (SELECT @rownum := NULL, @prev := 0) AS r 
  ORDER BY b.Post_ID, b.BID_DT 
) AS tmp
LEFT JOIN tbl_sms s
  ON tmp.Post_ID = s.Post_ID AND tmp.user_ID = s.user_ID 
WHERE tmp.rank <= 3 -- Top 3, adjust when more are necessary 
ORDER BY post_ID, ranking; 

然后您将得到如下输出：

Post_ID | Ranking  | User_ID | Bid_DT                 | SMS_DT
---------------------------------------------------------------------------
123     |    1     |   010   |   2010-05-14 10:27:25  | 2010-05-14 10:23:06
123     |    2     |   008   |   2010-05-14 10:28:32  | ....
123     |    3     |   009   |   2010-05-14 10:28:47  | ....
123     |    4     |   007   |   2010-05-14 10:35:06  | ....
124     |    1     | .......

您可以将此结果存储在临时表中：

CREATE TEMPORARY TABLE RankedBids(Post_ID INTEGER, Ranking INTEGER, User_ID INTEGER, Bid_DT DATETIME, SMS_DT DATETIME)
INSERT INTO Rankedbids SELECT.... (use above query)

不幸的是，由于 MySQL 限制 您不能在查询中使用对同一个临时表的多个引用，因此您必须拆分该表按排名：

CREATE TEMPORARY TABLE RankedBids1(Post_ID INTEGER, User_ID INTEGER, Bid_DT DATETIME, SMS_DT DATETIME)
CREATE TEMPORARY TABLE RankedBids2....
INSERT INTO Rankedbids1 SELECT Post_ID, User_ID, Bid_DT, SMS_DT FROM RankedBids WHERE Ranking = 1
INSERT INTO RankedBids2...

如果记录集非常大，则可以在 Post_ID 上分配（主键）索引以加速旋转查询。

现在您可以旋转这些数据：

SELECT R1.Post_ID, R1.Bid_DT AS Bid_DT1, R1.SMS_DT AS SMS_DT1 .... 
FROM RankedBids1 R1
LEFT JOIN RankedBids2 R2 ON R1.Post_ID = R2.Post_ID
LEFT JOIN RankedBids3 R3 ON ........

OMG Ponies 有一个观点，围绕未旋转的表构建系统更具可扩展性。所以，如果你不需要转向，就不要转向。