求SQL 语句

Question

本帖最后由 HW_2X_VC 于 2011-03-12 13:33:45 编辑

Answer 1

if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+名称 from tb where 编号=@Col1
    return @S
end
goSelect distinct 编号,名称=dbo.F_Str(编号) from tb

Answer 2

create table test1
(equimentid int,errocode varchar(10))insert into test1(equimentid,errocode) values(1,'123')
insert into test1(equimentid,errocode) values(1,'43')
insert into test1(equimentid,errocode) values(1,'567')
insert into test1(equimentid,errocode) values(2,'123')
insert into test1(equimentid,errocode) values(2,'563')
insert into test1(equimentid,errocode) values(3,'63')create function fn_showstring(@id int)
returns varchar(100)
as
begin
  declare @sql varchar(100)
  set @sql=''
  select @sql=@sql+errocode+',' from test1 where equimentid=@id
  return @sql
endselect equimentid,dbo.fn_showstring(equimentid) from test1 group by equimentid

Answer 3

create table tb(编号 varchar(10),名称 nvarchar(10))
insert into tb select '001','张三'
insert into tb select '001','李四'
insert into tb select '001','王五'
insert into tb select '002','张飞'
insert into tb select '002','刘备'
go
CREATE FUNCTION lj
(@bh varchar(10)) 
returns nvarchar(100) 
as   
begin 
declare @str nvarchar(100)
select @str=isnull(@str+',','')+名称 from tb where 编号=@bh
return @str  --alue+left(@lst,len(@lst)-1) 
end 
go
select 编号,dbo.lj(编号)名称 from tb group by 编号
go
drop function dbo.lj
drop table tb
/*
编号         名称
---------- ----------------------------------------------------------------------------------------------------
001        张三,李四,王五
002        张飞,刘备(2 行受影响)*/

Answer 4

ALTER   function [dbo].[汇总叠加]
(
    @表名 NVARCHAR(200),
    @Where NVARCHAR(200),
    @叠加字段 NVARCHAR(2000)
)
RETURNS NVARCHAR(2000)
AS
BEGIN
    declare @cSql NVARCHAR(2000)
    declare @return_status varchar(2000) 
    declare @lnId int ,@lnCount int,@c叠加值 varchar(2000)
    set @c叠加值=''
    --set @表名='tb_承包合同'
    --set @Where='合同编号=''20110043'''
   -- set @叠加字段='外协单位名称' 
     
    set @cSql='declare @c叠加值 varchar(2000) 
  SET @c叠加值=''''  
          select  @c叠加值=@c叠加值+case when isnull(@c叠加值,'''')='''' then '''' else '','' end +' + @叠加字段 +' from '+@表名+'  where '+@Where+
          CHAR(13)+'set @return_status=@c叠加值'           
          execute   sp_executesql   @cSql,N'@return_status varchar(100)   out',@return_status   out     
          set @c叠加值=@return_status
  RETURN ISNULL(@c叠加值,'')
ENDprint [dbo].[汇总叠加]('tb_承包合同','合同编号=''20110043''','外协单位名称')服务器: 消息 557，级别 16，状态 2，过程 汇总叠加，行 22
只有函数和扩展存储过程才能从函数内部执行。应该怎么解决这个动态的问题

Answer 5

2000 除了函数 还能使用临时表 游标等手段 如下：--3.3.1 使用游标法进行字符串合并处理的示例。
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3--合并处理
--定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
IF @col1=@col1_old
SELECT @s=@s+','+CAST(@col2 as varchar)
ELSE
BEGIN
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
END
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO
/*==============================================*/
--3.3.2 使用用户定义函数，配合SELECT处理完成字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
GO/*==============================================*/
--3.3.3 使用临时表实现字符串合并处理的示例
--处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3--合并处理
SELECT col1,col2=CAST(col2 as varchar(100)) 
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET 
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
@col1=col1,
col2=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--*/
--删除测试
DROP TABLE tb,#t
GO

Answer 6

/****************************************************************************************************************************************************** 
合并分拆表数据 整理人：中国风(Roy) 日期:2008.06.06 
******************************************************************************************************************************************************/ --> --> (Roy)生成測試數據 if not object_id('Tab') is null 
    drop table Tab 
Go 
Create table Tab([Col1] int,[Col2] nvarchar(1)) 
Insert Tab 
select 1,N'a' union all 
select 1,N'b' union all 
select 1,N'c' union all 
select 2,N'd' union all 
select 2,N'e' union all 
select 3,N'f' 
Go 合并表: SQL2000用函数: go 
if object_id('F_Str') is not null 
    drop function F_Str 
go 
create function F_Str(@Col1 int) 
returns nvarchar(100) 
as 
begin 
    declare @S nvarchar(100) 
    select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1 
    return @S 
end 
go 
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab go SQL2005用XML: 方法1: select 
    a.Col1,Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'') 
from 
    (select distinct COl1 from Tab) a 
Cross apply 
    (select COl2=(select N','+Col2 from Tab where Col1=a.COl1 For XML PATH(''), ROOT('R'), TYPE))b 方法2: select 
    a.Col1,COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44)) 
from 
    (select distinct COl1 from Tab) a 
cross apply 
    (select Col2=(select COl2 from Tab  where COl1=a.COl1 FOR XML AUTO, TYPE) 
                .query(' <Tab> 
                {for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")} 
                {concat("",string(/Tab[last()]/@COl2))} 
                </Tab>') 
                )b SQL05用CTE: ;with roy as(select Col1,Col2,row=row_number()over(partition by COl1 order by COl1) from Tab) 
,Roy2 as 
(select COl1,cast(COl2 as nvarchar(100))COl2,row from Roy where row=1 
union all 
select a.Col1,cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row from Roy a join Roy2 b on a.COl1=b.COl1 and a.row=b.row+1) 
select Col1,Col2 from Roy2 a where row=(select max(row) from roy where Col1=a.COl1) order by Col1 option (MAXRECURSION 0) 
生成结果: 
/* 
Col1        COl2 
----------- ------------ 
1          a,b,c 
2          d,e 
3          f (3 行受影响) 
*/ 
--参考
拆分表:--> --> (Roy)生成測試數據
 
if not object_id('Tab') is null
    drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
    drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select 
    a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from 
    Tab a,#Num b
where
    charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
    a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) 
from 
    Tab 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)=','
SQL2005用Xml:select 
    a.COl1,b.Col2
from 
    (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
    (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as 
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1        COl2
----------- -----
1           a
1           b
1           c
2           d
2           e
3           f
*/

Answer 7

use tempdb;/*
create table test
(
[编号] nvarchar(10) not null,
[名称] nvarchar(10) not null
);
insert into test([编号],[名称]) values('001','张三');
insert into test([编号],[名称]) values('001','李四');
insert into test([编号],[名称]) values('001','王五');
insert into test([编号],[名称]) values('002','张飞');
insert into test([编号],[名称]) values('002','刘备');
*/
----SQL Server 2000
create function fn_ShowThisName(@id nvarchar(10))
returns nvarchar(100)
as
begin
  declare @sql nvarchar(100)
  set @sql=''
  select @sql=@sql+[名称]+',' from test where [编号]=@id
  return @sql
end
select [编号],dbo.fn_ShowThisName(编号) as [名称] 
from test group by [编号];--SQL Server 2005及其以上
select t1.[编号],
stuff((select ',' + ltrim([名称]) from test as t2 where t1.[编号]=t2.[编号] for xml path('')), 1, 1, '') as [名称]
from test as t1
group by t1.[编号];