难度:求字段中的最大值(字段中的数值个数不固定,是通过逗号隔开的)

来源:互联网  责任编辑:小易  时间:2017/1/10 13:43:21
用户提出问题:难度:求字段中的最大值(字段中的数值个数不固定,是通过逗号隔开的),具体如下: 求字段中的最大值

通过互联网整理获得以下解决方法:

=================1楼=====================

将字符串拆分吧

=================2楼=====================

转成 xml,或拆分字符串。

至于哪个高效,还要实际测试。

=================3楼=====================


先写一个拆分字符串的函数  然后 调用就可以了
SELECT MAX(VAL2)as[MAX] FROM(
SELECT ID,CAST(val AS INT)AS VAL2
 FROM [Func_SplitStr]('52,46,48,2150,823,183,1870',',')) AS T1
/
MAX
2150
/

=================4楼=====================

引用 3 楼 baidu_36457652 的回复:

先写一个拆分字符串的函数  然后 调用就可以了
SELECT MAX(VAL2)as[MAX] FROM(
SELECT ID,CAST(val AS INT)AS VAL2
 FROM [Func_SplitStr]('52,46,48,2150,823,183,1870',',')) AS T1
/
MAX
2150
/

完整的代码怎样的?
Func_SplitStr

=================5楼=====================

create type str_split is table of varchar2(4000) ;
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
   /**********************************************************************
    * xdshi add blockment 2015-09-21
    * 通用方法:按列返回分割字符串后的内容
    * 入参:p_string 分割字符串
    *       p_delimiter 分割符
    * 返回:每个分割出来的字符串
    **********************************************************************/
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END splitstr;

=================6楼=====================

引用 5 楼 jycjyc 的回复:
create type str_split is table of varchar2(4000) ;
CREATE OR REPLACE FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
   /**********************************************************************
    * xdshi add blockment 2015-09-21
    * 通用方法:按列返回分割字符串后的内容
    * 入参:p_string 分割字符串
    *       p_delimiter 分割符
    * 返回:每个分割出来的字符串
    **********************************************************************/
    RETURN str_split
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END splitstr;

这个代码报错消息 156,级别 15,状态 1,第 1 行
关键字 'is' 附近有语法错误。

=================7楼=====================


这是字符串分割函数
CREATE function [dbo].[Func_SplitStr](@str nvarchar(2000),@split nvarchar(10))
returns @t table(id int IDENTITY,val VARCHAR(200) )
as 
begin
declare @getIndex int
set  @getIndex=charindex(@split,@str)
  while(@getIndex<>0)   
  begin
    insert into @t(val) values (substring(@str,1,@getIndex-1))
    set @str=stuff(@str,1,@getIndex+LEN(@split)-1,'')
    set @getIndex=charindex(@split,@str)   
  end
  insert into @t(val) values (@str)
  return 

end

=================8楼=====================

引用 3 楼 baidu_36457652 的回复:

先写一个拆分字符串的函数  然后 调用就可以了
SELECT MAX(VAL2)as[MAX] FROM(
SELECT ID,CAST(val AS INT)AS VAL2
 FROM [Func_SplitStr]('52,46,48,2150,823,183,1870',',')) AS T1
/
MAX
2150
/

找到一个函数,但是怎么套用到多条记录中呢?您给的例子是单条记录的。
CREATE FUNCTION Func_SplitStr(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+'a')-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
 END
 INSERT @re VALUES(@s)
 RETURN
END
GO

引用 7 楼 baidu_36457652 的回复:

这是字符串分割函数
CREATE function [dbo].[Func_SplitStr](@str nvarchar(2000),@split nvarchar(10))
returns @t table(id int IDENTITY,val VARCHAR(200) )
as 
begin
declare @getIndex int
set  @getIndex=charindex(@split,@str)
  while(@getIndex<>0)   
  begin
    insert into @t(val) values (substring(@str,1,@getIndex-1))
    set @str=stuff(@str,1,@getIndex+LEN(@split)-1,'')
    set @getIndex=charindex(@split,@str)   
  end
  insert into @t(val) values (@str)
  return 

end

感谢,不过这个只是针对一行的处理情况。如上述例子很多行如何快速max?

=================9楼=====================

先用 for xml path  合成一个字符串 然后再套用那个函数

=================10楼=====================

这样的格式的效率都不会高的
只能考虑用拆分函数来解决
先拆分 再取最大值

=================11楼=====================

select max(cast( b.Name as int)) from (
select val=convert(xml,'<row>'+replace('1000,300,600,50,800,1200',',','</row><row>')+'</row>')) a 
Cross Apply (Select Name=T.c.value('./text()[1]', 'nvarchar(max)') from a.[val].nodes('//row') as T(c) ) b

=================12楼=====================

