---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-03-04 11:03:10 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([F1] int,[F2] varchar(2),[F3] int) insert [tb] select 1,'买',50 union all select 2,'买',50 union all select 1,'卖',50 union all select 1,'卖',100 union all select 2,'卖',100 union all select 3,'买',50 union all select 3,'买',50 union all select 3,'卖',50 --------------开始查询-------------------------- SELECT f1, CASE WHEN f3<0 THEN 1 ELSE 0 END AS f2, F3 FROM ( SELECT f1, SUM(CASE WHEN f2='买' THEN f3 ELSE -f3 END) AS f3 FROM TB GROUP BY f1)t ----------------结果---------------------------- /* f1 f2 F3 ----------- ----------- ----------- 1 1 -100 2 1 -50 3 0 50(3 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-04 11:03:10
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([F1] int,[F2] varchar(2),[F3] int)
insert [tb]
select 1,'买',50 union all
select 2,'买',50 union all
select 1,'卖',50 union all
select 1,'卖',100 union all
select 2,'卖',100 union all
select 3,'买',50 union all
select 3,'买',50 union all
select 3,'卖',50
--------------开始查询--------------------------
SELECT
f1,
CASE WHEN f3<0 THEN 1 ELSE 0 END AS f2,
F3
FROM
(
SELECT
f1,
SUM(CASE WHEN f2='买' THEN f3 ELSE -f3 END) AS f3
FROM
TB
GROUP BY
f1)t
----------------结果----------------------------
/* f1 f2 F3
----------- ----------- -----------
1 1 -100
2 1 -50
3 0 50(3 行受影响)*/