首页新闻找找看学习计划

sql server查询昨天,今天,本周,本月的数据,急急急

0
悬赏园豆:140 [已解决问题] 解决于 2018-08-27 00:59

create table LeaveInfo
(
Lid int primary key identity(1,1),
StuNum nvarchar(50) not null,--学号,
StuName nvarchar(50) not null,--姓名
Phone nvarchar(30) not null,--手机号
GNum nvarchar(20) not null,--级别
CName nvarchar(20) not null,--班级
BeginDate date not null,--开始时间
EndDate date not null,--结束时间
Addresss nvarchar(50) not null,--地点
Reason nvarchar(200) not null,--原因
Principal nvarchar(10) not null,--负责人
Statu int ,--状态
)

上面是数据表,怎么编写sql语句实现像下图的功能,急急急急!

问题补充:

alger_li的主页 alger_li | 初学一级 | 园豆:65
提问于:2018-08-25 22:10
< >
分享
最佳答案
0
SELECT A.CName AS 班级名称, A.GNum AS 年级,
    (SELECT COUNT(*) FROM [dbo].[LeaveInfo] AS B 
      WHERE B.CName = A.CName AND B.GNum = A.GNum AND B.[Statu] = 1 
        AND B.BeginDate >= DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0) 
        AND B.EndDate < DATEADD(DD,DATEDIFF(DD,0,GETDATE()),1)) AS 今日请假,
    (SELECT COUNT(*) FROM [dbo].[LeaveInfo] AS B 
      WHERE B.CName = A.CName AND B.GNum = A.GNum AND B.[Statu] = 1 
        AND B.BeginDate >= DATEADD(DD,DATEDIFF(DD,0,GETDATE()),-1) 
        AND B.EndDate < DATEADD(DD,DATEDIFF(DD,0,GETDATE()),0)) AS 昨日请假,
    (SELECT COUNT(*) FROM [dbo].[LeaveInfo] AS B 
      WHERE B.CName = A.CName AND B.GNum = A.GNum AND B.[Statu] = 1 
        AND B.BeginDate >= DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) 
        AND B.EndDate < DATEADD(WK,DATEDIFF(WK,0,GETDATE()),7)) AS 本周请假,
    (SELECT COUNT(*) FROM [dbo].[LeaveInfo] AS B 
      WHERE B.CName = A.CName AND B.GNum = A.GNum AND B.[Statu] = 1 
        AND B.BeginDate >= DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0) 
        AND B.EndDate < DATEADD(MM,DATEDIFF(MM,0,GETDATE()) + 1,0)) AS 本月请假
FROM [dbo].[LeaveInfo] AS A 
GROUP BY A.CName, A.GNum
收获园豆:140
jello chen | 大侠五级 |园豆:7091 | 2018-08-27 00:22

为什么查出来的都是0,没有数据

alger_li | 园豆:65 (初学一级) | 2018-08-27 00:28

@alger_li: 你的请假状态Statu是多少

jello chen | 园豆:7091 (大侠五级) | 2018-08-27 00:29

@jello ch有的是1有的是0

alger_li | 园豆:65 (初学一级) | 2018-08-27 00:33

@alger_li: 请假状态是0还是1呢?

jello chen | 园豆:7091 (大侠五级) | 2018-08-27 00:34

@jello chen: 不用考虑这个值

alger_li | 园豆:65 (初学一级) | 2018-08-27 00:34

@alger_li: LeaveInfo这个是请假表是吧,那就把4个B.[Statu] = 1都去掉就好

jello chen | 园豆:7091 (大侠五级) | 2018-08-27 00:37

@jello chen: 本月数据对了,前几个不对

alger_li | 园豆:65 (初学一级) | 2018-08-27 00:42

@alger_li: 你可以自己验证啊,比如2015级Net1班的本月的6次请假,是在什么时间发生的,是否是在今日?昨日?本周?

jello chen | 园豆:7091 (大侠五级) | 2018-08-27 00:47

@jello chen: 搞定了,换一种方法就ok了,谢谢大牛指点。

alger_li | 园豆:65 (初学一级) | 2018-08-27 00:58
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册