mysql递归查询上级（父级id）

select t2.id,t2.pid 
    from(
        select 
            @r as _id,
            (select @r := pid from tf_store_info where id = _id) as parent_id,
            @I := @I + 1 as lvl
      from
            (select @r :=1,@I := 0) vars,
            tf_store_info h
        where @r <> 0) t1
    join tf_store_info t2
on t1._id = t2.id
ORDER BY id ;

　　如果上面不能查出数据，用这个，尽量用这个（其实是再套一层），因为有的mysql库执行上面那个没数据，原因不详（大致可以认为是mysql版本的问题

1	`@r` `<>` `0` `只知道这个不起作用，一加进去就没数据了`

）

SELECT
            t2.id storeId,
            t2.NAME storeName
        FROM
            (select _id,parent_id from
                (SELECT
                    @r AS _id,
                    ( SELECT @r := pid FROM tf_store_info WHERE id = _id ) AS parent_id,
                    @I := @I + 1 AS lvl
                FROM
                    ( SELECT @r := #{storeId}, @I := 0 ) vars,
                    tf_store_info h
                WHERE
                    STATUS = 'E'
                ) a where a._id &lt;&gt; 0
            ) t1
            JOIN tf_store_info t2 ON t1._id = t2.id
        WHERE
            t2.STATUS = 'E'
        ORDER BY
            CONVERT ( NAME USING gbk )