求一统计T-SQL语句

谁是谁的谁 2009-04-22 01:03:44



create table #t (out_no varchar(10) primary key,date datetime,part varchar(30),qty numeric(12,4),price numeric(12,4)

insert into #t

select 'A01','2009-1-11','B001',100,1.1 union all

select 'A02','2009-1-12','B002',200,1.3 union all

select 'A03','2009-2-22','B003',120,1.5 union all

select 'A04','2009-3-22','B004',155,1.2 union all

select 'A05','2009-4-20','B005',600,1.6 union all

select 'A06','2009-4-22','B006',750,1.6

要求按月份分类汇总得出以下报表
月份 1月 2月 3月 4月 5月
出货数量 300 120 155 1350 0
出货金额 370 180 186 2160 0

...全文

152 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

谁是谁的谁 2009-04-22

打赏
举报

我这表大概有1W多条记录,我是想按月分类统计,上面的这些代码MS在5月以后又需要重新写报表了.

ws_hgo 2009-04-22

打赏
举报

接分
走人

長胸為富 2009-04-22

打赏
举报



SELECT TOP 1 '日期','1月','2月','3月','4月','5月' FROM #t

UNION ALL

SELECT 

    '出货数量', 

    CAST(SUM(CASE WHEN MONTH(date)=1 THEN qty ELSE 0 END) AS VARCHAR),  

    CAST(SUM(CASE WHEN MONTH(date)=2 THEN qty ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=3 THEN qty ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=4 THEN qty ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=5 THEN qty ELSE 0 END) AS VARCHAR)

FROM #t 

UNION ALL 

SELECT 

    '出货金额', 

    CAST(SUM(CASE WHEN MONTH(date)=1 THEN total ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=2 THEN total ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=3 THEN total ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=4 THEN total ELSE 0 END) AS VARCHAR),   

    CAST(SUM(CASE WHEN MONTH(date)=5 THEN total ELSE 0 END) AS VARCHAR) 

