我有三个表customer1customer2product,如下所示:

客户1:

c_id --- c_name
----------------
1    ---    anns

2    ---   skja

3    ---    kkjk


客户2:

c_id --- c_name
----------------
11   ---  sjhja

12   ---  skkkkk

13   ---  aaasss


产品:

p_id --- c_id ---  p_name
-------------------------
1    ---   1  ---   pen

2    ---   2  ---  card

3    ---   3  ---   cell

4    ---   11  ---  pot

5    ---   12  ---  ice

6    ---   13  ---  apple


我想用c_idproduct中的c_name替换customer1表中customer2的值

我正在使用以下sql查询:

SELECT c1.c_name or c2.c_name as customer_name, p.p_name from product p
left JOIN customer1 c1 on c1.c_id=p.c_id
left JOIN customer2 c2 on c2.c_id=p.c_id;


得到结果:

c_name -----  p_name
---------------
null   -----  pen

null   -----  card

null   -----  cell

null   -----  pot

null   -----  ice

null   -----  apple


预期结果:

c_name -----  p_name
----------------------
anns    ----- pen

skja    ----- card

kkjk    ----- cell

sjhja   ----- pot

skkkkk  ----- ice

aaasss  ----- apple


请帮助我获得预期的结果。

最佳答案

试试这个未经测试的查询:

SELECT ifnull (c1.c_name ,c2.c_name) as customer_name, p.p_name from product p
left JOIN customer1 c1 on c1.c_id=p.c_id
left JOIN customer2 c2 on c2.c_id=p.c_id;

关于mysql - 如何合并两个表的结果并在sql的单列中显示,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28038953/

10-14 18:00
查看更多