SQL2005

SQL2005

【交流】SQL 2005溢用之：合并列值

无论是在sql 2000， 还是在 sql 2005 中，都没有提供字符串的聚合函数， 所以， 当我们在处理下列要求时，会比较麻烦：

有表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 的和（字符串相加）

这个问题的一般处理方法是， 写一个聚合函数：
create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
   declare @r varchar(8000)
   set @r=''
   select @r=@r+','+value from tb where id=@id
   return stuff(@r,1,1,'')
end
go

-- 调用函数
select id, values=dbo.f_str(id) from tb group by id

这样的问题是，函数不通用，必须为每个要处理的表编写相应的处理函数
在SQL2005中，这个问题的更好解决办法是写一个CLR函数，用于实现字符串的聚合，这样可以解决通用性的问题了。

 

而在我下面要实现的, 是只用一条SQL语句来完成这个功能
(绝对是一条, 不会是用EXEC()取巧的那种)

-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

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

(2 行受影响)
--*/

 

SQL 2005新增了xml数据类型， 而且xml数据类型可以方便的与字符类型之间做转换，上面的方法只是巧妙地利用了这一点，结合字符串的一些处理函数就出来结果了

-- 下面这个示例也是合并字符串的， 以系统表为列， 合并两列

SELECT *
FROM(
SELECT DISTINCT
type
FROM sys.objects
)O
OUTER APPLY(
SELECT names = STUFF(REPLACE(REPLACE(
(
SELECT object_id, name FROM sys.objects N
WHERE type = O.type
FOR XML AUTO
), '<N ', ','), '/>', ''), 1, 1, '')
)N

发表于 @ 2006年06月05日 4:37 PM | 评论 (1)

【交流】SQL 2005溢用之：分拆列值

有表tb, 如下:
id          values
----------- -----------
1           aa,bb
2           aaa,bbb,ccc

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

之前的老办法, 一般是使用临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(',', A.[values] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[values], B.id, 1) = ','
DROP TABLE #

-- 在sql 2005中, 借用xml, 可以简单地一句来实现这个功能

-- 示例数据
DECLARE @t TABLE(id int, [values] varchar(100))
INSERT @t SELECT 1, 'aa,bb'
UNION ALL SELECT 2, 'aaa,bbb,ccc'

-- 查询处理
SELECT A.id, B.value
FROM(
SELECT id, [values] = CONVERT(xml,
'<root><v>' + REPLACE([values], ',', '</v><v>') + '</v></root>')
FROM @t
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)')
FROM A.[values].nodes('/root/v') N(v)
)B

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

(5 行受影响)
--*/

 

发表于 @ 2006年06月05日 4:36 PM | 评论 (1)

在SQL中有没有办法，不通过游标，把一个列的字符全部连起来

    摘要：在SQL中有没有办法，不通过游标，把一个列的字符全部连起来    （全文共864字）——点击 此处阅读全文

发表于 @ 2006年06月05日 4:26 PM | 评论 (1)

SQL Server 2005 中的同义词

=============创建同义词

可以为下列对象类型创建同义词：

 

程序集 (CLR) 存储过程	程序集 (CLR) 表值函数
程序集 (CLR) 标量函数	程序集聚合 (CLR) 聚合函数
复制筛选过程	扩展存储过程
SQL 标量函数	SQL 表值函数
SQL 内联表值函数	SQL 存储过程
视图	表1（用户定义）

^{1 包括本地和全局临时表}

不支持使用函数基对象的四部分名称。

在动态 SQL 中可以创建、删除和引用同义词。

在不引用或者不需要同义词后,可以删除同义词.

 --A:为本地对象创建同义词
==================================================================
CREATE SYNONYM MYBOM
FOR TEST.DBO.BOM;
GO
SELECT *
FROM MYBOM
WHERE ID < 5;
GO

--B:为远程对象创建同义词
==================================================================
--创建链接服务器
exec sp_addlinkedserver  'srv_lnk','','SQLOLEDB','远程服务器名或ip地址'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用户名','密码'
GO
USE databasename
CREATE SYNONYM 同以词 FOR srv_lnk.远程数据库.dbo.远程表;
GO
select * from 同以词

