感觉较难的一道sql，想不通，高手进

Question

select * from tsoid;
+------+-------+
| soid | cus   |
+------+-------+
| 1001 | Gavin |
| 1002 | aduan |
| 1003 | LH    |
+------+-------+
select * from order1;
+----------+-----------+
| orderNo  | soid      |
+----------+-----------+
| 20090513 | 1002/1003 |
| 20090514 | 1001      |
+----------+-----------+
怎么获得如下结果：
orderNo    soid        cus 
20090513    1002/1003    aduan/LH 
20090514    1001        Gavin
最好用mysql实现

Answer 1

mysql> select a.orderNo,2.soid,group_concat(cus separator '/') from order1 a lef
t join tsoid b on find_in_set(b.soid,replace(a.soid,"/",",")) group by orderNo;
+----------+------+---------------------------------+
| orderNo  | soid | group_concat(cus separator '/') |
+----------+------+---------------------------------+
| 20090513 | 1002/1003 | aduan/LH                        |
| 20090514 | 1001 | Gavin                           |
+----------+------+---------------------------------+
2 rows in set (0.00 sec)

Answer 2

上面贴错了
mysql> select a.orderNo,a.soid,group_concat(cus separator '/') from order1 a lef
t join tsoid b on find_in_set(b.soid,replace(a.soid,"/",",")) group by orderNo;
+----------+------+---------------------------------+
| orderNo  | soid | group_concat(cus separator '/') |
+----------+------+---------------------------------+
| 20090513 | 1002/1003 | aduan/LH                        |
| 20090514 | 1001 | Gavin                           |
+----------+------+---------------------------------+
2 rows in set (0.00 sec)

Answer 3

select o.orderNo,o.soid,GROUP_CONCAT(cus)
from order1 o ,tsoid t
where FIND_IN_SET(t.soid,REPLACE(o.soid,'/',',') ) 
group by o.orderNo,o.soid

Answer 4

有没有人有mysql cookbook和sql cookbook 中文版的书，帮忙发给我一份啊。。[email protected] 
十分感谢。。

Answer 5

啊，没想到lzl8146 更快。我还想了半天。这些主要是对MySQL的函数的熟悉，你把MySQL官方文档中的函数介绍看一遍就行了。
http://dev.mysql.com/doc/refman/5.1/zh/functions.html
第12章：函数和操作符

Answer 6

select a.orderNo,a.soid,group_concat(b.cus separator '/') from tt2a a left join tt1a b on instr(a.soid,b.soid)>0
group by a.orderNo,a.soid