mysql 连接三个表,Mysql连接三个表(其中两个相似)

我的客户表

id | name

==========

1 | mick

2 | george

3 | john

面包店交易

id | customer | type | amount | date

============================================

1 | 3 | bread | 1 | 2016/03/10 10:00

2 | 1 | bread | 2 | 2016/03/10 11:00

3 | 1 | baguette| 1 | 2016/03/10 11:00

4 | 2 | bread | 2 | 2016/03/11 09:00

5 | 3 | cookie | 5 | 2016/03/11 09:30

蔬菜杂货交易

id | customer | type | amount | date

============================================

1 | 1 | banana | 1 | 2016/03/10 08:00

2 | 1 | apple | 3 | 2016/03/10 08:00

3 | 1 | orange | 5 | 2016/03/10 14:00

4 | 3 | apple | 8 | 2016/03/11 09:30

5 | 1 | apple | 8 | 2016/03/12 09:30

客户是否可以从这些交易表中按日期获取客户交易？

更具体地说,我想获取客户ID：1交易,按日期排序；

这就是我要的.

Id| transactionType|customer | type | amount | date

=====================================================================

1 | greengrocery | 1 | banana | 1 | 2016/03/10 08:00

2 | greengrocery | 1 | apple | 3 | 2016/03/10 08:00

2 | bakery | 1 | bread | 2 | 2016/03/10 11:00

3 | bakery | 1 | baguette| 2 | 2016/03/10 11:00

4 | greengrocery | 1 | orange | 5 | 2016/03/10 14:00

5 | greengrocery | 1 | apple | 8 | 2016/03/12 09:30

解决方法:

基本上,您需要两个联合在一起的联合查询,如下所示：

SELECT * FROM (

SELECT t.id,'bakery' as TransType,s.id as customer,t.type,t.amount,t.date

FROM customer s

INNER JOIN bakery t ON(s.id = t.customer)

UNION ALL

SELECT t.id,'greengrocery' as TransType,s.id as customer,t.type,t.amount,t.date

FROM customer s

INNER JOIN greengrocery t ON(s.id = t.customer)) tt

WHERE tt.customer = 1

order by tt.date

基本上,您甚至不需要从客户中进行选择,因为您没有使用名称值,因此可以这样进行：

SELECT * FROM (

SELECT t.id,'bakery' as TransType,t.customer as customer,t.type,t.amount,t.date

FROM bakery t

UNION ALL

SELECT t.id,'greengrocery' as TransType,t.customer as customer,t.type,t.amount,t.date

FROM greengrocery t) tt

WHERE tt.customer = 1

order by tt.date

标签：join,mysql

来源： https://codeday.me/bug/20191118/2032147.html