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版本的问题

@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 <> 0
            ) t1
            JOIN tf_store_info t2 ON t1._id = t2.id
        WHERE
            t2.STATUS = 'E'
        ORDER BY
            CONVERT ( NAME USING gbk )