我很长一段时间都在乱扔头,我不知道该如何解决。
我有2个mysql表
table1
+----+----------------------+
| id | name | order_list |
+----+----------------------+
| 1 | Bob | 3 |
| 2 | Marc | 2 |
| 3 | Edith | 1 |
| 4 | John | 4 |
+----+----------------------+
table2
+----+-------------------+
| id | user_id | sale |
+----+-------------------+
| 1 | 3 | 30 |
| 2 | 2 | 40 |
| 3 | 1 | 100 |
| 4 | 3 | 75 |
| 5 | 2 | 40 |
| 6 | 1 | 100 |
| 7 | 4 | 75 |
+----+-------------------+
我需要从user_id的表1的order_list从表2的表2中从表2中购买全部购买。
现在,我在这样的PHP中这样做。1查询以从表1订单订单订单中获取ID。一个循环在1个查询中,每个员工都可以进行销售
我需要独立的每笔销售,而不是总和或组。
query to get the id from table 1 order by order_list
loop
query the table2 for the sale of the looped employee
end loop
我知道这可以用这样的事情来完成。
SELECT * FROM table2 ORDER BY FIELD (id,order from table 1)
基本的join
应该足够:
select t2.*
from table2 t2 join
table1 t1
on t2.user_id = t1.id
order by t1.order_list;
或,如果您愿意,请在order by
中放置一个子查询:
select t2.*
from table2 t2
order by (select t1.order_list from table1 t1 where t1.id = t2.user_id);
SELECT a.* FROM table2 a
LEFT JOIN table1 b on a.user_id = b.id
ORDER BY b.order_list
只需加入它们,以便您可以从2个表中访问列。