mysql find_in_set 递归,SQL Server 2014相当于mysql的find_in_set（）

I'm working with a database that has a locations table such as:

locationID | locationHierarchy

1 | 0

2 | 1

3 | 1,2

4 | 1

5 | 1,4

6 | 1,4,5

which makes a tree like this

1

--2

----3

--4

----5

------6

where locationHierarchy is a csv string of the locationIDs of all its ancesters (think of a hierarchy tree). This makes it easy to determine the hierarchy when working toward the top of the tree given a starting locationID.

Now I need to write code to start with an ancestor and recursively find all descendants. MySQL has a function called 'find_in_set' which easily parses a csv string to look for a value. It's nice because I can just say "find in set the value 4" which would give all locations that are descendants of locationID of 4 (including 4 itself).

Unfortunately this is being developed on SQL Server 2014 and it has no such function. The CSV string is a variable length (virtually unlimited levels allowed) and I need a way to find all ancestors of a location.

A lot of what I've found on the internet to mimic the find_in_set function into SQL Server assumes a fixed depth of hierarchy such as 4 levels maximum) which wouldn't work for me.

Does anyone have a stored procedure or anything that I could integrate into a query? I'd really rather not have to pull all records from this table to use code to individually parse the CSV string.

I would imagine searching the locationHierarchy string for locationID% or %,{locationid},% would work but be pretty slow.

解决方案

I think you want like -- in either database. Something like this:

select l.*

from locations l

where l.locationHierarchy like @LocationHierarchy + ',%';

If you want the original location included, then one method is:

select l.*

from locations l

where l.locationHierarchy + ',' like @LocationHierarchy + ',%';

I should also note that SQL Server has proper support for recursive queries, so it has other options for hierarchies apart from hierarchy trees (which are still a very reasonable solution).