@@@@@@@@简单的SQL语句@@@@@@@@

[heartflying201]

有一个表，数据是：单子号，日期，部门编号...
doc_no, date1,dept_no,...

现在想汇总每个部门在各个月份的单子数量。
部门编号，一月份数量，二月份数量...十二月份数量
但是这个月份是 公司内部定的月份放在一个表中，比如：一月份‘2005-01-1’到‘2005-1-26’属于一月份
不知道如何根据这个月份表，然后把单子进行汇总显示为：部门编号，一月份数量，二月份数量...十二月份数量

[heartflying201]

这个不正确啊

[paoluo]

这个不正确啊

---------------------------------
錯誤在哪？？

[paoluo]

不好意思，的確有問題，改下。


create table table1(doc_no varchar(10),date1 datetime,dept_no int)
insert table1
select '1000001','2005-01-02',1 union
select '1000002','2005-01-26',1 union
select '1000003','2005-01-27',1 union
select '1000004','2005-01-28',2

create table table2(theYear Int,theMonth int,begDate datetime,endDate datetime)
Insert table2 Select 2005, 1, '2005-01-01', '2005-01-27'
Union All Select 2005, 2, '2005-01-28', '2005-03-29'
Union All Select 2005, 3, '2005-03-30', '2005-04-28'


Select
dept_no As 部门编号,
SUM((Case theMonth When 1 Then 1 Else 0 End)) As 一月份数量,
SUM((Case theMonth When 2 Then 1 Else 0 End)) As 二月份数量,
SUM((Case theMonth When 3 Then 1 Else 0 End)) As 三月份数量,
SUM((Case theMonth When 4 Then 1 Else 0 End)) As 四月份数量,
SUM((Case theMonth When 5 Then 1 Else 0 End)) As 五月份数量,
SUM((Case theMonth When 6 Then 1 Else 0 End)) As 六月份数量,
SUM((Case theMonth When 7 Then 1 Else 0 End)) As 七月份数量,
SUM((Case theMonth When 8 Then 1 Else 0 End)) As 八月份数量,
SUM((Case theMonth When 9 Then 1 Else 0 End)) As 九月份数量,
SUM((Case theMonth When 10 Then 1 Else 0 End)) As 十月份数量,
SUM((Case theMonth When 11 Then 1 Else 0 End)) As 十一月份数量,
SUM((Case theMonth When 12 Then 1 Else 0 End)) As 十二月份数量
from
(Select A.dept_no,B.theMonth from
table1 A
Inner Join table2 B
On A.date1 Between B.begDate And endDate) T
Group By dept_no,theMonth

Drop Table Table1,Table2
--結果
/*
部门编号 一月份数量 二月份数量 三月份数量 四月份数量 五月份数量 六月份数量 七月份数量 八月份数量 九月份数量 十月份数量 十一月份数量 十二月份数量
1 3 0 0 0 0 0 0 0 0 0 0 0
2 0 1 0 0 0 0 0 0 0 0 0 0
*/

[zlp321002]

--借楼上数据一用：

create table table1(doc_no varchar(10),date1 datetime,dept_no int)
insert table1
select '1000001','2005-01-02',1 union
select '1000002','2005-01-26',1 union
select '1000003','2005-01-27',1 union
select '1000004','2005-01-28',2

create table table2(theYear Int,theMonth int,begDate datetime,endDate datetime)
Insert table2 Select 2005, 1, '2005-01-01', '2005-01-27'
Union All Select 2005, 2, '2005-01-28', '2005-03-29'
Union All Select 2005, 3, '2005-03-30', '2005-04-28'



select
dept_no,
[一月份]=(select count(doc_no) from table1 t1, table2 t2
where dept_no=a.dept_no and year(date1)=2005 and month(date1)=1
and year(t1.date1)=t2.theYear and date1 between t2.begDate and t2.endDate
),
[二月份]=(select count(doc_no) from table1 t1, table2 t2
where dept_no=a.dept_no and year(date1)=2005 and month(date1)=2
and year(t1.date1)=t2.theYear and date1 between t2.begDate and t2.endDate
)
from table1 a
group by dept_no

--结果：
/*
dept_no 一月份 二月份
----------- ----------- -----------
1 3 0
2 1 0

(2 行受影响)

*/

