MySQL Pivot rows to columns for a similar record
I have got two tables:
- tbl_sms
- tbl_bids
The above two table holds something like this:
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
With those two tables I'm trying to achieve the following:
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
The query I had written is:
SELECT b.post_id,
sms.message_sent_at as notif_sent1,
开发者_如何学C 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
That gives me the correct result but they are not pivoted the way I want it to be.
Can please someone help me out with this one. I welcome any solution be it a full lengthy query or a procedure.
In SQL Server this would be easy to accomplish using a ROW_NUMBER()
function or CROSS APPLY
construct. In MySQL this is harder.
One solution is to emulate ROW_NUMBER() in MySQL using variables. This way it is possible to return the bids for each post id ranked on bid time, and get the user_ids. From there on it is an easy matter to LEFT JOIN the SMS time to the post_id/user_id combination. Following the example in the link, the code will be something like:
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;
You'll then have an output like this:
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 | .......
You can store this result in a temporary table:
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)
Unfortunately due to a MySQL limitation you can't use multiple references to the same temporary table in a query, so you'll have to split out this table by ranking:
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...
If the recordset is very large, it pays off to assign a (primary key) index on Post_ID to speed up the pivoting query.
Now you can pivot this data:
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 has a point though, it's more scalable to build your system around an unpivoted table. So if you don't need to pivot, don't.
精彩评论