SELECT
u.id
FROM
user u,
(SELECT
id,
tree
FROM
user
WHERE id IN (30, 59, 58, 57, 56, 60, 61, 62, 63)) a
WHERE u.tree LIKE CONCAT('1,', a.id)
ORDER BY u.id ASC
tree列的数据为:
1
1
1,30
1,59
1,59,100
....
要查出(1,*)的数据,这个SQL要怎么优化?
...全文
1116打赏收藏
请教,优化一个SQL语句!!!
SELECT u.id FROM user u, (SELECT id, tree FROM user WHERE id IN (30, 59, 58, 57, 56, 60, 61, 62, 63)) a WHERE u.tree LIKE CONCAT('1,', a.id) ORDER BY u.id ASC tree列的数据为: 1 1 1,30 1,59 1,59,100 .... 要查出(1,*)的数据,这个SQL要怎么优化?
SELECT id FROM user WHERE LEFT(tree,2)='1,' AND (SUBSTR(tree,3,IF(locate(',',tree,3) > 0,locate(',',tree,3)-3,length(tree)-2))+0) IN (30, 59, 58, 57, 56, 60, 61, 62, 63)