我有三个表customer1
,customer2
和product
,如下所示:
客户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_id
和product
中的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/