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

2006-04-24 10:06:29 259 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

heartflying201 墨衍会员 · AI 创作全网分发关注 + 加入社区

8266+DS3231时钟之语音MP3-TF-16P模块使用【五】这个时钟系列的前的四篇分别是：《8266+DS3231时钟之开发个时钟遇到的N个坑【一】》《8266+ds3231时钟之arduino官网发布的DS3231库的分析【二】》《8266+DS3231时钟之DS3231具体实现及代码【三】》《8266+DS3231时钟之显示TM1638的使用【四】上》《8266+DS3231时钟之显示TM1638的使用【四】下》有兴趣的可以去看看，如果觉得对你有帮助，请点个赞。前面几篇，完成了8266NodeMCU、DS3231时钟模块、TM1638及数码管显示、阅读详情

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

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

...全文

odoo19/18集成reportbro 打印插件完美支持静默打印 ReportBro设计器一个用于创建PDF和XLSX报告模板的javascript插件。ReportBro Designer可以轻松集成到您的Web应用程序中。每个人都可以设计和编辑文档模板，并直接在浏览器中预览它们。可以使用服务器上的（有关完整的文档和演示，请参阅上的ReportBro项目网站。reportBro官方没有提供。测试环境：odoo17社区版。2.产品标签打印设计。阅读详情

heartflying201

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

这个不正确啊

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

相关推荐