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'