--以后不再使用时删除链接服务器
exec sp_dropserver 'srv_lnk','droplogins'

 

========删除同义词===================================================
对同义词的引用不受架构限制；因此，可随时删除同义词。只有在运行时才能发现对已删除的同义词的引用。在动态 SQL 中可以创建、删除和引用同义词。

DROP SYNONYM 同义词名称

发表于 @ 2006年05月24日 2:42 PM | 评论 (1)

SQL Server 2005 处理交叉表

--原贴:http://community.csdn.net/Expert/topic/4617/4617995.xml?temp=4.882449E-02
日期            时间   售货金额
2006-01-02       早上    50
2006-01-02       中午    20
2006-01-02       晚上    30
2006-01-02       零晨    40
2006-01-03       早上    40
2006-01-03       中午    60
2006-01-03       晚上    50
2006-01-03       零晨    50
2006-01-04       早上    80
2006-01-04       中午    60
2006-01-04       晚上    20
2006-01-04       零晨    40
...........................
............................
...........................
.........
能否用行转列的方式在进行数据查询中将上面数据的查询结果显示为:

日期        早上   中午  晚上   零晨   金额小计
2006-01-02   50     20    30      40     140
2006-01-03   40     60    50      50     200
2006-01-04   80     60    30      20     190
..........
..........

--SQL 20005中的处理方式:

--测试环境
Create table T(日期 datetime,时间 varchar(20),售货金额 int)
insert into T select '2006-01-02','早上',50
union all select '2006-01-02','中午',20
union all select '2006-01-02','晚上',30
union all select '2006-01-02','零晨',40
union all select '2006-01-03','早上',40
union all select '2006-01-03','中午',60
union all select '2006-01-03','晚上',50
union all select '2006-01-03','零晨',50
union all select '2006-01-04','早上',80
union all select '2006-01-04','中午',60
union all select '2006-01-04','晚上',20
union all select '2006-01-04','零晨',40
--查询
select * ,金额小计=(select sum(售货金额) from T where 日期=PT.日期 ) from T as TAB
PIVOT
( max(售货金额)
  for 时间 in ([早上],[中午],[晚上],[零晨])
) as PT
--结果
/*
日期                      早上          中午          晚上          零晨          金额小计
----------------------- ----------- ----------- ----------- ----------- -----------
2006-01-02 00:00:00.000 50          20          30          40          140
2006-01-03 00:00:00.000 40          60          50          50          200
2006-01-04 00:00:00.000 80          60          20          40          200

(3 行受影响)
*/
--删除测试环境
Drop Table T

 ---动态SQL

DECLARE @S VARCHAR(MAX)
SET @S=''
SELECT @S=@S+',['+时间+']' FROM T
  GROUP BY 时间
SET @S=STUFF(@S,1,1,'')
EXEC('
select 日期,'+@S+',金额小计=(select sum(售货金额) from T where 日期=PT.日期 ) from T as TAB
PIVOT
( max(售货金额)
  for 时间 in ('+@S+')
) as PT
')

发表于 @ 2006年05月24日 2:40 PM | 评论 (1)

列转行

怎么把一条记录拆分成几条记录？
User     No.         A           B            C
1        1           21          34           24
1        2           42          25           16

RESULT:

User     No.        Type       Num
1        1          A          21
1        1          B          34
1        1          C          24
1        2          A          42
1        2          B          25
1        2          C          16

不好意思，没有多少分了，只好给20分

 

---sql server 2005
declare @t table(usser int ,no int ,a int,b int, c int)
insert into @t select 1,1,21,34,24
union all select 1,2,42,25,16

SELECT usser,no,Type=attribute, Num=value
FROM @t
  UNPIVOT
  (
    value FOR attribute IN([a], [b], [c])
  ) AS UPV
       
--结果
/*

usser   no       Type      num
----   ---      --------  --------
1 1 a 21
1 1 b 34
1 1 c 24
1 2 a 42
1 2 b 25
1 2 c 16
*/

发表于 @ 2006年05月19日 1:13 PM | 评论 (2)