从TableA到TableB如何实现?
TableA:
ID FeatureID
1 sp1;sp2
2 null
3 sp2
4 sp1;sp3TableB:
FeatureID ID
sp1 1
sp1 4
sp2 1
sp2 3
sp3 4
TableA:
ID FeatureID
1 sp1;sp2
2 null
3 sp2
4 sp1;sp3TableB:
FeatureID ID
sp1 1
sp1 4
sp2 1
sp2 3
sp3 4
2。 使用辅助表 S (ID) (1,2,3,4,5,6,....) 然后做JOIN查询
FROM
(
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(FeatureID, ';', cnt), ';', -1) FeatureID, ID, cnt
FROM TableA,
(
SELECT 1 cnt UNION ALL
SELECT 2 UNION ALL
SELECT 3 /*UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 */
) x -- 如楼上所述,这个最好建辅助表
) y
WHERE FeatureID IS NOT NULL
ORDER BY FeatureID