求按年份、月份统计的SQL

Question

数据库中有3个表a，b，c。通过JobID关联，年份、月份通过JobNo的第3位到第6位来表示，例如，ZL0708-001，其中0708就表示07年8月份。现在要实现按年份、月份，也就是通过0708之类的数据，来统计每个月的收入和支出，如何通过SQL来实现一次查询得到所有的数据（或一年的数据）表a （成本）
JobID    Detail   InvoiceSum   
001       仓储费      100.00
002       运输费      300.00
001       加班费      100.00
001       误餐费       50.00
001       交通费       20.00
002       卸货费      300.00
002       仓储费      700.00
003       运输费      600.00
003       起吊费      500.00表b （收入）
JobID    ChargeName    Account
001       仓储费       100.00
001       服务费       400.00
001       交通费       100.00
002       服务费       400.00
002       运输费       400.00
002       卸货费       300.00
002       仓储费       700.00
003       服务费       500.00
003       运输费      1200.00表c  （业务资料）
JobID     JobNo
001       ZL0708-001
002       ZL0708-002
004       ZL0709-001要实现：
       收入       成本       毛利
8月    2400     1570.00      830.00
9月    1700.00  1100.00      600.00

Answer 1

create table #a
(JobID char(3),Detail nvarchar(20), InvoiceSum  decimal(10,2))insert #a select '001','仓储费' ,100.00
insert #a select '002','运输费' ,300.00
insert #a select '001','加班费' ,100.00
insert #a select '001','误餐费' ,50.00
insert #a select '001','交通费' ,20.00
insert #a select '002','卸货费' ,300.00
insert #a select '002','仓储费' ,700.00
insert #a select '003','运输费' ,600.00
insert #a select '003','起吊费' ,500.00create table #b
(JobID char(3),ChargeName nvarchar(20), Account  decimal(10,2))
insert #b select '001','仓储费',100.00
insert #b select '001','服务费',400.00
insert #b select '001','交通费',100.00
insert #b select '002','服务费',400.00
insert #b select '002','运输费',400.00
insert #b select '002','卸货费',300.00
insert #b select '002','仓储费',700.00
insert #b select '003','服务费',500.00
insert #b select '003','运输费',1200.00
create table #c
(JobID char(3),JobNo char(10))insert #c select '001','ZL0708-001'
insert #c select '002','ZL0708-002'
insert #c select '003','ZL0709-001'
select SUBSTRING(c.JobNo,3,4),SUM(b.Account) as 收入,SUM(a.InvoiceSum)  as 成本 ,SUM(b.Account)-SUM(a.InvoiceSum) as 毛利 from #a a,#b b,#c c
where c.JobID=a.JobID and c.JobID=b.JobID
group by SUBSTRING(c.JobNo,3,4)
         收入                                      成本                                      毛利
-------- --------------------------------------- --------------------------------------- ---------------------------------------
0708     7800.00                                 6010.00                                 1790.00
0709     3400.00                                 2200.00                                 1200.00(2 行受影响)???????数据不对

Answer 2

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-03-12 21:58:00
-- 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)
--
----------------------------------------------------------------
--> 测试数据：#A
if object_id('tempdb.dbo.#A') is not null drop table #A
go 
create table #A([JobID] varchar(3),[Detail] varchar(6),[InvoiceSum] numeric(5,2))
insert #A
select '001','仓储费',100.00 union all
select '002','运输费',300.00 union all
select '001','加班费',100.00 union all
select '001','误餐费',50.00 union all
select '001','交通费',20.00 union all
select '002','卸货费',300.00 union all
select '002','仓储费',700.00 union all
select '003','运输费',600.00 union all
select '003','起吊费',500.00
--> 测试数据：#B
if object_id('tempdb.dbo.#B') is not null drop table #B
go 
create table #B([JobID] varchar(3),[ChargeName] varchar(6),[Account] numeric(6,2))
insert #B
select '001','仓储费',100.00 union all
select '001','服务费',400.00 union all
select '001','交通费',100.00 union all
select '002','服务费',400.00 union all
select '002','运输费',400.00 union all
select '002','卸货费',300.00 union all
select '002','仓储费',700.00 union all
select '003','服务费',500.00 union all
select '003','运输费',1200.00
--> 测试数据：#C
if object_id('tempdb.dbo.#C') is not null drop table #C
go 
create table #C([JobID] varchar(3),[JobNo] varchar(10))
insert #C
select '001','ZL0708-001' union all
select '002','ZL0708-002' union all
select '003','ZL0709-001'
--------------开始查询--------------------------
select 
SUBSTRING(JobNo,3,CHARINDEX('-',JobNo)-1-3+1) JobNo,
SUM(B.收入),SUM(A.成本),SUM(B.收入)-SUM(A.成本)
 from #C C
