table_old
---------
id name
table_new
---------
id name
两个表连接问题、
现有两个表table_old ,table_new
两个表结构完全相同。条件:1 把table_old ,table_new 连接成一个表 table_2009(id,name)
2 id相同的数据记录(),name 取table_new里的值
3 table_old 里专有的ID,在新表里,每条NAME数据前面加上"old"
4 table_old 表里的ID前有S,table_new的没有, table_2009表ID 要加S例子:
table_old
---------
id name
S001 张
S002 王
S004 孙table_new
---------
id name
001 张
002 李
003 赵出来的结构应该是:
table_2009
---------
id name
S001 张
S002 李
S003 赵
S004 old孙(刚才在sql server试过一些大家提供的语句 总是出不来 ,现在转到mysql 看看再 谢谢)
UNION ALL
SELECT A.ID,CONCAT('OLD',A.NAME) FROM A LEFT JOIN B ON ON B.ID=RIGHT(A.ID,3)
WHERE B.ID IS NULL
ORDER BY NEWID
select concat('S',B.ID) AS NEWID,B.NAME FROM table_new B INNER JOIN
table_old A ON B.ID=RIGHT(A.ID,3)
UNION ALL
SELECT A.ID,CONCAT('OLD',A.NAME) FROM table_old A LEFT JOIN
table_new B ON ON B.ID=RIGHT(A.ID,3)
WHERE B.ID IS NULL
ORDER BY NEWID
table_old A ON B.ID=RIGHT(A.ID,3)
UNION ALL
SELECT A.ID,CONCAT('OLD',A.NAME) FROM table_old A LEFT JOIN
table_new B ON B.ID=RIGHT(A.ID,3)
WHERE B.ID IS NULL
ORDER BY NEWID测试通过
select concat('S',B.ID) AS NEWID,B.NAME FROM table_new B
UNION ALL
SELECT A.ID,CONCAT('OLD',A.NAME) FROM table_old A LEFT JOIN
table_new B ON B.ID=RIGHT(A.ID,3)
WHERE B.ID IS NULL
ORDER BY NEWID