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

heartflying201 2006-04-24 10:06:29
有一个表,数据是:单子号,日期,部门编号...
doc_no, date1,dept_no,...

现在想汇总每个部门在各个月份的单子数量。
部门编号,一月份数量,二月份数量...十二月份数量
但是这个月份是 公司内部定的月份放在一个表中,比如:一月份‘2005-01-1’到‘2005-1-26’属于一月份
不知道如何根据这个月份表,然后把单子进行汇总显示为:部门编号,一月份数量,二月份数量...十二月份数量
...全文
169 12 打赏 收藏 举报
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
paoluo 2006-04-24
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
  • 打赏
  • 举报
回复
heartflying201 2006-04-24
月份表是:年份 月份,开始时间,结束时间
2005 1 ‘2005-01-01’ ‘2005-01-27’
2005 2 ‘2005-01-28’ ‘2005-03-29’
2005 3 ‘2005-03-30’ ‘2005-04-28’
.....
  • 打赏
  • 举报
回复
Jane_64 2006-04-24
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)
  • 打赏
  • 举报
回复
paoluo 2006-04-24
你的“月份表”的數據格式是怎樣的??
  • 打赏
  • 举报
回复
itblog 2006-04-24
贴出两个表的结果和一些数据来~
  • 打赏
  • 举报
回复
-狙击手- 2006-04-24
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)
  • 打赏
  • 举报
回复
zlp321002 2006-04-24
--有子查询,效率的确不高。可参考楼上的方法。
  • 打赏
  • 举报
回复
paoluo 2006-04-24
如果按照樓上這麼寫,寫完12個月的,SQL語句就太長了,而且每個月份都用子查詢,效率不夠高。
  • 打赏
  • 举报
回复
zlp321002 2006-04-24
--借楼上数据一用:

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 2006-04-24
不好意思,的確有問題,改下。


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
*/
  • 打赏
  • 举报
回复
paoluo 2006-04-24
这个不正确啊

---------------------------------
錯誤在哪??
  • 打赏
  • 举报
回复
heartflying201 2006-04-24
这个不正确啊
  • 打赏
  • 举报
回复
相关推荐
发帖
MS-SQL Server
加入

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
申请成为版主
帖子事件
创建了帖子
2006-04-24 10:06
社区公告
暂无公告