FROM (select out_no,date,part,qty,price,qty*price total from #t) m

SQL77 2009-04-22

打赏
举报



SELECT  DATEPART(MM,DATE) MM, SUM(QTY*PRICE) MO INTO #T1 FROM #T GROUP BY  DATEPART(MM,DATE)



SELECT 

SUM(CASE WHEN DATEPART(MM,DATE)=1 THEN QTY ELSE 0 END) AS '1月',

SUM(CASE WHEN DATEPART(MM,DATE)=2 THEN QTY ELSE 0 END) AS '2月',

SUM(CASE WHEN DATEPART(MM,DATE)=3 THEN QTY ELSE 0 END) AS '3月',

SUM(CASE WHEN DATEPART(MM,DATE)=4 THEN QTY ELSE 0 END) AS '4月',

SUM(CASE WHEN DATEPART(MM,DATE)=5 THEN QTY ELSE 0 END) AS '5月'

FROM #T



UNION ALL



SELECT  

MAX(CASE WHEN MM=1 THEN MO ELSE 0 END) AS '1月',

MAX(CASE WHEN MM=2 THEN MO ELSE 0 END) AS '2月',

MAX(CASE WHEN MM=3 THEN MO ELSE 0 END) AS '3月',

MAX(CASE WHEN MM=4 THEN MO ELSE 0 END) AS '4月',

MAX(CASE WHEN MM=5 THEN MO ELSE 0 END) AS '5月'

FROM #T1

300.0000 120.0000 155.0000 1350.0000 .0000
370.0000 180.0000 186.0000 2160.0000 .0000

usher_gml 2009-04-22

打赏
举报

create table #t(out_no varchar(10) primary key,date datetime,part varchar(30),qty numeric(12,4),price numeric(12,4))

insert into #t
select 'A01','2009-1-11','B001',100,1.1 union all
select 'A02','2009-1-12','B002',200,1.3 union all
select 'A03','2009-2-22','B003',120,1.5 union all
select 'A04','2009-3-22','B004',155,1.2 union all
select 'A05','2009-4-20','B005',600,1.6 union all
select 'A06','2009-4-22','B006',750,1.6
/*
月份 1月 2月 3月 4月 5月
出货数量 300 120 155 1350 0
出货金额 370 180 186 2160 0
*/
select * from #t
SELECT
月份='出货数量',
[1月]=SUM(CASE WHEN MONTH(date)=1 THEN qty ELSE 0 END),
[2月]=SUM(CASE WHEN MONTH(date)=2 THEN qty ELSE 0 END),
[3月]=SUM(CASE WHEN MONTH(date)=3 THEN qty ELSE 0 END),
[4月]=SUM(CASE WHEN MONTH(date)=4 THEN qty ELSE 0 END),
[5月]=SUM(CASE WHEN MONTH(date)=5 THEN qty ELSE 0 END)
FROM #t
UNION ALL
SELECT
月份='出货金额',
[1月]=SUM(CASE WHEN MONTH(date)=1 THEN total ELSE 0 END),
[2月]=SUM(CASE WHEN MONTH(date)=2 THEN total ELSE 0 END),
[3月]=SUM(CASE WHEN MONTH(date)=3 THEN total ELSE 0 END),
[4月]=SUM(CASE WHEN MONTH(date)=4 THEN total ELSE 0 END),
[5月]=SUM(CASE WHEN MONTH(date)=5 THEN total ELSE 0 END)
FROM (select out_no,date,part,qty,price,qty*price total from #t) m

drop table #t

htl258_Tony 2009-04-22

打赏
举报

create table #t (out_no varchar(10) primary key,date datetime,part varchar(30),qty numeric(12,4),price numeric(12,4))

insert into #t

select 'A01','2009-1-11','B001',100,1.1 union all

select 'A02','2009-1-12','B002',200,1.3 union all

select 'A03','2009-2-22','B003',120,1.5 union all

select 'A04','2009-3-22','B004',155,1.2 union all

select 'A05','2009-4-20','B005',600,1.6 union all

select 'A06','2009-4-22','B006',750,1.6 

--

--select * from #t



select

    月份='出货数量',

    [1月]=sum(case when month(date)=1 then qty else 0 end),

    [2月]=sum(case when month(date)=2 then qty else 0 end),

    [3月]=sum(case when month(date)=3 then qty else 0 end),

    [4月]=sum(case when month(date)=4 then qty else 0 end),

    [5月]=sum(case when month(date)=5 then qty else 0 end)

from #t

union all

select

    月份='出货金额',

    [1月]=sum(case when month(date)=1 then price*qty else 0 end),

    [2月]=sum(case when month(date)=2 then price*qty else 0 end),

    [3月]=sum(case when month(date)=3 then price*qty else 0 end),

    [4月]=sum(case when month(date)=4 then price*qty else 0 end),

    [5月]=sum(case when month(date)=5 then price*qty else 0 end)

from #t



/*

月份       1月                                      2月                                      3月                                      4月                                      5月

-------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------

出货数量     300.0000                                120.0000                                155.0000                                1350.0000                               0.0000

出货金额     370.0000                                180.0000                                186.0000                                2160.0000                               0.0000



(2 行受影响)

*/



drop table #t

claro 2009-04-22

打赏
举报

帮顶

liangCK 2009-04-22

打赏
举报

貌似看错了..

jinjazz 2009-04-22

打赏
举报

行转列，精华搜索一大堆

liangCK 2009-04-22

打赏
举报

SELECT

    月份='出货数量',

    [1月]=SUM(CASE WHEN MONTH(date)=1 THEN qty ELSE 0 END),

    [2月]=SUM(CASE WHEN MONTH(date)=2 THEN qty ELSE 0 END),

    [3月]=SUM(CASE WHEN MONTH(date)=3 THEN qty ELSE 0 END),

    [4月]=SUM(CASE WHEN MONTH(date)=4 THEN qty ELSE 0 END),

    [5月]=SUM(CASE WHEN MONTH(date)=5 THEN qty ELSE 0 END)

FROM #t

UNION ALL

SELECT

    月份='出货金额',

    [1月]=SUM(CASE WHEN MONTH(date)=1 THEN price ELSE 0 END),

    [2月]=SUM(CASE WHEN MONTH(date)=2 THEN price ELSE 0 END),

    [3月]=SUM(CASE WHEN MONTH(date)=3 THEN price ELSE 0 END),

    [4月]=SUM(CASE WHEN MONTH(date)=4 THEN price ELSE 0 END),

    [5月]=SUM(CASE WHEN MONTH(date)=5 THEN price ELSE 0 END)

FROM #t