队列 转换 合并 分拆

2019-04-01 19:29栏目:程序人生


  1. 旧的缓解措施(在sql server 两千中只可以用函数化解。) --============================================================================= create table tb(id int, value varchar(十)) insert into tb values(1, 'aa') insert into tb values(一, 'bb') insert into tb values(贰, 'aaa') insert into tb values(2, 'bbb') insert into tb values(2, 'ccc') go --壹. 创设处理函数 CREATE FUNCTION dbo.f_strUnite(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @str varchar(8000) SET @str = '' SELECT @str = @str ','

李四 74   84   94

 一 --分拆列值 
 2 --原著:邹建 
 三 --改编:爱新觉罗.毓华(拾八年风雨,守得冰山雪水华开) 二零零六-1②-1陆 湖南阿布扎比
 4 
 5 --有表tb, 如下: 
 6 --id value 
 7 ------------- ----------- 
 8 --1 aa,bb 
 9 --2 aaa,bbb,ccc 
十 --欲按id,分拆value列, 分拆后结果如下: 
11 --id value 
12 ------------- -------- 
13 --1 aa 
14 --1 bb 
15 --2 aaa 
16 --2 bbb 
17 --2 ccc 
18 
1玖 --一. 旧的消除措施(sql server 3000) 
20 SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
21 
22 SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values]   ',', B.id) - B.id) 
23 FROM tb A, # B 
24 WHERE SUBSTRING(','   A.[values], B.id, 1) = ','
25 
26 DROP TABLE #
27 
2捌 --二. 新的消除方法(sql server 200五) 
29 create table tb(id int,value varchar(30)) 
30 insert into tb values(1,'aa,bb') 
31 insert into tb values(2,'aaa,bbb,ccc') 
32 go 
33 SELECT A.id, B.value 
34 FROM( 
35 SELECT id, [value] = CONVERT(xml,' <root> <v>'   REPLACE([value], ',', ' </v> <v>')   ' </v> </root>') FROM tb 
36 )A 
37 OUTER APPLY( 
38 SELECT value = N.v.value('.', 'varchar(100)') FROM A.[ca888会员登录 ,value].nodes('/root/v') N(v) 
39 )B
40 
41 DROP TABLE tb
42 
43 /* 
44 id value 
45 ----------- ------------------------------ 
46 1 aa 
47 1 bb 
48 2 aaa 
49 2 bbb 
50 2 ccc
51 
5贰 (伍 行受影响)
53 */

1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value

联合列值 --******************************************************************************************* 表结构,数据如下: id value ----- ------ 一 aa 一 bb 2 aaa 2 bbb 贰 ccc 需求获得结果: id values ------ ----------- 一 aa,bb 贰 aaa,bbb,ccc 即:group by id, 求 value 的谷青阳和(字符串相加)

张三 总分   250.00

*/

select*from
(
select 姓名 as 姓名 , 课程 ='语文' , 分数 = 语文 from tb
unionall
select 姓名 as 姓名 , 课程 ='数学' , 分数 = 数学 from tb
unionall
select 姓名 as 姓名 , 课程 ='物理' , 分数 = 物理 from tb
unionall
select 姓名 as 姓名 , 课程 ='平均分' , 分数 =cast((语文

  • 数学 物理)*1.0/3asdecimal(18,2)) from tb
    unionall
    select 姓名 as 姓名 , 课程 ='总分' , 分数 = 语文 数学
  • 物理 from tb
    ) t
    orderby 姓名 , case 课程 when'语文'then1when'数学'then2when'物理'then3when'平均分'then4when'总分'then5end

droptable tb

 

 

 

/*表结构,数据如下: 
id    value 
----- ------ 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 

内需取得结果: 
id    values 
------ ----------- 
1      aa,bb 
2      aaa,bbb,ccc 
即:group by id, 求 value 的和(字符串相加) */

  1. 旧的缓解方式(在sql server

    三千中不得不用函数消除。) 

    create table tb(id int, value varchar(10)) 
    insert into tb values(1, 'aa') 
    insert into tb values(1, 'bb') 
    insert into tb values(2, 'aaa') 
    insert into tb values(2, 'bbb') 
    insert into tb values(2, 'ccc') 
    go 
    --1. 创立处理函数
    CREATE FUNCTION dbo.f_strUnite(@id int) 
    RETURNS varchar(8000) 
    AS 
    BEGIN 
        DECLARE @str varchar(8000) 
        SET @str = '' 
        SELECT @str = @str   ','  value FROM tb WHERE id=@id 
        RETURN STUFF(@str, 1, 1, '') 
    END 
    GO 
    -- 调用函数
    SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id 
    drop table tb 
    drop function dbo.f_strUnite 
    go
    /* 
    id          value      
    ----------- ----------- 
    1          aa,bb 
    2          aaa,bbb,ccc 
    (所影响的行数为 二 行) 

    */ 

  2. 新的化解办法(在sql server 200第55中学用OUTE汉兰达APPLY等化解。) 
    create table tb(id int, value varchar(10)) 
    insert into tb values(1, 'aa') 
    insert into tb values(1, 'bb') 
    insert into tb values(2, 'aaa') 
    insert into tb values(2, 'bbb') 
    insert into tb values(2, 'ccc') 
    go 
    -- 查询处理
    SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( 
            SELECT [values]= STUFF(REPLACE(REPLACE( 
                ( 
                    SELECT value FROM tb N 
                    WHERE id = A.id 
                    FOR XML AUTO 
                ), ' <N value="', ','), '"/>', ''), 1, 1, '') 
    )N 
    drop table tb 

/* 
id          values 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 

(二 行受影响) 
*/ 

