sql - 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 中，这是 harder .

一个解决方案是 emulate ROW_NUMBER() in MySQL使用变量。这样就可以返回在出价时间排名的每个帖子 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 limitation您不能在查询中使用对同一个临时表的多个引用，因此您必须按排名拆分此表:

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 虽然有一点，但围绕非透视表构建系统更具可扩展性。因此，如果您不需要转向，就不要转向。