LEFT JOIN 
(SELECT B.JobID,SUM(B.Account)收入 FROM #B B GROUP BY B.JobID) B ON C.JobID=B.JobID 
LEFT JOIN 
(SELECT A.JobID,SUM(A.InvoiceSum)成本 FROM #A A GROUP BY A.JobID) A ON C.JobID=A.JobIDGROUP BY SUBSTRING(JobNo,3,CHARINDEX('-',JobNo)-1-3+1)----------------结果----------------------------
/* （所影响的行数为 9 行）
（所影响的行数为 9 行）
（所影响的行数为 3 行）JobNo                                                                                                                                 
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
0708       2400.00                                  1570.00                                  830.00
0709       1700.00                                  1100.00                                  600.00（所影响的行数为 2 行）
*/

Answer 3

create table #a
(JobID char(3),Detail nvarchar(20), InvoiceSum  decimal(10,2))insert #a select '001','仓储费' ,100.00
insert #a select '002','运输费' ,300.00
insert #a select '001','加班费' ,100.00
insert #a select '001','误餐费' ,50.00
insert #a select '001','交通费' ,20.00
insert #a select '002','卸货费' ,300.00
insert #a select '002','仓储费' ,700.00
insert #a select '003','运输费' ,600.00
insert #a select '003','起吊费' ,500.00create table #b
(JobID char(3),ChargeName nvarchar(20), Account  decimal(10,2))
insert #b select '001','仓储费',100.00
insert #b select '001','服务费',400.00
insert #b select '001','交通费',100.00
insert #b select '002','服务费',400.00
insert #b select '002','运输费',400.00
insert #b select '002','卸货费',300.00
insert #b select '002','仓储费',700.00
insert #b select '003','服务费',500.00
insert #b select '003','运输费',1200.00
create table #c
(JobID char(3),JobNo char(10))insert #c select '001','ZL0708-001'
insert #c select '002','ZL0708-002'
insert #c select '003','ZL0709-001'go
select SUBSTRING(c.JobNo,3,4),SUM(b.Account) as 收入,
SUM(a.InvoiceSum)  as 成本 ,SUM(b.Account)-SUM(a.InvoiceSum) as 毛利 
from (select jobid,sum(invoicesum) as invoicesum from #a group by jobid)a,
(select jobid ,sum(account) as account from #b group by jobid) b,#c c
where c.JobID=a.JobID and c.JobID=b.JobID
group by SUBSTRING(c.JobNo,3,4)
drop table #a,#b,#c         收入                                       成本                                       毛利                                       
-------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
0708     2400.00                                  1570.00                                  830.00
0709     1700.00                                  1100.00                                  600.00（所影响的行数为 2 行）

Answer 4

select substring(c.jobno,5,2)+'年'+substring(c.jobno,5,2)+'月' as 日期,
sum(b.Account)as 收入 ,sum(a.InvoiceSum) as 成本,sum(b.Account)-sum(a.InvoiceSum) as 毛利
from 
----......./*
         收入    成本     毛利
---------------------------------
08年08月 2400.00 1570.00 830.00
09年09月 1700.00 1100.00 600.00*/

Answer 5

Hi,Coming one.0)Structure and datacreate table #A([JobID] varchar(3),[Detail] varchar(6),[InvoiceSum] numeric(5,2))
insert #A
select '001','仓储费',100.00 union all
select '002','运输费',300.00 union all
select '001','加班费',100.00 union all
select '001','误餐费',50.00 union all
select '001','交通费',20.00 union all
select '002','卸货费',300.00 union all
select '002','仓储费',700.00 union all
select '003','运输费',600.00 union all
select '003','起吊费',500.00create table #B([JobID] varchar(3),[ChargeName] varchar(6),[Account] numeric(6,2))
insert #B
select '001','仓储费',100.00 union all
select '001','服务费',400.00 union all
select '001','交通费',100.00 union all
select '002','服务费',400.00 union all
select '002','运输费',400.00 union all
select '002','卸货费',300.00 union all
select '002','仓储费',700.00 union all
select '003','服务费',500.00 union all
select '003','运输费',1200.00create table #C([JobID] varchar(3),[JobNo] varchar(10))
insert #C
select '001','ZL0708-001' union all
select '002','ZL0708-002' union all
select '003','ZL0709-001'1)QuerySELECT date = CAST(CAST(RIGHT(A.Sort,2) AS int)AS varchar(2))+'月' ,收入 = Income,成本 = Cost,毛利 = Income - Cost
FROM
(
SELECT Cost = SUM(a.InvoiceSum),Sort = SUBSTRING(c.JobNo,1,6) FROM #C c
INNER JOIN #A a
ON c.JobID = a.JobID
GROUP BY SUBSTRING(c.JobNo,1,6)
)A
INNER JOIN  
(
SELECT Income = SUM(b.Account),Sort= SUBSTRING(c.JobNo,1,6) FROM #C c
INNER JOIN #B b
ON b.JobID = c.JobID
GROUP BY SUBSTRING(c.JobNo,1,6))B
ON A.Sort = B.Sort2)Resultdate 收入                                      成本                                      毛利
---- --------------------------------------- --------------------------------------- ---------------------------------------
8月   2400.00                                 1570.00                                 830.00
9月   1700.00                                 1100.00                                 600.00(2 row(s) affected)

Answer 6

select tab1.date,tab1.收入,tab2.成本,收入-成本 as 毛利
from
(select subString(c.jobNo,6,1) + '月' as date,
       sum(b.Account) as 收入 
from (c inner join b on c.jobId = b.jobID)
group by subString(jobNo,6,1) + '月') as tab1
inner join
(select subString(c.jobNo,6,1) + '月' as date, 
       sum(a.InvoiceSum) as 成本  
from   (c inner join a on c.jobId = a.jobID)
group by subString(jobNo,6,1) + '月') as tab2
on tab1.date = tab2.date;

Answer 7

[code=SQL][select tab1.date,tab1.收入,tab2.成本,收入-成本 as 毛利
from
(select subString(c.jobNo,6,1) + '月' as date,
       sum(b.Account) as 收入 
from (c inner join b on c.jobId = b.jobID)
group by subString(jobNo,6,1) + '月') as tab1
inner join
(select subString(c.jobNo,6,1) + '月' as date, 
       sum(a.InvoiceSum) as 成本  
from   (c inner join a on c.jobId = a.jobID)
group by subString(jobNo,6,1) + '月') as tab2
on tab1.date = tab2.date;]

Answer 8

各位大侠们，出来乍到，sql不会插，请指点指点

Answer 9

谢谢楼上各位。还有个问题，如果成本或应收为空，我用了left join，得到的结果就不对了，为空的那一个业务号下所有的费用就不会统计进去了，这个要如何解决？

Answer 10

也就是，假如数据库是这样：create table #a
(JobID char(3),Detail nvarchar(20), InvoiceSum  decimal(10,2))insert #a select '001','仓储费' ,100.00
insert #a select '002','运输费' ,300.00
insert #a select '001','加班费' ,100.00
insert #a select '001','误餐费' ,50.00
insert #a select '001','交通费' ,20.00
insert #a select '002','卸货费' ,300.00
insert #a select '002','仓储费' ,700.00
insert #a select '003','运输费' ,600.00
insert #a select '003','起吊费' ,500.00
insert #a select '004','起吊费' ,550.00create table #b
(JobID char(3),ChargeName nvarchar(20), Account  decimal(10,2))
insert #b select '001','仓储费',100.00
insert #b select '001','服务费',400.00
insert #b select '001','交通费',100.00
insert #b select '002','服务费',400.00
insert #b select '002','运输费',400.00
insert #b select '002','卸货费',300.00
insert #b select '002','仓储费',700.00
insert #b select '003','服务费',500.00
insert #b select '003','运输费',1200.00
create table #c
(JobID char(3),JobNo char(10))insert #c select '001','ZL0708-001'
insert #c select '002','ZL0708-002'
insert #c select '003','ZL0709-001'
insert #c select '004','ZL0709-002'go
select SUBSTRING(c.JobNo,3,4),SUM(b.Account) as 收入,
SUM(a.InvoiceSum)  as 成本 ,SUM(b.Account)-SUM(a.InvoiceSum) as 毛利 
from (select jobid,sum(invoicesum) as invoicesum from #a group by jobid)a,
(select jobid ,sum(account) as account from #b group by jobid) b,#c c
where c.JobID=a.JobID and c.JobID=b.JobID
group by SUBSTRING(c.JobNo,3,4)
drop table #a,#b,#c成本表增加 004 起吊费 550，业务表增加 004   ZL0709-002,那么，得到的结果应该是：
收入              成本                毛利
8月 2400       1570.00      830.00
9月 1700.00    1650.00      600.00                
而运行的结果却是：
          收入    成本       毛利
8月    2400.00  1570.00    830.00
9月    1700.00  1100.00    600.00收入为空的数据被忽略了

Answer 11

刚才试验了一下，在有空数据的情况下，happyflystone的语句结果出错，shenliang1985的正确。原因是什么？

Answer 12

Hi,1)QuerySELECT date = CAST(CAST(RIGHT(A.Sort,2) AS int)AS varchar(2))+'月' ,收入 = Income,成本 = Cost,毛利 = Income - Cost
FROM
(
SELECT Cost = SUM(a.InvoiceSum),Sort = SUBSTRING(c.JobNo,1,6) FROM #C c
INNER JOIN #A a
ON c.JobID = a.JobID
GROUP BY SUBSTRING(c.JobNo,1,6)
)A
INNER JOIN  
(
SELECT Income = SUM(b.Account),Sort= SUBSTRING(c.JobNo,1,6) FROM #C c
INNER JOIN #B b
ON b.JobID = c.JobID
GROUP BY SUBSTRING(c.JobNo,1,6))B
ON A.Sort = B.Sort2)Resultdate 收入                                      成本                                      毛利
---- --------------------------------------- --------------------------------------- ---------------------------------------
8月   2400.00                                 1570.00                                 830.00
9月   1700.00                                 1650.00                                 50.00(2 row(s) affected)