----------------------------------------------------------------
-- Author  :fredrickhu(小F,向高手学习)
-- Date    :2017-01-10 11:25:20
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
-- Feb 10 2012 19:39:15 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(25),[col2] varchar(26))
insert [tb]
select '100,200,210,300,240,500','1000,300,600,50,800,1200' union all
select '600,500,610,700,50,70,833','52,46,48,2150,823,183,1870' union all
select '200,400,63,80,500','152,416,438,1950,863'
--------------开始查询--------------------------
SELECT MAX(CAST(col2 AS int)) AS col2
FROM 
(
SELECT
    substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) AS col2
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=',') AS t
----------------结果----------------------------
/* 2150
*/

=================13楼=====================

引用 12 楼 fredrickhu 的回复:
----------------------------------------------------------------
-- Author  :fredrickhu(小F,向高手学习)
-- Date    :2017-01-10 11:25:20
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
-- Feb 10 2012 19:39:15 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(25),[col2] varchar(26))
insert [tb]
select '100,200,210,300,240,500','1000,300,600,50,800,1200' union all
select '600,500,610,700,50,70,833','52,46,48,2150,823,183,1870' union all
select '200,400,63,80,500','152,416,438,1950,863'
--------------开始查询--------------------------
SELECT MAX(CAST(col2 AS int)) AS col2
FROM 
(
SELECT
    substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) AS col2
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=',') AS t
----------------结果----------------------------
/* 2150
*/

大神!怎么会想到zhemaster..spt_values 这么神秘东东?介绍介绍吧。

=================14楼=====================

引用 12 楼 fredrickhu 的回复:
----------------------------------------------------------------
-- Author  :fredrickhu(小F,向高手学习)
-- Date    :2017-01-10 11:25:20
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
-- Feb 10 2012 19:39:15 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(25),[col2] varchar(26))
insert [tb]
select '100,200,210,300,240,500','1000,300,600,50,800,1200' union all
select '600,500,610,700,50,70,833','52,46,48,2150,823,183,1870' union all
select '200,400,63,80,500','152,416,438,1950,863'
--------------开始查询--------------------------
SELECT MAX(CAST(col2 AS int)) AS col2
FROM 
(
SELECT
    substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) AS col2
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=',') AS t
----------------结果----------------------------
/* 2150
*/

效率杠杠的,原先几分钟的,秒秒就出来了。牛掰!!!
大神再帮忙看看这个帖子类似的,不过更麻烦点的,代码咋写?
http://bbs.csdn.net/topics/392074260

=================15楼=====================

引用 13 楼 jycjyc 的回复:
Quote: 引用 12 楼 fredrickhu 的回复:

----------------------------------------------------------------
-- Author  :fredrickhu(小F,向高手学习)
-- Date    :2017-01-10 11:25:20
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
-- Feb 10 2012 19:39:15 
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col1] varchar(25),[col2] varchar(26))
insert [tb]
select '100,200,210,300,240,500','1000,300,600,50,800,1200' union all
select '600,500,610,700,50,70,833','52,46,48,2150,823,183,1870' union all
select '200,400,63,80,500','152,416,438,1950,863'
--------------开始查询--------------------------
SELECT MAX(CAST(col2 AS int)) AS col2
FROM 
(
SELECT
    substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) AS col2
from 
    Tb a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
     substring(','+a.COl2,b.number,1)=',') AS t
----------------结果----------------------------
/* 2150
*/

大神!怎么会想到zhemaster..spt_values 这么神秘东东?介绍介绍吧。


构造0-2047的数字经常用到这个系统表的

=================16楼=====================

看得晕  正好你的帖子也有那么多人回复了 换做我也是那样的解决办法。

=================17楼=====================

引用 16 楼 fredrickhu 的回复:
看得晕  正好你的帖子也有那么多人回复了 换做我也是那样的解决办法。

大神谦虚了,那个解决办法效率太慢,帮忙看看怎么改写快呢?多谢多谢!

=================18楼=====================

引用 16 楼 fredrickhu 的回复:
看得晕  正好你的帖子也有那么多人回复了 换做我也是那样的解决办法。


我记着,你有一个转成 XML 来实现的,语法简练的多。

如果您还有更好的解决方法,请在最下面评论中留下您的解决方法


  • 本文相关:
  • 站长必读,如何真正写好一篇原创文章
  • 互联网之路细嗅蔷薇 资深站长分享掘金之道
  • 企业站的站长的工作重心究竟有哪些
  • 浅析:行业门户网站的一些盈利模式
  • 3000IP的企业网站每天订单不到30个的苦恼
  • 网站想内外兼修?先学习提高网站可用性的6大原则
  • 浅谈网页设计中的简约之美
  • 网页改版实战:日本设计师如何彻底优化旅游网站?
  • 网页改版实战!日本设计师如何彻底优化招聘网站?
  • 2015年值得关注的21个网页设计趋势
  • 免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2015 www.zgxue.com All Rights Reserved