解决方案 »
- 在SQL 2005里附加数据库的方法
- sql server 发邮件
- 请教更新海量数据的语句如何优化?本语句已经运行7个小时了,还在运行
- 临时表访问出现的问题
- Access库导入到sqlserver2000中,表间关系与视图如何导入。。急,,在线等
- *********两个存储过程合并为一个,能实现么?****
- 再开50分,欢迎各位有经验的大哥指教~~讨教 数据表设计 的问题!!!!!!
- 救命高手快进来呀,去百度问了N个人都没找到答案!!!
- SQL SERVER安装错误
- sql server 6.5 中的临时表的大小,怎莫改,在线等待
- SQLServer中子查询速度很快,拼在一起就很慢的问题
- 对视图排序,去重,增加新行连续报错……求助……
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-16 10:15:21
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[H] nvarchar(4),[P] nvarchar(4),[C] nvarchar(4))
insert [huang]
select 1,'h1','p1','c1' union all
select 2,'h1','p1','c1' union all
select 3,'h1','p1','c1' union all
select 4,'h1','p1','c2' union all
select 5,'h1','p1','c2' union all
select 6,'h1','p1','c3' union all
select 7,'h1','p1','c4' union all
select 8,'h1','p1','c1' union all
select 9,'h1','p2','c1' union all
select 10,'h1','p2','c1' union all
select 11,'h1','p2','c2' union all
select 12,'h1','p3','c1' union all
select 13,'h1','p3','c1' union all
select 14,'h1','p1','c3' union all
select 15,'h2','p1','c1' union all
select 16,'h2','p1','c1' union all
select 17,'h2','p1','c4' union all
select 18,'h2','p1','c1' union all
select 19,'h3','p1','c4' union all
select 20,'h4','p1','c5'
--------------生成数据--------------------------select a.[C],a.[P],
stuff((select ','+[H] from huang b
where b.[C]=a.[C] and b.[P]=a.[P]
for xml path('')),1,1,'') 'H'
from [huang] a
group by a.[C],a.[P]
----------------结果----------------------------
/*
C P H
---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c1 p1 h1,h1,h1,h1,h2,h2,h2
c1 p2 h1,h1
c1 p3 h1,h1
c2 p1 h1,h1
c2 p2 h1
c3 p1 h1,h1
c4 p1 h1,h2,h3
c5 p1 h4
*/
----------------------------------------------------------------
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-16 10:15:21
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[H] nvarchar(4),[P] nvarchar(4),[C] nvarchar(4))
insert [huang]
select 1,'h1','p1','c1' union all
select 2,'h1','p1','c1' union all
select 3,'h1','p1','c1' union all
select 4,'h1','p1','c2' union all
select 5,'h1','p1','c2' union all
select 6,'h1','p1','c3' union all
select 7,'h1','p1','c4' union all
select 8,'h1','p1','c1' union all
select 9,'h1','p2','c1' union all
select 10,'h1','p2','c1' union all
select 11,'h1','p2','c2' union all
select 12,'h1','p3','c1' union all
select 13,'h1','p3','c1' union all
select 14,'h1','p1','c3' union all
select 15,'h2','p1','c1' union all
select 16,'h2','p1','c1' union all
select 17,'h2','p1','c4' union all
select 18,'h2','p1','c1' union all
select 19,'h3','p1','c4' union all
select 20,'h4','p1','c5'
--------------生成数据--------------------------
--2005写法
select a.[C],a.[P],
stuff((select ','+[H] from huang b
where b.[C]=a.[C] and b.[P]=a.[P]
for xml path('')),1,1,'') 'H'
from [huang] a
group by a.[C],a.[P]--2000写法
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 nvarchar(100),@col2 NVARCHAR(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+[H] from huang where [C]=@Col1 AND [P]=@col2
return @S
end
go
Select distinct [C],[P],H=dbo.F_Str([c],[p]) from huang
----------------结果----------------------------
/*
C P H
---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c1 p1 h1,h1,h1,h1,h2,h2,h2
c1 p2 h1,h1
c1 p3 h1,h1
c2 p1 h1,h1
c2 p2 h1
c3 p1 h1,h1
c4 p1 h1,h2,h3
c5 p1 h4(8 row(s) affected)C P H
---- ---- ----------------------------------------------------------------------------------------------------
c1 p1 h1,h1,h1,h1,h2,h2,h2
c1 p2 h1,h1
c1 p3 h1,h1
c2 p1 h1,h1
c2 p2 h1
c3 p1 h1,h1
c4 p1 h1,h2,h3
c5 p1 h4*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-16 10:15:21
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[H] nvarchar(4),[P] nvarchar(4),[C] nvarchar(4))
insert [huang]
select 1,'h1','p1','c1' union all
select 2,'h1','p1','c1' union all
select 3,'h1','p1','c1' union all
select 4,'h1','p1','c2' union all
select 5,'h1','p1','c2' union all
select 6,'h1','p1','c3' union all
select 7,'h1','p1','c4' union all
select 8,'h1','p1','c1' union all
select 9,'h1','p2','c1' union all
select 10,'h1','p2','c1' union all
select 11,'h1','p2','c2' union all
select 12,'h1','p3','c1' union all
select 13,'h1','p3','c1' union all
select 14,'h1','p1','c3' union all
select 15,'h2','p1','c1' union all
select 16,'h2','p1','c1' union all
select 17,'h2','p1','c4' union all
select 18,'h2','p1','c1' union all
select 19,'h3','p1','c4' union all
select 20,'h4','p1','c5'
--------------生成数据--------------------------
--2005写法
select a.[C],a.[P],
stuff((select ','+[H] from huang b
where b.[C]=a.[C] and b.[P]=a.[P]
for xml path('')),1,1,'') 'H'
from [huang] a
WHERE p='p1'
group by a.[C],a.[P]--2000写法
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 nvarchar(100),@col2 NVARCHAR(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+[H] from huang where [C]=@Col1 AND [P]=@col2
return @S
end
go
Select distinct [C],[P],H=dbo.F_Str([c],[p]) from huang WHERE p='p1'
----------------结果----------------------------
/*
C P H
---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c1 p1 h1,h1,h1,h1,h2,h2,h2
c2 p1 h1,h1
c3 p1 h1,h1
c4 p1 h1,h2,h3
c5 p1 h4(5 row(s) affected)C P H
---- ---- ----------------------------------------------------------------------------------------------------
c1 p1 h1,h1,h1,h1,h2,h2,h2
c2 p1 h1,h1
c3 p1 h1,h1
c4 p1 h1,h2,h3
c5 p1 h4
*/
我sql2012
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-16 10:15:21
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[H] nvarchar(4),[P] nvarchar(4),[C] nvarchar(4))
insert [huang]
select 1,'h1','p1','c1' union all
select 2,'h1','p1','c1' union all
select 3,'h1','p1','c1' union all
select 4,'h1','p1','c2' union all
select 5,'h1','p1','c2' union all
select 6,'h1','p1','c3' union all
select 7,'h1','p1','c4' union all
select 8,'h1','p1','c1' union all
select 9,'h1','p2','c1' union all
select 10,'h1','p2','c1' union all
select 11,'h1','p2','c2' union all
select 12,'h1','p3','c1' union all
select 13,'h1','p3','c1' union all
select 14,'h1','p1','c3' union all
select 15,'h2','p1','c1' union all
select 16,'h2','p1','c1' union all
select 17,'h2','p1','c4' union all
select 18,'h2','p1','c1' union all
select 19,'h3','p1','c4' union all
select 20,'h4','p1','c5'
--------------生成数据--------------------------
--2005写法
select a.[C],a.[P],
stuff((select ','+[H] from (SELECT DISTINCT h,p,c FROM huang WHERE p='p1') b
where b.[C]=a.[C] and b.[P]=a.[P]
for xml path('')),1,1,'') 'H'
from (SELECT DISTINCT h,p,c FROM huang WHERE p='p1') a
group by a.[C],a.[P]--2000写法
go
if object_id('F_Str') is not null
drop function F_Str
go
create function F_Str(@Col1 nvarchar(100),@col2 NVARCHAR(100))
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+[H] from (SELECT DISTINCT h,p,c FROM huang WHERE p='p1') huang where [C]=@Col1 AND [P]=@col2
return @S
end
go
Select distinct [C],[P],H=dbo.F_Str([c],[p]) FROM (SELECT DISTINCT h,p,c FROM huang WHERE p='p1') a
----------------结果----------------------------
/*
C P H
---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
c1 p1 h1,h2
c2 p1 h1
c3 p1 h1
c4 p1 h1,h2,h3
c5 p1 h4(5 row(s) affected)C P H
---- ---- ----------------------------------------------------------------------------------------------------
c1 p1 h1,h2
c2 p1 h1
c3 p1 h1
c4 p1 h1,h2,h3
c5 p1 h4
*/