SQL code

/* 4 */
declare @s nvarchar(4000)
select  @s=isnull(@s+’,’,”)+quotename([时间]) from (select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)) t group by[时间] ORDER BY 时间
exec(‘select * from (select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)  
 )t pivot (max([总量]) for [时间] in(‘+@s+’))b’)

 1 –分拆列值 
 2 –原著:邹建 
 3 –改编:爱新觉罗.毓华(十六年风霜,守得冰山雪水华开) 二零零七-12-16 广西深圳
 4 
 5 –有表tb, 如下: 
 6 –id value 
 7 ————- ———– 
 8 –1 aa,bb 
 9 –2 aaa,bbb,ccc 
10 –欲按id,分拆value列, 分拆后结果如下: 
11 –id value 
12 ————- ——– 
13 –1 aa 
14 –1 bb 
15 –2 aaa 
16 –2 bbb 
17 –2 ccc 
18 
19 –1. 旧的缓和方法(sql server 三千) 
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 
28 –2. 新的缓慢解决情势(sql server 2006) 
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.[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 
52 (5 行受影响)
53 */

有表tb, 如下:
id value

联合列值 –******************************************************************************************* 表结构,数据如下: 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 的谷青阳和(字符串相加)

李四 74   84   94

1 aa
1 bb
2 aaa
2 bbb
2 ccc

  • value FROM tb
    WHERE id=@id
    RETU途乐N 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
    行) */ –=================================================================================== 2. 新绿业Computer学校的化解方法(在sql server
    2006中用OUTE路虎极光 应用程式LY等化解。) 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 (2 行受影响) */
    –SQL二零零六中的方法2 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 (2 row(s)
    affected) */ drop
    table tb /*
    标题:分拆列值1
    小编:爱新觉罗.毓华(磅lb年风雨,守得将军神话冰山雪菡萏开)
    时间:二零一零-11-20 地点:黑龙江日内瓦 描述 有表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
    TOP 8000 id =
    IDENTITY(int,
    1, 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 二〇〇七)
    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 (5 行受影响)
    */

 
/*
时间    9    10    11    12    13    14    15    16    17    18
总量    187    369    340    56    374    381    307    410    516    73
*/

1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
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 +
    ‘,’

/* 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)

–方法1.使用xml完成
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

张三 物理 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

admin

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注