求按月进行GOODID分类汇总的SQL语句(高)
表1
goodid date1 bh Num1 Num2
000001 2005-1-1 1101 50 10
000001 2005-1-2 1101 50 10
000001 2005-1-3 1101 50 10
000001 2005-1-4 1101 50 10
000001 2005-2-1 1101 50 10
000001 2005-2-2 1101 50 10
000002 2005-1-1 1101 50 10
000002 2005-1-2 1101 50 10
000002 2005-1-3 1101 50 10
000002 2005-1-4 1101 50 10
000002 2005-2-1 1101 50 10
000002 2005-2-2 1101 50 10
000003 2005-1-1 1101 50 10
000003 2005-1-2 1101 50 10
000003 2005-1-3 1101 50 10
000003 2005-1-4 1101 50 10
000003 2005-2-1 1101 50 10
000003 2005-2-2 1101 50 10
其中：goodid=char(6)为商品ID
bh=char(4)
num1=int为销量1
num2=int为销量2
现要统计显示如下表
说明：每个月按 "商品ID "（goodID）进行汇总,(下表空行便于查看)
goodid date1 bh Num1 Num2
000001 2005-1-1 1101 50 10
000001 2005-1-2 1101 50 10
000001 2005-1-3 1101 50 10
000001 2005-1-4 1101 50 10
小计 200 40
000001 2005-2-1 1101 50 10
000001 2005-2-2 1101 50 10
小计 100 20
000002 2005-1-1 1101 50 10
000002 2005-1-2 1101 50 10
000002 2005-1-3 1101 50 10
000002 2005-1-4 1101 50 10
小计 200 40
000002 2005-2-1 1101 50 10
000002 2005-2-2 1101 50 10
小计 100 20
000003 2005-1-1 1101 50 10
000003 2005-1-2 1101 50 10
000003 2005-1-3 1101 50 10
000003 2005-1-4 1101 50 10
小计 200 40
000003 2005-2-1 1101 50 10
000003 2005-2-2 1101 50 10
小计 100 20
合计 900 180
[解决办法]
select
goodid,
date1,
bh,
sum(Num1) as Num1,
sum(Num2) as Num2
from
表1
group by
goodid,convert(char(7),date1,120),date1,bh
with rollup having grouping(bh)=0 or grouping(date1)=1
[解决办法]
显示出来的每次都有都有二次小计
可以只显示一次小计吗
------------------------------
樓主的意思是：按 "商品ID "（goodID）、月份分組求和嗎？
declare @t table(goodid varchar(10),date1 datetime,bh int,Num1 int,Num2 int)
insert into @t select '000001 ', '2005-1-1 ',1101,50,10
insert into @t select '000001 ', '2005-1-2 ',1101,50,10
insert into @t select '000001 ', '2005-1-3 ',1101,50,10
insert into @t select '000001 ', '2005-1-4 ',1101,50,10
insert into @t select '000001 ', '2005-2-1 ',1101,50,10
insert into @t select '000001 ', '2005-2-2 ',1101,50,10
insert into @t select '000002 ', '2005-1-1 ',1101,50,10
insert into @t select '000002 ', '2005-1-2 ',1101,50,10
insert into @t select '000002 ', '2005-1-3 ',1101,50,10
insert into @t select '000002 ', '2005-1-4 ',1101,50,10
insert into @t select '000002 ', '2005-2-1 ',1101,50,10
insert into @t select '000002 ', '2005-2-2 ',1101,50,10
insert into @t select '000003 ', '2005-1-1 ',1101,50,10
insert into @t select '000003 ', '2005-1-2 ',1101,50,10
insert into @t select '000003 ', '2005-1-3 ',1101,50,10
insert into @t select '000003 ', '2005-1-4 ',1101,50,10
insert into @t select '000003 ', '2005-2-1 ',1101,50,10
insert into @t select '000003 ', '2005-2-2 ',1101,50,10
select
(case
when goodid is null then '合计 '
when bh is null and goodid is not null then '小计 '
else goodid
end) as goodid,
convert(char(7),date1,120) as date1,
bh,
sum(Num1) as Num1,
sum(Num2) as Num2
from
@t
group by
goodid,convert(char(7),date1,120),bh
with rollup
having grouping(bh)=0 or grouping(convert(char(7),date1,120))=0 or grouping(goodid)=1
[解决办法]
CREATE TABLE A
(
goodid char(6),
date1 DATETIME,
bh char(4),
Num1 INT,
Num2 INT
)
INSERT INTO A
SELECT '000001 ', '2005-1-1 ', '1101 ',50,10 UNION ALL
SELECT '000001 ', '2005-1-2 ', '1101 ',50,10 UNION ALL
SELECT '000001 ', '2005-1-3 ', '1101 ',50,10 UNION ALL
SELECT '000001 ', '2005-1-4 ', '1101 ',50,10 UNION ALL
SELECT '000001 ', '2005-2-1 ', '1101 ',50,10 UNION ALL
SELECT '000001 ', '2005-2-2 ', '1101 ',50,10 UNION ALL
SELECT '000001 ', '2005-2-2 ', '1101 ',50,10 UNION ALL
SELECT '000002 ', '2005-1-1 ', '1101 ',50,10 UNION ALL
SELECT '000002 ', '2005-1-2 ', '1101 ',50,10 UNION ALL
SELECT '000002 ', '2005-1-3 ', '1101 ',50,10 UNION ALL
SELECT '000002 ', '2005-1-4 ', '1101 ',50,10 UNION ALL
SELECT '000002 ', '2005-2-1 ', '1101 ',50,10 UNION ALL
SELECT '000002 ', '2005-2-2 ', '1101 ',50,10 UNION ALL
SELECT '000003 ', '2005-1-1 ', '1101 ',50,10 UNION ALL
SELECT '000003 ', '2005-1-2 ', '1101 ',50,10 UNION ALL
SELECT '000003 ', '2005-1-3 ', '1101 ',50,10 UNION ALL
SELECT '000003 ', '2005-1-4 ', '1101 ',50,10 UNION ALL
SELECT '000003 ', '2005-2-1 ', '1101 ',50,10 UNION ALL
SELECT '000003 ', '2005-2-2 ', '1101 ',50,10
SELECT CASE GROUPING(bh)+GROUPING(date1)+GROUPING(goodid) WHEN 2 THEN '小计 '
WHEN 3 THEN '总计 ' ELSE goodid END goodid,isnull(convert(char(10),date1,120), ' ') date1,SUM(Num1) Num1,SUM(Num2) Num2 FROM A
GROUP BY goodid,CONVERT(char(7),date1,120),date1,bh
WITH ROLLUP
HAVING grouping(bh)=0 or grouping(date1)=1
--结果
goodid date1 Num1 Num2
------ ---------- ----------- -----------
000001 2005-01-01 50 10
000001 2005-01-02 50 10
000001 2005-01-03 50 10
000001 2005-01-04 50 10
小计 200 40
000001 2005-02-01 50 10
000001 2005-02-02 100 20
小计 150 30
小计 350 70
000002 2005-01-01 50 10
000002 2005-01-02 50 10
000002 2005-01-03 50 10
000002 2005-01-04 50 10
小计 200 40
000002 2005-02-01 50 10
000002 2005-02-02 50 10
小计 100 20
小计 300 60
000003 2005-01-01 50 10
000003 2005-01-02 50 10
000003 2005-01-03 50 10
000003 2005-01-04 50 10
小计 200 40
000003 2005-02-01 50 10
000003 2005-02-02 50 10
小计 100 20
小计 300 60
总计 950 190
(28 行受影响)
[解决办法]
----比我快....要枪毙~~~
create table 表1(goodid char(6),
date1 datetime,
bh char(4),
num1 int,
num2 int)
insert into 表1 select '000001 ', '2005-1-1 ', '1101 ', 50, 10
union all select '000001 ', '2005-1-2 ', '1101 ', 50, 10
union all select '000001 ', '2005-1-3 ', '1101 ', 50, 10
union all select '000001 ', '2005-1-4 ', '1101 ', 50, 10
union all select '000001 ', '2005-2-1 ', '1101 ', 50, 10
union all select '000001 ', '2005-2-2 ', '1101 ', 50, 10
union all select '000002 ', '2005-1-1 ', '1101 ', 50, 10
union all select '000002 ', '2005-1-2 ', '1101 ', 50, 10
union all select '000002 ', '2005-1-3 ', '1101 ', 50, 10
union all select '000002 ', '2005-1-4 ', '1101 ', 50, 10
union all select '000002 ', '2005-2-1 ', '1101 ', 50, 10
union all select '000002 ', '2005-2-2 ', '1101 ', 50, 10
union all select '000003 ', '2005-1-1 ', '1101 ', 50, 10
union all select '000003 ', '2005-1-2 ', '1101 ', 50, 10
union all select '000003 ', '2005-1-3 ', '1101 ', 50, 10
union all select '000003 ', '2005-1-4 ', '1101 ', 50, 10
union all select '000003 ', '2005-2-1 ', '1101 ', 50, 10
union all select '000003 ', '2005-2-2 ', '1101 ', 50, 10
select
case when grouping(goodid)=1 then '合计: 'when grouping(date1)=1 then '小计: ' else goodid end 'goodid ',
isnull(CONVERT(varchar(10),date1,120), ' ') 'date1 ',
case when grouping(date1)=1 then ' 'else bh end 'bh ',
sum(num1)num1,sum(num2)num2
from 表1
group by goodid,CONVERT(varchar(7),date1,120),bh,date1 with rollup
having grouping(CONVERT(varchar(7),date1,120))=0 and grouping(bh)=0 or grouping(goodid)=1
[解决办法]
goodid date1 bh num1 num2
0000012005-01-0111015010
0000012005-01-0211015010
0000012005-01-0311015010
0000012005-01-0411015010
小计: 20040
0000012005-02-0111015010
0000012005-02-0211015010
小计: 10020
0000022005-01-0111015010
0000022005-01-0211015010
0000022005-01-0311015010
0000022005-01-0411015010
小计: 20040
0000022005-02-0111015010
0000022005-02-0211015010
小计: 10020
0000032005-01-0111015010
0000032005-01-0211015010
0000032005-01-0311015010
0000032005-01-0411015010
小计: 20040
0000032005-02-0111015010
0000032005-02-0211015010
小计: 10020
合计: 900180
