mysql得到最后2条记录，有3个表

I have 3 mysql tables (order , camp , user) as bellow values,

我有3个mysql表(order, camp, user)作为bellow值，

order_table
ID    camp_id       orderDate       message
1       1           2015-01-01      ok
2       1           2015-02-01      ok
3       2           2015-03-01      not ok
4       3           2015-01-01      not ok
5       1           2015-04-01      not ok
6       2           2015-01-01      ok
7       3           2015-07-01      not ok

camp_table
camp_id camp_uid     camp name
1       10             first camp
2       11             second camp
3       12             third camp
4       10             forth camp

user_table
uid    uname
10      abc
11      xyz
12      wrt

i want to have result as bellow

我想要的结果是吼叫。

uname,camp name,message

for last 2 records of each user from order_table for today order by orderDate

根据orderDate，从order_table中记录每个用户的最后2个记录。

I want to join these tables to have uname from user_table and camp name from camp_table and message from order_table. for today order by orderDate Thanks

我希望将这些表连接到user_table的uname和camp_table的camp名称以及order_table的消息。今天的订货单，谢谢

3 个解决方案

#1

SELECT
    u.uname,
    ct.camp_name,
    ot.message
FROM
    (
        SELECT
            *
        FROM
            order_table o1
        WHERE
            (
                SELECT
                    COUNT(*)
                FROM
                    order_table o2
                WHERE
                    o1.camp_id = o2.camp_id
                AND o2.ID >= o1.ID
            ) <= 2
    ) ot
INNER JOIN camp_table ct ON ct.camp_id = ot.camp_id
INNER JOIN user_table u ON ct.camp_uid = u.uid
ORDER BY
    u.uname

#2

Select
ct.camp_name,
ut.uname,
ot.message FROM order_table as ot
LEFT JOIN camp_table as ct on ot.camp_id = ct.camp_id
LEFT JOIN user_table as ut on ct.camp_uid = ut.uid
order by ot.id desc
limit 2

#3

Order By Date and join both table.

按日期排序并连接两个表。

  Select t1.camp_name, t2.uname,t3.message FROM order_table as t3
    LEFT JOIN camp_table as t1 on t3.camp_id = t1.camp_id
    LEFT JOIN user_table as t2 on t1.camp_uid = t2.uid
    order by t3.orderDate desc
    limit 2