---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-04-24 08:19:43 -- 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) -- ---------------------------------------------------------------- --> 测试数据@a declare @a table([区域] nvarchar(4),[类型] nvarchar(2),[金额] int,[日期] datetime) insert @a select N'华北',N'买',3500,'2013.3.25' union all select N'西北',N'卖',2500,'2013.3.24' union all select N'东北',N'买',1200,'2013.3.25' union all select N'华北',N'卖',2000,'2013.3.23' union all select N'东北',N'卖',200,'2013.3.25' union all select N'华北',N'买',10000,'2013.3.29' --------------生成数据--------------------------select [区域],SUM([金额])[金额],count(CASE WHEN [类型]=N'买' THEN 1 ELSE NULL END )'买的次数', count(CASE WHEN [类型]=N'卖' THEN 1 ELSE NULL END )'卖的次数' from @a GROUP BY [区域] ----------------结果---------------------------- /* 区域 金额 买的次数 卖的次数 ---- ----------- ----------- ----------- 东北 1400 1 1 华北 15500 2 1 西北 2500 0 1 */
WITH CTE(Area,TYPE,Amt,Date) AS ( SELECT N'华北', N'买', 3500, '2013.3.25' UNION ALL SELECT N'西北', N'卖', 2500, '2013.3.24' UNION ALL SELECT N'东北', N'买', 1200, '2013.3.25' UNION ALL SELECT N'华北', N'卖', 2000 , '2013.3.23' UNION ALL SELECT N'东北', N'卖', 200 , '2013.3.25' UNION ALL SELECT N'华北', N'买', 10000, '2013.3.29' ) SELECT Area ,SUM(Amt) AS SumAmt ,SUM(CASE WHEN TYPE=N'买' THEN 1 ELSE 0 END) AS PutQty ,SUM(CASE WHEN TYPE=N'卖' THEN 1 ELSE 0 END) AS OutQty FROM CTE GROUP BY Area
--SQL2000--创建表 create table #record([区域] Nvarchar(10),[类型] char(2), [金额] decimal(18,2) ,[日期] varchar(10)) go --插入测试数据 insert #record select '华北', '买',3500 ,'2013.3.25' union all select '西北', '卖',2500, '2013.3.24' union all select '东北', '买',1200 , '2013.3.25' union all select '华北', '卖',2000 , '2013.3.23' union all select '东北', '卖',200 , '2013.3.25' union all select '华北', '买',10000, '2013.3.29' go select [区域],sum([金额])as [金额],sum(case when [类型]='买' then 1 else 0 end)[买的次数],sum(case when [类型]='卖' then 1 else 0 end)[卖的次数] from #record group by [区域] drop table #record
select [区域],sum([金额])as [金额],sum(case when [类型]='买' then 1 else 0 end)[买的次数],sum(case when [类型]='卖' then 1 else 0 end)[卖的次数] from #record group by [区域]
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-04-24 08:19:43
-- 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)
--
----------------------------------------------------------------
--> 测试数据@a
declare @a table([区域] nvarchar(4),[类型] nvarchar(2),[金额] int,[日期] datetime)
insert @a
select N'华北',N'买',3500,'2013.3.25' union all
select N'西北',N'卖',2500,'2013.3.24' union all
select N'东北',N'买',1200,'2013.3.25' union all
select N'华北',N'卖',2000,'2013.3.23' union all
select N'东北',N'卖',200,'2013.3.25' union all
select N'华北',N'买',10000,'2013.3.29'
--------------生成数据--------------------------select [区域],SUM([金额])[金额],count(CASE WHEN [类型]=N'买' THEN 1 ELSE NULL END )'买的次数',
count(CASE WHEN [类型]=N'卖' THEN 1 ELSE NULL END )'卖的次数'
from @a
GROUP BY [区域]
----------------结果----------------------------
/*
区域 金额 买的次数 卖的次数
---- ----------- ----------- -----------
东北 1400 1 1
华北 15500 2 1
西北 2500 0 1
*/
http://jimshu.blog.51cto.com/3171847/1376637
AS (
SELECT N'华北', N'买', 3500, '2013.3.25' UNION ALL
SELECT N'西北', N'卖', 2500, '2013.3.24' UNION ALL
SELECT N'东北', N'买', 1200, '2013.3.25' UNION ALL
SELECT N'华北', N'卖', 2000 , '2013.3.23' UNION ALL
SELECT N'东北', N'卖', 200 , '2013.3.25' UNION ALL
SELECT N'华北', N'买', 10000, '2013.3.29'
)
SELECT Area
,SUM(Amt) AS SumAmt
,SUM(CASE WHEN TYPE=N'买' THEN 1 ELSE 0 END) AS PutQty
,SUM(CASE WHEN TYPE=N'卖' THEN 1 ELSE 0 END) AS OutQty
FROM CTE
GROUP BY Area
create table #record([区域] Nvarchar(10),[类型] char(2), [金额] decimal(18,2) ,[日期] varchar(10))
go
--插入测试数据
insert #record select '华北', '买',3500 ,'2013.3.25' union all
select '西北', '卖',2500, '2013.3.24' union all
select '东北', '买',1200 , '2013.3.25' union all
select '华北', '卖',2000 , '2013.3.23' union all
select '东北', '卖',200 , '2013.3.25' union all
select '华北', '买',10000, '2013.3.29'
go
select [区域],sum([金额])as [金额],sum(case when [类型]='买' then 1 else 0 end)[买的次数],sum(case when [类型]='卖' then 1 else 0 end)[卖的次数] from #record group by [区域]
drop table #record