从每个记录中选择逗号分隔值的最小值/最大值

consider below table and its records

考虑下表及其记录

create table dbo.test
(
id  numeric(4),
vals    nvarchar(1000)
);

insert into dbo.test values (1,'1,2,3,4,5');
insert into dbo.test values (2,'6,7,8,9,0');
insert into dbo.test values (3,'11,54,76,23');

I am going to use below function to split CSVs, you can use any method to help in select syntax

我将使用以下函数来分割CSV,您可以使用任何方法来帮助选择语法

CREATE FUNCTION [aml].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int     
declare @slice varchar(8000)     

select @idx = 1     
    if len(@String)<1 or @String is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@String)     
    if @idx!=0     
        set @slice = left(@String,@idx - 1)     
    else     
        set @slice = @String     

    if(len(@slice)>0)
        insert into @temptable(Items) values(@slice)     

    set @String = right(@String,len(@String) - @idx)     
    if len(@String) = 0 break     
end 
return     
end

I want to select id and max and min values from vals against each record.

我想从vals中为每条记录选择id,max和min值。

Update Though i am writing the query on SQL Server 2008 but i need to support SQL Server 2005 and above

更新虽然我在SQL Server 2008上编写查询但我需要支持SQL Server 2005及更高版本

3 个解决方案

#1

You can CROSS APPLY to the table projected by the function and then apply normal aggregation functions on each group of id :

您可以CROSS APPLY到函数投影的表,然后对每组id应用常规聚合函数:

SELECT t.id, MIN(CAST(x.items AS INT)) AS MinItem, MAX(CAST(x.items AS INT)) AS MaxItem
FROM dbo.test t 
     CROSS APPLY dbo.Split(t.vals, ',') x
GROUP BY t.id;

(Edit - since these appear to be integers, you'll want to cast before applying the MIN / MAX aggregates otherwise you'll get an alphanumeric sort)

(编辑 - 因为这些似乎是整数,你需要在应用MIN / MAX聚合之前进行投射,否则你会得到一个字母数字排序)

SqlFiddle example here

这里有SqlFiddle示例

Another option is to persist the comma separated list in a normalized table structure before applying queries over them - it isn't useful storing non-normalized data in an RDBMS :)

另一种选择是在对它们应用查询之前将逗号分隔列表保持在规范化表结构中 - 将非规范化数据存储在RDBMS中是没有用的:)

#2

Without function, just plain sql:

没有函数,只是简单的sql:

SELECT t.id,  
       Max(Split.a.value('.', 'VARCHAR(100)')) AS MaxVal,
       Min(Split.a.value('.', 'VARCHAR(100)')) AS MinVal
 FROM
 (
     SELECT id,  
         CAST ('<M>' + REPLACE(vals, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  test
 ) AS t CROSS APPLY Data.nodes ('/M') AS Split(a)
 group by t.id

Fiddle http://sqlfiddle.com/#!3/22321/6

#3

select A.id,(select max(item) from dbo.Split(A.vals,',')) as maxvalue,(select min(item) from dbo.Split(A.vals,',')) as minvalue from test A

选择A.id,(从dbo.Split(A.vals,',')中选择max(item))作为maxvalue,(从dbo.Split(A.vals,',')中选择min(item))作为minvalue来自测试A.

Create Function For fplit CREATE FUNCTION Split ( @InputString VARCHAR(8000), @Delimiter VARCHAR(50) )

为fplit CREATE FUNCTION Split创建函数(@InputString VARCHAR(8000),@ Delimiter VARCHAR(50))

RETURNS @Items TABLE ( Item VARCHAR(8000) )

RETURNS @Items TABLE(Item VARCHAR(8000))

AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE(@InputString, ' ', @Delimiter) END

AS BEGIN如果@Delimiter =''BEGIN SET @Delimiter =','SET @InputString = REPLACE(@InputString,'',@ Delimiter)END

  IF (@Delimiter IS NULL OR @Delimiter = '')
        SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic --INSERT INTO @Items VALUES (@InputString) -- Diagnostic

--INSERT INTO @Items VALUES(@Delimiter) - 诊断--INSERT INTO @Items VALUES(@InputString) - 诊断

  DECLARE @Item                 VARCHAR(8000)
  DECLARE @ItemList       VARCHAR(8000)
  DECLARE @DelimIndex     INT

  SET @ItemList = @InputString
  SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
  WHILE (@DelimIndex != 0)
  BEGIN
        SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
        INSERT INTO @Items VALUES (@Item)

        -- Set @ItemList = @ItemList minus one less item
        SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
        SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
  END -- End WHILE

  IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
  BEGIN
        SET @Item = @ItemList
        INSERT INTO @Items VALUES (@Item)
  END

  -- No delimiters were encountered in @InputString, so just return @InputString
  ELSE INSERT INTO @Items VALUES (@InputString)

  RETURN

END -- End Function GO

结束 - 结束功能GO

#1