Question

我需要一个可以用于(或作为)一种功能的问询,并检索所有微值的变动。而且,我需要所有长度k=1.n的间隔。

EDIT#1: Extended sample input and result so input has 3 values instead of 2 - however, number of input values may vary from 1 to n.

例:

Input: table with values in one column in multiple rows

    Value  (nvarchar(500))
    ------
    Ann
    John
    Mark

Edit#2:我可以享受以下任何产出:

产出1:在一栏中排列的数值表

    Ann
    John
    Ann,John
    John,Ann
    Ann,Mark
    Mark,Ann
    John,Mark
    Mark,John
    Ann,John,Mark
    Ann,Mark,John
    John,Ann,Mark
    John,Mark,Ann
    Mark,Ann,John
    Mark,John,Ann

产出2:以折现/合并、价值和秩序为单位的表格

    id    value    order
    --------------------
    1     Ann      1
    2     John     1
    3     Ann      1
    3     John     2
    4     John     1
    5     Ann      2

注:

must work on SQL Server 2008 R2
must work within function
maximum number of expected distinct elements in input should never exceed 5 - if this matters for performance or any other reason
Value column in input table is not likely to exceed 200 characters in length - if this matters for performance or any other reason

在对互联网进行大量搜索后,发现。在T-SQL论坛中进行变换,但我没有设法加以修改,以给出预期产出格式,迫使它作出每班1.n。

解决类似问题的办法很多,主要是用非瑞典语书写的,但我无法找到有助于我解决这一具体问题的办法。

Answer 1

;WITH Names(Name) As
(
SELECT  Ann  UNION ALL
SELECT  John  UNION ALL
SELECT  Mark  
), R(Name,Lvl) AS
(
SELECT CAST( ,  + Name AS VARCHAR(MAX)), 1
FROM Names
UNION ALL
SELECT R.Name +  ,  + N.Name, Lvl + 1
FROM R JOIN Names N ON R.Name +  ,  NOT LIKE  %,  + N.Name +  ,% 
)
SELECT STUFF(Name,1,1,  ) AS Name
FROM R
ORDER BY Lvl, Name

Returns

Name
------------------------------
Ann
John
Mark
Ann,John
Ann,Mark
John,Ann
John,Mark
Mark,Ann
Mark,John
Ann,John,Mark
Ann,Mark,John
John,Ann,Mark
John,Mark,Ann
Mark,Ann,John
Mark,John,Ann

友情链接