MySql的复杂查询

案例一：

有表message和user。

表message
messageid senderid recipientid content
1 1 2 "xxx"
2 2 3 "yyy"
3 1 3 "123"

表user
userid username
1 "Tom"
2 "Jim"
3 "Rose"

希望查询的结果如下：
id sender recipient content
1 "Tom" "Jim" "xxx"
2 "Jim" "Rose" "yyy"
3 "Tom" "Rose" "123"

MySql的查询语句如下：
SELECT s.messageid,s.username as "sender",b.username as "recipient"
FROM message a
INNER JOIN user b
ON b.userid=a.recipientid
RIGHT JOIN
(
SELECT a.messageid,b.username
FROM message a
INNER JOIN user b
ON b.userid=a.senderid
) s
ON s.messageid=a.messageid

---

案例二：

有表devicerecord和user。

表devicerecord
recordid device1 device2 device3 usetime
1 3 1 2 "2017-04-10 00:00:00"
2 2 1 3 "2017-04-11 01:00:00"
3 3 3 2 "2017-04-11 02:30:00"

表user
同上。

希望查询的结果如下：
2 "Jim" "Tom" "Rose" "2017-04-11 01:00:00"
3 "Rose" "Rose" "Jim" "2017-04-11 02:30:00"

MySql的查询语句如下：
SELECT m.recordid,col1.realname as "c1",col2.realname "c2",col3.realname as "c3",userdate
FROM deviceuserecord m
INNER JOIN user AS col1 ON m.device1=col1.userid
INNER JOIN user AS col2 ON m.device2=col2.userid
INNER JOIN user AS col3 ON m.device3=col3.userid
WHERE usetime>'2017-04-11 00:00:00'

转载于:https://www.cnblogs.com/luckymonkey/p/6693958.html