--SQL二零零五中的方法二
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
select id, [values]=stuff((select ',' [value] from tb t where id=tb.id 
for xml path('')), 1, 1, '') 
from tb 
group by id 
/* 
id          values 
----------- -------------------- 
1          aa,bb 
2          aaa,bbb,ccc  
*/ 

drop table tb 

 

 

 

 

/*有表tb, 如下: 
id          value 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
----------- -------- 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc */

  1. 旧的化解方法(sql server 3000)  
    select a.id, substring(a.[value], b.number, charindex(',', a.[value]   ',', b.number) - b.number) 
    FROM tb a, master..spt_values  b 
    WHERE b.type='p' and substring(','  a.[value],b.number, 1) = ',' 

  2. 新的消除措施(sql server 二零零五) 
    create table tb(id int,value varchar(30)) 
    insert into tb values(1,'aa,bb') 
    insert into tb values(2,'aaa,bbb,ccc') 
    go 
    SELECT a.id, b.value 
    FROM( 
        SELECT id, [value] = CONVERT(xml,' <root> <v>'   REPLaCE([value], ',', ' </v> <v>')  ' </v> </root>') FROM tb 
    )a 
    OUTER aPPLY( 
        SELECT value = N.v.value('.', 'varchar(100)') FROM a.[value].nodes('/root/v') N(v) 
    )b 

DROP TabLE tb 

/* 
id          value 
----------- ------------------------------ 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 

(伍 行受影响) 
*/

--方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(',',[value] ',')-1) as nvarchar(100)),Split=cast(stuff([value] ',',1,charindex(',',[value] ','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)

  • value FROM tb WHERE id=@id RETU翼虎N STUFF(@str, 一, 1, '') END GO -- 调用函数 SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id drop table tb drop function dbo.f_strUnite go /* id value ----------- ----------- ① aa,bb 二 aaa,bbb,ccc (所影响的行数为 2行) */ --=================================================================================== 2. 新绿业电脑高校的解决办法(在sql server 二零零五中用OUTE锐界 APPLY等消除。) create table tb(id int, value varchar(10)) insert into tb values(1, 'aa') insert into tb values(1, 'bb') insert into tb values(2, 'aaa') insert into tb values(二, 'bbb') insert into tb values(贰, 'ccc') go -- 查询处理 SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM tb N WHERE id = A.id FOR XML AUTO ), ' <N value="', ','), '"/>', ''), 1, 1, '') )N drop table tb /* id values ----------- ----------- 壹 aa,bb 二aaa,bbb,ccc (2 行受影响) */ --SQL二〇〇七中的方法2 create table tb(id int, value varchar(10)) insert into tb values(一, 'aa') insert into tb values(壹, 'bb') insert into tb values(二, 'aaa') insert into tb values(贰, 'bbb') insert into tb values(2, 'ccc') go select id, [values]=stuff((select ',' [value] from tb t where id=tb.id for xml path('')), 1, 1, '') from tb group by id /* id values ----------- -------------------- 1 aa,bb 2 aaa,bbb,ccc (2 row(s) affected) */ drop table tb /* 标题:分拆列值1作者:爱新觉罗.毓华(拾八年风霜,守得新秀传说冰山雪水华开) 时间:2010-11-20 地方:福建尼科西亚 描述 有表tb, 如下: id value ----------- ----------- 一 aa,bb 2 aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id value ----------- -------- 一 aa 一 bb 二 aaa 二 bbb 二 ccc */ --1. 旧的缓解办法(sql server 3000) SELECT TOP 柒仟 id = IDENTITY(int, 1, 壹) INTO # FROM syscolumns a, syscolumns b SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value]
  • ',', B.id)
  • B.id) FROM tb A, # B WHERE SUBSTRING(','
  • A.[value], B.id, 1) = ',' DROP TABLE # --2. 新永生的消除办法(sql server 2005) create table tb(id int,value varchar(30)) insert into tb values(一,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go SELECT A.id, B.value FROM( SELECT id, [value] = CONVERT(xml,'<root><v>'
  • REPLACE([value], ',', '</v><v>')
  • '</v></root>') FROM tb )A OUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) )B DROP TABLE tb /* id value ----------- ------------------------------ 1 aa 1 bb 贰 aaa 二 bbb 二 ccc (五 行受影响) */

标题:假如有张学生战绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想成为(获得如下结果):
姓名 语文 数学 物理

1 aa
1 bb
2 aaa
2 bbb
2 ccc

SQL code

张三 物理 93

*/

createtable tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insertinto tb values('张三',74,83,93)
insertinto tb values('李四',74,84,94)
go

--SQL SERVER 2000 静态SQL。
select*from
(
select 姓名 , 课程 ='语文' , 分数 = 语文 from tb
unionall
select 姓名 , 课程 ='数学' , 分数 = 数学 from tb
unionall
select 姓名 , 课程 ='物理' , 分数 = 物理 from tb
) t
orderby 姓名 , case 课程 when'语文'then1when'数学'then2when'物理'then3end

--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql ' union all ' , '' ) ' select 姓名 , [课程] = ' quotename(Name , '''') ' , [分数] = ' quotename(Name) ' from tb'
from syscolumns
where name! = N'姓名'and ID =object_id('tb') --表名tb,不带有列名叫现名的任何列
orderby colid asc
exec(@sql ' order by 姓名 ')

--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL,同SQL SERVER 2000 动态SQL。


/*
难点:在上述的结果上加个平均分,总分,获得如下结果:
姓名 课程   分数


李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33


/* 1 */
declare @sql varchar(8000)
set @sql = 'select ''总量'' as [时间] '
select @sql = @sql   ' , sum(case 时间 when '   rtrim(时间)   ' then 总量 end) ['   rtrim(时间)   ']'
from (
select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)

版权声明:本文由ca888发布于程序人生,转载请注明出处:队列 转换 合并 分拆