mysql - MySQL 中的左外连接

Question

是否可以连接这两个表以获得以下结果。

---

Table Stats
Date         Cus_ID    Pur        Amount 2015-02-01   2585711    2           1002015-02-02   2585711    5           2502015-05-03   2585711    8           4002015-02-01   2585475    2           1002015-02-02   2585475    5           2502015-05-03   2585475    8           400 
  

Table Customer     Reg_Date     Cus_ID    Gender   Country2014-04-11   2585711    Male    Sweden2015-02-01   2585475    Female  Sweden
  

Expected ResultDate        Cus_ID   Pur       Amount   Gender  Country2014-04-11  2585711  NULL       NULL    Male    Sweden2015-02-01  2585711  2          100     Male    Sweden2015-02-02  2585711  5          250     Male    Sweden2015-05-03  2585711  8          400     Male    Sweden2015-02-01  2585475  2          100     Female  Sweden2015-02-02  2585475  5          250     Female  Sweden2015-05-03  2585475  8          400     Female  Sweden
  

if I use a left outer join to join the two tables, I get the following resultDate        Cus_ID  Pur Amt Gender  Country2015-02-01  2585711 2   100 Male    Sweden2015-02-02  2585711 5   250 Male    Sweden2015-05-03  2585711 8   400 Male    Sweden2015-02-01  2585475 2   100 Female  Sweden2015-02-02  2585475 5   250 Female  Sweden2015-05-03  2585475 8   400 Female  Sweden

My query:Select if(a.Date IS NULL,b.reg_date,a.date) as Date,b.cus_id,pur,Amount,gender,country     from         (Select * from stats) as a                 Left outer join                      (select * from customer) as bon a.cus_id = b.cus_id

任何帮助将不胜感激。谢谢!

Answer 1

我不知道这是否仍然相关。

如果要显示客户活跃的每个日期，即使他们没有购买，我也会从一个首先提供所有日期和客户对的表格开始。

select [date],[cust_id] 
into #activity
from stats
union
select [reg_date],[cust_id]
from customer


select t1.*, t2.pur, t2.amt
from #activity t1
left outer join stats t2 on 
t1.[date] = t2.[date] and t1.[cust_id] = t2.[cust_id]