都在sqlserver数据库下操作,注意sqlserver与mysql和oracle的语法区别
用惯了mysql 和oracle,突然改用sqlserver,有诸多的不习惯,诸多的坑爹,好多的坑,一一跳过一遍才知道,头破血流呀!
1,建表
create table stuUnion
(
sid int identity primary key,
cid int,
id varchar(500)
)
2 插入数据
insert into stuUnion
select 1,'a' union
select 1,'b' union
select 2,'c' union
select 2,'d' union
select 3,'e' union
select 3,'f' union
select 3,'g'
3 标量函数(合并不同记录的相同字段)
create function b(@cid int)
returns varchar(500)
as
begin
declare @s varchar(500)
select @s=isnull(@s+'','')+rtrim(id)+',' from stuUnion where cid=@cid
return @s
end;
4 显示结果
select cid,dbo.b(cid) as id from stuUnion group by cid
5改造标量函数
注意点1 参数类型,int 和 String
2 where VID like '%'+@VID+'%'
create function c(@VID nvarchar)
returns varchar(500)
as
begin
declare @s varchar(500)
select @s=isnull(@s+'','')+rtrim(items)+' ' from cus_checks where VID like '%'+@VID+'%'
return @s
end;
select VID,dbo.c(VID) as 检查项目 from cus_checks group by VID
6修改字段
sp_rename 'cus_checks.check','items','column'
7不用标量函数进行查询取值
select VID,items=STUFF((select DISTINCT ' '+rtrim(items)+' ' from cus_checks where st.VID=VID for XML path('')),1,1,'') from cus_checks st group by VID
8 对表数据进行去重处理
slect * insert into abc from select DISTINCT VID,Items from cus_checks
9复制去重后的表
select * into aaa from cus_checks where 1=2
insert into aaa select DISTINCT * from cus_checks