select sum(金额) from 表名 where id in ('1','2','3') and dah ='b'
select sum(金额) from #tab where id in(1,2,3) and dah='b'
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-04-23 10:49:08 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) -- Jun 17 2011 00:54:03 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([id] int,[金额] numeric(2,1),[dah] varchar(1)) insert [huang] select 1,1.1,'b' union all select 2,2.2,'b' union all select 3,3.3,'b' --------------开始查询--------------------------SELECT STUFF(( SELECT ',' + CONVERT(VARCHAR(10),id) FROM [huang] b FOR XML PATH('') ), 1, 1, '') 'id',SUM([金额])[金额],[dah] FROM [huang] a WHERE [id] IN (1,2,3) GROUP BY [dah]----------------结果---------------------------- /* id 金额 dah ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ---- 1,2,3 */
create table #tab (id int,金额 float,dah varchar(50)) insert into #tab select 1, 1.1, 'b' union all select 2, 2.2, 'b' union all select 3, 3.3, 'b' select (select stuff((replace((replace((select ','+convert(varchar,id) from #tab for xml path('')),'<id>','')),'</id>','')),1,1,''))id, sum(金额)金额, dah from #tab group by dahid 金额 dah --------- ------------------------- ---------------------- 1,2,3 6.6 b(1 行受影响)
SELECT id=STUFF(( SELECT ','+convert(varchar,id) FROM t1 WHERE dah=a.dah FOR XML PATH('')),1,1,''),SUM(金额) AS 金额 ,dah FROM t1 a GROUP BY dah
select sum(金额) as summoney from ( select 金额,id =stuff((select ','+convert(varchar,id) from A where id in ('1','2','3') for xml path('')), 1, 1, '') from A where id in ('1','2','3') and dah ='b' )a where id='1,2,3' group by id
最终sqlselect sum(mony) as summoney,id,dah from ( select mony,dah,id =stuff((select ','+convert(varchar,id) from A where id in ('1','2','3') for xml path('')), 1, 1, '') from A where id in ('1','2','3') and dah ='b' )a group by id,dah
create table t5(id int,je decimal(5,1),dah varchar(10))insert into t5 select 1,1.1,'b' union all select 2,2.2,'b' union all select 3,3.3,'b' declare @t varchar(8000) select @t=isnull(@t,'')+','+convert(varchar(10),id) from t5 select id=stuff(@t,1,1,''),金额=sum(je),dah from t5 group by dah
SELECT GROUP_CONCAT(s.id),SUM(s.sal),s.dah FROM suma s GROUP BY s.dah;
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-23 10:49:08
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([id] int,[金额] numeric(2,1),[dah] varchar(1))
insert [huang]
select 1,1.1,'b' union all
select 2,2.2,'b' union all
select 3,3.3,'b'
--------------开始查询--------------------------SELECT STUFF(( SELECT ',' + CONVERT(VARCHAR(10),id)
FROM [huang] b
FOR
XML PATH('')
), 1, 1, '') 'id',SUM([金额])[金额],[dah]
FROM [huang] a
WHERE [id] IN (1,2,3)
GROUP BY [dah]----------------结果----------------------------
/*
id 金额 dah
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- ----
1,2,3
*/
(id int,金额 float,dah varchar(50))
insert into #tab
select 1, 1.1, 'b' union all
select 2, 2.2, 'b' union all
select 3, 3.3, 'b' select (select stuff((replace((replace((select ','+convert(varchar,id) from #tab for xml path('')),'<id>','')),'</id>','')),1,1,''))id,
sum(金额)金额,
dah
from #tab group by dahid 金额 dah
--------- ------------------------- ----------------------
1,2,3 6.6 b(1 行受影响)
SELECT id=STUFF((
SELECT ','+convert(varchar,id) FROM t1 WHERE dah=a.dah FOR XML PATH('')),1,1,''),SUM(金额) AS 金额 ,dah FROM t1 a GROUP BY dah
(
select 金额,id =stuff((select ','+convert(varchar,id) from A where id in ('1','2','3')
for xml path('')), 1, 1, '') from A where id in ('1','2','3') and dah ='b'
)a where id='1,2,3' group by id
(
select mony,dah,id =stuff((select ','+convert(varchar,id) from A where id in ('1','2','3')
for xml path('')), 1, 1, '') from A where id in ('1','2','3') and dah ='b'
)a group by id,dah
create table t5(id int,je decimal(5,1),dah varchar(10))insert into t5 select 1,1.1,'b'
union all
select 2,2.2,'b'
union all
select 3,3.3,'b'
declare @t varchar(8000)
select @t=isnull(@t,'')+','+convert(varchar(10),id) from t5
select id=stuff(@t,1,1,''),金额=sum(je),dah from t5 group by dah
SELECT GROUP_CONCAT(s.id),SUM(s.sal),s.dah FROM suma s
GROUP BY s.dah;