SQL Server 2014相当于mysql的find_in_set（） [英] SQL Server 2014 equivalent to mysql's find_in_set()

问题描述

我正在使用具有位置表的数据库，例如：

  locationID | locationHierarchy 
 1 | 0 
 2 | 1 
 3 | 1,2 
 4 | 1 
 5 | 1,4 
 6 | 1,4,5 
  

这样就形成了这样的树形

  1 
 --2 
 ---- 3 
 --4 
 ---- 5 
 ------ 6 
  

其中locationHierarchy是其所有ancesters的locationID的csv字符串（考虑层次树）。这使得在向给定起始位置ID的树的顶部确定层级时容易确定。

现在我需要编写代码以从祖先开始并递归地找到所有后代。 MySQL有一个名为'find_in_set'的函数，它很容易解析csv字符串以查找值。这很好，因为我可以说find in set the value 4，这将给位置ID为4的后代的所有位置（包括4本身）。

正在SQL Server 2014上开发，它没有这样的功能。 CSV字符串是一个可变长度（允许的几乎无限级别），我需要一种方法来找到一个位置的所有祖先。

很多我发现的互联网模仿到SQL Server的find_in_set函数假设一个固定的层次结构深度，如4级最大）。这对我来说不起作用。

有人有一个存储过程或任何我可以集成到查询？我真的不想拉这个表中的所有记录来使用代码来单独解析CSV字符串。

我想象搜索locationHierarchy字符串为locationID％ ％，{locationid}，％会工作，但很慢。

解决方案

我想你想要 - 在任一数据库中。类似这样的：

  select l。* 
从位置l 
其中l.locationHierarchy像@LocationHierarchy +'，％'; 
  

如果您希望包含原始位置，则一种方法是：

选择l。*
从位置l
其中l.locationHierarchy +'，'like @LocationHierarchy +'，％';


我还应该注意，SQL Server对递归查询有适当的支持，所以它有层次结构的其他选项除了层次树（它仍然是一个非常合理的解决方案）。

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).

这篇关于SQL Server 2014相当于mysql的find_in_set（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！