--删除测试环境
Drop table Table1,Table2

--注意：补充完整所有月份。

[paoluo]

如果按照樓上這麼寫，寫完12個月的，SQL語句就太長了，而且每個月份都用子查詢，效率不夠高。

[zlp321002]

--有子查询,效率的确不高。可参考楼上的方法。

[-狙击手-]

select dept_no,
一月份数量 = sum(case datepart(mm,date1) when 1 then sl else 0 end),
二月份数量 = sum(case datepart(mm,date1) when 2 then sl else 0 end),
....

from table
group by datepart(mm,date1)

[itblog]

贴出两个表的结果和一些数据来~

[paoluo]

你的“月份表”的數據格式是怎樣的？？

[Jane_64]

create table table1(doc_no varchar(10),date1 datetime,dept_no int)
insert table1
select '1000001','2005-01-02',1 union
select '1000002','2005-01-26',1 union
select '1000003','2005-01-27',1 union
select '1000004','2005-01-28',2

create table table2(theMonth int,begDate datetime,endDate datetime)
insert table2
select 1,'2005-01-1','2005-1-26' union
select 2,'2005-01-27','2005-02-26' union
select 3,'2005-02-27','2005-03-26' union
select 4,'2005-03-27','2005-04-26' union
select 5,'2005-04-27','2005-05-26' union
select 6,'2005-05-27','2005-06-26' union
select 7,'2005-06-27','2005-07-26' union
select 8,'2005-07-27','2005-08-26' union
select 9,'2005-08-27','2005-09-26' union
select 10,'2005-09-27','2005-10-26' union
select 11,'2005-10-27','2005-11-26' union
select 12,'2005-11-27','2005-12-26'

declare @sql varchar(2000)
declare @month int
set @month=1
set @sql='select dept_no 部门编号'
select @sql=@sql+','+quotename(convert(varchar,theMonth)+'月份数量')
+'=sum(case when theMonth='+convert(varchar,@month)+' and date1 between begDate and endDate then 1 else 0 end)'
,@month=@month+1 from table2
select @sql=@sql+' from table1,table2 group by dept_no'
exec(@sql)

[heartflying201]

月份表是：年份 月份，开始时间，结束时间
2005 1 ‘2005-01-01’ ‘2005-01-27’
2005 2 ‘2005-01-28’ ‘2005-03-29’
2005 3 ‘2005-03-30’ ‘2005-04-28’
.....

[paoluo]

create table table1(doc_no varchar(10),date1 datetime,dept_no int)
insert table1
select '1000001','2005-01-02',1 union
select '1000002','2005-01-26',1 union
select '1000003','2005-01-27',1 union
select '1000004','2005-01-28',2

create table table2(theYear Int,theMonth int,begDate datetime,endDate datetime)
Insert table2 Select 2005, 1, '2005-01-01', '2005-01-27'
Union All Select 2005, 2, '2005-01-28', '2005-03-29'
Union All Select 2005, 3, '2005-03-30', '2005-04-28'

Select
doc_no As 部门编号,
(Case theMonth When 1 Then 1 Else 0 End) As 一月份数量,
(Case theMonth When 2 Then 1 Else 0 End) As 二月份数量,
(Case theMonth When 3 Then 1 Else 0 End) As 三月份数量,
(Case theMonth When 4 Then 1 Else 0 End) As 四月份数量,
(Case theMonth When 5 Then 1 Else 0 End) As 五月份数量,
(Case theMonth When 6 Then 1 Else 0 End) As 六月份数量,
(Case theMonth When 7 Then 1 Else 0 End) As 七月份数量,
(Case theMonth When 8 Then 1 Else 0 End) As 八月份数量,
(Case theMonth When 9 Then 1 Else 0 End) As 九月份数量,
(Case theMonth When 10 Then 1 Else 0 End) As 十月份数量,
(Case theMonth When 11 Then 1 Else 0 End) As 十一月份数量,
(Case theMonth When 12 Then 1 Else 0 End) As 十二月份数量
from
(Select A.doc_no,B.theMonth from
table1 A
Inner Join table2 B
On A.date1 Between B.begDate And endDate) T
Group By doc_no,theMonth

Drop Table Table1,Table2