某表2个字段
key_code-----key_name
3------------随便
3_1__________随便1
3_2__________随便2
3_3__________随便3希望查询结果为:
key_code-----key_name------key_code1-----key_name1
3------------随便------------3_1------------随便1
3------------随便------------3_2------------随便2
3------------随便------------3_3------------随便3
就是这样
key_code-----key_name
3------------随便
3_1__________随便1
3_2__________随便2
3_3__________随便3希望查询结果为:
key_code-----key_name------key_code1-----key_name1
3------------随便------------3_1------------随便1
3------------随便------------3_2------------随便2
3------------随便------------3_3------------随便3
就是这样
mysql> select * from tt;
+----------+----------+
| key_code | key_name |
+----------+----------+
| 3 | 随便 |
| 3_1 | 随便1 |
| 3_2 | 随便2 |
| 3_3 | 随便3 |
+----------+----------+
4 rows in set (0.02 sec)mysql> select t1.key_code,t1.key_name,t2.key_code as key_code1,t2.key_name as ke
y_name1 from tt as t1 inner join tt as t2 on t1.key_code=substring_index(t2.key_
code,'_',1) where t1.key_code not RLIKE '_' and t2.key_code rlike '_';
+----------+----------+-----------+-----------+
| key_code | key_name | key_code1 | key_name1 |
+----------+----------+-----------+-----------+
| 3 | 随便 | 3_1 | 随便1 |
| 3 | 随便 | 3_2 | 随便2 |
| 3 | 随便 | 3_3 | 随便3 |
+----------+----------+-----------+-----------+
3 rows in set (0.00 sec)
from table1 a,table1 b
where instr(b.key_code,a.key_code)>0
and instr(a.key_code,'_')=0
and instr(b.key_code,'_')>0
SELECT k.*,z.*
FROM (SELECT * FROM tb WHERE locate('_',key_code)=0)K
JOIN (SELECT * FROM tb WHERE locate('_',key_code)<>0)Z ON
LEFT(z.key_code,locate('_',z.key_code)-1)=k.key_code