现在又表t
c1,c2,c3,c4
1 1 2, 1
1 1 -2 1
1 2 3 1
现在要group by c1,c2后取sum(c3)>0的列
也就是
1 2 3 1
1 null 3 1
null null 3 1
我现在是这样写的
select c1,c2,sum(c3),sum(c4)
from t
group by c1,c2
having sum(c3)>0
结果确实1 2 3 1
1 null 3 3
1 null 3 3
请各位大虾赐教这个sql改如何写
c1,c2,c3,c4
1 1 2, 1
1 1 -2 1
1 2 3 1
现在要group by c1,c2后取sum(c3)>0的列
也就是
1 2 3 1
1 null 3 1
null null 3 1
我现在是这样写的
select c1,c2,sum(c3),sum(c4)
from t
group by c1,c2
having sum(c3)>0
结果确实1 2 3 1
1 null 3 3
1 null 3 3
请各位大虾赐教这个sql改如何写
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 13:27:02
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([c1] int,[c2] int,[c3] int,[c4] int)
insert #TB
select 1,1,2,1 union all
select 1,1,-2,1 union all
select 1,2,3,1
--------------开始查询--------------------------
select c1,c2,sum(c3),sum(c4)
from #TB
group by c1,c2
having sum(c3)>0
----------------结果----------------------------
/* (所影响的行数为 3 行)c1 c2
----------- ----------- ----------- -----------
1 2 3 1(所影响的行数为 1 行)
*/楼主结果有问题
1 1 2, 1
1 1 -2 1
1 2 3 1
现在要group by c1,c2后取sum(c3)>0的列
也就是
1 2 3 1
1 null 3 1
null null 3 1
----------\
怎么得到这个结果的?规律呢?不是你说的group by c1,c2后取sum(c3)>0的列 吧
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 13:27:02
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([c1] int,[c2] int,[c3] int,[c4] int)
insert #TB
select 1,1,2,1 union all
select 1,1,-2,1 union all
select 1,2,3,1
--------------开始查询--------------------------
select c1,c2,sum(c3),min(c4)
from #TB
group by c1,c2 with rollup
having sum(c3)>0
(所影响的行数为 3 行)c1 c2
----------- ----------- ----------- -----------
1 2 3 1
1 NULL 3 1
NULL NULL 3 1(所影响的行数为 3 行)楼主WITH ROLLUP 是统计,按C1分组统计,最后再全部统计
-- Author :SQL77(只为思齐老)
-- Date :2010-02-26 13:27:02
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([c1] int,[c2] int,[c3] int,[c4] int)
insert #TB
select 1,1,2,1 union all
select 1,1,-2,1 union all
select 1,2,3,1
--------------开始查询--------------------------
select c1,c2,sum(c3),sum(c4)
from (
select c1,c2,sum(c3)c3,sum(c4)c4
from #TB
group by c1,c2
having sum(c3)>0)as t
group by c1,c2 with rollup(所影响的行数为 3 行)c1 c2
----------- ----------- ----------- -----------
1 2 3 1
1 NULL 3 1
NULL NULL 3 1(所影响的行数为 3 行)先查出来再统计还差不多
例子:
if object_id('a') is not null
drop table a
create table a
(
bm varchar(20), --编码
ck varchar(2), --仓库
sl int --数量
)insert into a
select '01' ,'a', 6
union all
select '01','b',7
union all
select '02','a',8
union all
select '02','b',9select * from a
(1)rollup 的用法
SELECT bm, hj=
case when grouping(ck)=0
then
ck
else
bm+'合计'
end
, Sum(sl) as sl
FROM A
GROUP BY bm, ck WITH ROLLUP
/*
01 a 6
01 b 7
01 01合计 13
02 a 8
02 b 9
02 02合计 17
NULL NULL 30*/(2)关于cuble的基本用法。SELECT bm, ck, Sum(sl) as sl
FROM A
GROUP BY bm,ck WITH cube
/* 01 a 6
01 b 7
01 NULL 13
02 a 8
02 b 9
02 NULL 17
NULL NULL 30
NULL a 14
NULL b 16 */本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/jack15850798154/archive/2010/02/24/5322853.aspx
不知道 在where字句或者having子句加条件能不能解决啊