表 table1字段1 字段2 字段3
A q 1
A q 2
A e 3
B r 4
B t 5
C y 6
C y 7
C u 8
C i 9
结果 字段一分组,字段2横向累加(不重复),字段3求和A q,e 6
B r.t 9
C y,u,i 30
A q 1
A q 2
A e 3
B r 4
B t 5
C y 6
C y 7
C u 8
C i 9
结果 字段一分组,字段2横向累加(不重复),字段3求和A q,e 6
B r.t 9
C y,u,i 30
解决方案 »
- asp链接sql server 2005报错 本机调试很好,上传至服务器就报错
- 如何将一个表中某字段所有值插入到另一个表中
- 怎样把'1,2,3'字符串作为int列传入存储过程?我想用SELECT .. IN (..)
- SQL Server 复制发布-采用事务日志,报当 IDENTITY_INSERT 设置为 OFF 时,不能为表 'Links' 中的标识列插入显式值。
- SS2000中,怎么把一个已设好主键的int列改为其它列有数据加入就自动递增,步长为1?
- 菜鸟一问(送分)
- 请教:一个更新触发器的问题
- 求救!一不小心酿成大祸!
- 紧急求助:sql server 7.0在2000 server下的问题??? --pbworm(pb菜鸟)
- 请调手 生产环境下 sql server 企业管理员不参点击新查询
- SQL判断 一个字段为空时,取出另一个字段数据。判断怎么写??????
- 利用时间查询问题!
select a.字段1,
stuff((select ','+字段2 from table1 where 字段1 = a.字段1 for xml path('')),1,1,''),
sum(isnull(a.字段3,0))
from table1 as a
group by a.字段1
drop table tb
Go
Create table tb([字段1] nvarchar(1),[字段2] nvarchar(1),[字段3] int)
Insert tb
select N'A',N'q',1 union all
select N'A',N'q',2 union all
select N'A',N'e',3 union all
select N'B',N'r',4 union all
select N'B',N't',5 union all
select N'C',N'y',6 union all
select N'C',N'y',7 union all
select N'C',N'u',8 union all
select N'C',N'i',9
Go
;with tt
as
(
Select [字段1],
[字段2]=case when exists(select 1
from tb
where [字段2]=t.[字段2] and [字段3]>t.[字段3])then '' else [字段2] end,
[字段3]
from tb t)
select 字段1,
字段2=stuff((select ','+字段2
from tt
where 字段1 =t.字段1
for xml path('') ),1,1,''),
sum(字段3)字段3
from tt t
group by 字段1
/*
字段1 字段2 字段3
---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
A ,q,e 6
B r,t 9
C ,y,u,i 30*/
if object_id('tempdb.dbo.#table1') is not null drop table #table1
create table #table1 (字段1 varchar(1),字段2 varchar(1),字段3 int)
insert into #table1
select 'A','q',1 union all
select 'A','q',2 union all
select 'A','e',3 union all
select 'B','r',4 union all
select 'B','t',5 union all
select 'C','y',6 union all
select 'C','y',7 union all
select 'C','u',8 union all
select 'C','i',9select * from #table1
select 字段1,字段2=(select 字段2 +',' from #table1 t where t.字段1=tt.字段1 for xml path('')),
sum(字段3) as 字段3
from #table1 tt group by 字段1
drop table table1
create table table1
(
字段1 varchar(10),
字段2 varchar(10),
字段3 int
)insert into table1
select 'A', 'q', 1
union all
select 'A', 'q', 2
union all
select 'A', 'e', 3
union all
select 'B', 'r', 4
union all
select 'B', 't', 5
union all
select 'C', 'y', 6
union all
select 'C', 'y', 7
union all
select 'C', 'u', 8
union all
select 'C', 'i', 9select a.字段1,
stuff((select distinct ','+字段2 from table1 where 字段1 = a.字段1 for xml path('')),1,1,''),
sum(isnull(a.字段3,0))
from table1 as a
group by a.字段1
呵呵!要不重复,那就改一下结果
A e,q 6
B r,t 9
C i,u,y 30
DROP TABLE [table1]
CREATE TABLE [table1]
(
字段1 varchar(100) NULL ,
字段2 varchar(100) NULL ,
字段3 int NULL
)
GO--插入测试数据
INSERT INTO [table1] (字段1,字段2,字段3)
SELECT 'A','q','1' UNION
SELECT 'A','q','2' UNION
SELECT 'A','e','3' UNION
SELECT 'B','r','4' UNION
SELECT 'B','t','5' UNION
SELECT 'C','y','6' UNION
SELECT 'C','y','7' UNION
SELECT 'C','u','8' UNION
SELECT 'C','i','9'
GOCREATE FUNCTION f_t(@value1 VARCHAR(100))
returns varchar(4000)
AS
BEGIN
declare @s varchar(4000)
set @s=''
select @s=@s+','+[字段2] from table1 where [字段1]=@value1
RETURN (stuff(@s,1,1,''))
END
GO--调用
SELECT 字段1,dbo.f_t(字段1),SUM(字段3) FROM table1 GROUP BY 字段1--结果
/*
A e,q,q 6
B r,t 9
C i,u,y,y 30
*/
DROP TABLE [table1]
CREATE TABLE [table1]
(
字段1 varchar(100) NULL ,
字段2 varchar(100) NULL ,
字段3 int NULL
)
GO--插入测试数据
INSERT INTO [table1] (字段1,字段2,字段3)
SELECT 'A','q','1' UNION
SELECT 'A','q','2' UNION
SELECT 'A','e','3' UNION
SELECT 'B','r','4' UNION
SELECT 'B','t','5' UNION
SELECT 'C','y','6' UNION
SELECT 'C','y','7' UNION
SELECT 'C','u','8' UNION
SELECT 'C','i','9'
GOCREATE FUNCTION f_t(@value1 VARCHAR(100))
returns varchar(4000)
AS
BEGIN
declare @s varchar(4000)
set @s=''
select @s=@s+','+ [字段2] from (SELECT DISTINCT 字段1,字段2 FROM table1) a where [字段1]=@value1
RETURN (stuff(@s,1,1,''))
END
GO--调用
SELECT 字段1,dbo.f_t(字段1),SUM(字段3) FROM table1 GROUP BY 字段1--结果
/*
A e,q 6
B r,t 9
C i,u,y 30
*/