懂SQL的大哥帮帮忙。入住天数计算。

harrycheng　发表于：2013-05-21 15:52:50 　

复制

员工入住宿舍入住房号日期状态

小明 C 302 2013-2-1 入住

小明 C 302 2013-5-2 退宿

小明 C 303 2013-5-3 入住

小明 C 303 2013-5-13 退宿

小明 C 304 2013-5-14 入住

小明 C 304 2013-5-20 退宿

小明 C 302 2013-5-21 入住

小红 C 302 2013-2-1 入住

小刚 C 301 2013-5-15 退宿

以上是员工入住宿舍的明细表，现在需要查询统计5月份（至5月31日止）各个员工在入住房号的入住天数。

即查询结果：

员工入住宿舍入住房号天数

小明 C 302 13

小明 C 303 11

小明 C 304 7

小红 C 302 31

小刚 C 302 15

如何编写SQL语句

Top

恐龙的传人发表于：2013-05-21 16:33:27

建议把日期改为入住日期和退宿日期，状态取消。否则不方便直接编写查询语句。这样可以减少记录数，减少冗余（套一下竹笛老师的讲课标准用词）。

Top

恐龙的传人发表于：2013-05-21 16:37:02

改为入住日期和退宿宿日期后，增加一个入住天数字段，用退宿日期-入住日期，统计该条记录入住天数，然后……后面的就很简单了。或者在窗体报表上sum合计入住天数，或者建查询汇总……

Top

西出阳关无故人发表于：2013-05-21 18:14:40

“小刚 C 301 2013-5-15 退宿”怎么算他的入住时间？

Top

西出阳关无故人发表于：2013-05-21 19:46:05

假设表的名称为a

sql：

SELECT b.员工, b.入住宿舍, b.入住房号, b.入住日期, b.退宿日期, [退宿日期]-IIf([入住日期]<#5/1/2013#,#5/1/2013#,[入住日期])+1 AS 天数, *
FROM (SELECT a.员工, a.入住宿舍, a.入住房号, a.日期 AS 入住日期,iif(isnull(DMIN("日期","A","员工='" & 员工 & "' AND 日期>#" & 日期 & "#")),#2013-5-31#,DMIN("日期","A","员工='" & 员工 & "' AND 日期>#" & 日期 & "#")) as 退宿日期
FROM a
WHERE (((a.状态)="入住"))) AS b;

缺失了小刚的数据

Top

西出阳关无故人发表于：2013-05-21 19:56:31

这个查询是以入住为基础（如果入住时间早于2013-5-1，就按2013-5-1计算），查找退宿时间，如果没有退宿，就按2013-5-31日计算。

小刚的数据肯定需要补录入住日期（不能无中生有，是吧？），应该没有问题。

Top

蒋元根发表于：2013-05-21 20:36:11

供参考

SELECT 查询交叉表.员工, 查询交叉表.入住宿舍, 查询交叉表.入住房号, IIf(IsNull(查询交叉表!入住),Forms!查询数据!起始,查询交叉表!入住) AS 入住日, IIf(IsNull(查询交叉表!退宿),Forms!查询数据!结束,查询交叉表!退宿) AS 退宿日, DateDiff("d",[入住日],[退宿日])+1 AS 入住天数
FROM 查询交叉表;

点击下载此附件

Top

西出阳关无故人发表于：2013-05-22 09:17:15

楼上的，有问题，一人在统计时段内多次入住相同的房间、宿舍，就会出错。

Top

西出阳关无故人发表于：2013-05-22 11:00:51

完整的sql（表名称为“住宿表”）：

PARAMETERS 开始日期 DateTime, 结束日期 DateTime;
SELECT dd.员工, dd.入住宿舍, dd.入住房号, Sum(dd.天数) AS 天数之总计
FROM (SELECT b.员工, b.入住宿舍, b.入住房号, b.入住日期, b.退宿日期, [退宿日期]-IIf([入住日期]<开始日期,开始日期,[入住日期])+1 AS 天数, *
FROM (SELECT 住宿表.员工, 住宿表.入住宿舍, 住宿表.入住房号, 住宿表.日期 AS 入住日期,iif(isnull(DMIN("日期","住宿表","员工='" & 员工 & "' AND 日期>#" & 日期 & "#")),结束日期,DMIN("日期","住宿表","员工='" & 员工 & "' AND 日期>#" & 日期 & "#")) as 退宿日期
FROM 住宿表
WHERE (((住宿表.状态)="入住"))) AS b
UNION SELECT AA.员工, AA.入住宿舍, AA.入住房号, AA.入住日期, AA.日期, IIf([日期]>结束日期,结束日期,[日期])-[入住日期]+1 AS 天数
FROM (SELECT 住宿表.员工, 住宿表.入住宿舍, 住宿表.入住房号, IIf(IsNull(DMax("日期","住宿表","状态='入住' AND 员工='" & [员工] & "' AND 日期<#" & [日期] & "#")),开始日期,DMax("日期","住宿表","状态='入住' AND 员工='" & [员工] & "' AND 日期<#" & [日期] & "#")) AS 入住日期, 住宿表.日期
FROM 住宿表
WHERE (((住宿表.日期)>=开始日期) AND ((DMax("日期","住宿表","状态='入住' AND 员工='" & [员工] & "' AND 日期<#" & [日期] & "#")) Is Null) AND ((住宿表.状态)="退宿"))) AS AA) AS dd
GROUP BY dd.员工, dd.入住宿舍, dd.入住房号;

把没有入住记录的也按照统计期初入住计算了，也解决一个员工在不同时间入住同一宿舍、同一房号的情况。

Top

蒋元根发表于：2013-05-22 18:01:47

修改，并且加上学习了西出阳关无故人老师方法的查询，供参考

点击下载此附件

Top

harrycheng 发表于：2013-05-22 19:41:34

版主，在“ 技术文章 > access查询”看到你的回复，实在有点意外和格外兴奋，多谢你的解答。

Top

总记录：10篇页次：1/1 9 1 :

Access交流中心

懂SQL的大哥帮帮忙。入住天数计算。