求一SQL语句,测试数据已经写好,多谢帮忙!!!!!!

WeiWY 2012-03-03 10:04:54

--我用的数据库是SQL Serever 2008,有如下表及测试数据,
--其中c1,c2,c3,g1,g2,g3为时间,f1,f2,f3的值只有0或1,0代表当天,1代表第二天
--如 f1=1,g1=20:23 ,表示 g1所表达的时间为第二天的20:23分。
CREATE TABLE [Test](
[Id] [int] NOT NULL primary key,
[c1] [time](7) NOT NULL,
[c2] [time](7) NOT NULL,
[c3] [time](7) NOT NULL,
[f1] [tinyint] NOT NULL,
[g1] [time](7) NOT NULL,
[f2] [tinyint] NOT NULL,
[g2] [time](7) NOT NULL,
[f3] [tinyint] NOT NULL,
[g3] [time](7) NOT NULL,
[n] [int] NOT NULL
)
insert into Test(id,c1,c2,c3,f1,g1,f2,g2,f3,g3,n)
select 1,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',1 union all
select 2,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'17:59',1 union all
select 3,'20:10','20:30','20:59',1,'2:10',1,'2:30',1,'5:00',1 union all
select 4,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',2 union all
select 5,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'19:59',2

--假设查询的条件为 当前时间(getdate()), 和 字段 n (值可能为1,2,3,4,5,6等整数),
--想要的查询结果:
--如果当前时间在 区间 c1~c2,返回值为1,在区间c2~c3,返回值为2,
--在区间g1~g2,返回值为3,在区间g2~g3,返回值为4,(此时需要考虑对应的f1\f2\f3,表示当天还是第二天)
--如上述测试用例,如果查询时间是2018-9-1 8:20,返回值应该是1
--如果查询时间为 2012-3-3 11:25, n=1,那么返回值应该是 3
--如果查询时间为 2013-3-4 2:15,n=1返回值应该是3 ,如果查询时间是2012-3-4 3:50,n=1,返回值应该是4
...全文
228 23 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复

select MAX(
case
when GETDATE() between c1 and c2 then 1
when GETDATE() between c2 and c3 then 2
when f1=0 and GETDATE() between g1 and g2 then 3
when f1=0 and GETDATE() between g2 and g3 then 4
else 0 end
) as [state] from [test] where n=1

--基本思路貌似就是这样的,楼主把getdate()里的time段取出来去比较就好了
anlianganl 2012-03-03
  • 打赏
  • 举报
回复
CREATE TABLE [Test](
[Id] [int] NOT NULL primary key,
[c1] varchar(7) NOT NULL,
[c2] varchar(7) NOT NULL,
[c3] varchar(7) NOT NULL,
[f1] [tinyint] NOT NULL,
[g1] varchar(7) NOT NULL,
[f2] [tinyint] NOT NULL,
[g2] varchar(7) NOT NULL,
[f3] [tinyint] NOT NULL,
[g3] varchar(7) NOT NULL,
[n] [int] NOT NULL
)
insert into Test(id,c1,c2,c3,f1,g1,f2,g2,f3,g3,n)
select 1,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',1 union all
select 2,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'17:59',1 union all
select 3,'20:10','20:30','20:59',1,'2:10',1,'2:30',1,'5:00',1 union all
select 4,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',2 union all
select 5,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'19:59',2
select * from test

declare @time int
declare @n int
set @n=1
set @time =74000;
with cte as
(
select
id,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + c1)as c1,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + c2)as c2,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + c3)as c3,
f1,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + g1)as g1,
f2,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + g2)as g2,
f3,
datediff(ss,convert(varchar(10),getdate(),120) ,convert(varchar(11),getdate(),120) + g3)as g3,
n
from test
)
select
max(
case when @time between c1 and c2 then '1'+ cast(f1 as varchar(1))
when @time between c2 and c3 then '2,'+ cast(f1 as varchar(1))
when @time between g1 and g2 then '3'+ cast(f1 as varchar(1))
when @time between g2 and g3 then '4'+ cast(f1 as varchar(1))
else '0' end
)as flag
from cte where n=@n

--flag 前面是输出的 后面是 f1
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
叶子的语法通不过。没有考虑 f1,f2,f3, f1,f2,f2为1表示第二天,为0表示当天。多谢。
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 maco_wang 的回复:]

SQL code

select max(case
when getdate() between c1 and c2 then 1
when getdate() between c2 and c3 then 2
when getdate() between g1 and g2 then 3
end) from [Test] where f1=0
[/Quote]

叶子哥哥
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
f1,f2,f3和g1,g2,g3联合使用的。 f1=0,g1表示的是当天的时间, f1=1,表示的g1是第二天的时间。
不一定要一条SQL语句,存储过程,能查询出来也行啊,只要效率好就行。多谢
百年树人 2012-03-03
  • 打赏
  • 举报
回复
其实没弄懂f1/f2/f3的作用,try
declare @dt datetime
set @dt='2018-9-1 8:20'
select value
from(
select id,c1,c2,n,1 as value from test
union all
select id,c2,c3,n,2 from test
union all
select id,g1,g2,n,3 from test
union all
select id,g2,g3,n,4 from test
) t
where convert(varchar(5),@dt,108) between c1 and c2
and n=1

WeiWY 2012-03-03
  • 打赏
  • 举报
回复
maco_wang
(☆叶子☆)

我用的是SQL Server 2008,time是时间类型的,和getdate()不能比较,也就是说你写的语法上通不过。
多谢!
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
--如上述测试用例,如果查询时间是2018-9-1 8:20,返回值应该是1
这里返回值应该是2,写错了。
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
是考勤用的。
百年树人 2012-03-03
  • 打赏
  • 举报
回复
--如上述测试用例,如果查询时间是2018-9-1 8:20,返回值应该是1

这个时间应该是c2到c3之间,返回值为什么是1不是2?
叶子 2012-03-03
  • 打赏
  • 举报
回复

declare @date datetime set @date='2018-9-1 8:20'
declare @n int set @i=1

select max(case
when @date between c1 and c2 then 1
when @date between c2 and c3 then 2
when @date between g1 and g2 then 3
end) from [Test] where f1=0 and n=@i
叶子 2012-03-03
  • 打赏
  • 举报
回复

select max(case
when getdate() between c1 and c2 then 1
when getdate() between c2 and c3 then 2
when getdate() between g1 and g2 then 3
end) from [Test] where f1=0
  • 打赏
  • 举报
回复
case when then when then……应该可以实现,没测试环境,不然可以试试
叶子 2012-03-03
  • 打赏
  • 举报
回复
只有当天和第二天,而当天还是根据系统时间在变化的,岂不是无法确定准确日期?

--如上述测试用例,如果查询时间是2018-9-1 8:20,返回值应该是1
--如果查询时间为 2012-3-3 11:25, n=1,那么返回值应该是 3
--如果查询时间为 2013-3-4 2:15,n=1返回值应该是3
--如果查询时间是2012-3-4 3:50,n=1,返回值应该是4

为什么年度有2012,2013,2018,表中也只能根据当天和第二天来判断吗?
anlianganl 2012-03-03
  • 打赏
  • 举报
回复
这是员工打卡考勤吧?
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
树人的代码这一点没有通过。
dateadd(dd,1,c2)

错误是:
消息 9810,级别 16,状态 1,第 3 行
数据类型 time 的日期函数 dateadd 不支持日期部分 day。

不过我只改了一点就可以了。用了上面的代码。

非常感谢各位
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
declare @dt datetime
set @dt='2013-3-3 4:00'
select value
from(
select id,0 as f1,c1,0 as f2,c2,n,1 as value from test
union all
select id,0,c2,0,c3,n,2 from test
union all
select id,f1,g1,f2,g2,n,3 from test
union all
select id,f2,g2,f3,g3,n,4 from test
) t
where datediff(mi,case when f1=0 and f2=1 then dateadd(dd,1,convert(varchar(10),c2,120)) else c2 end,convert(varchar(5),@dt,108))<=0
and datediff(mi,c1,case when f1=0 and f2=1 then dateadd(dd,1,convert(varchar(5),@dt,108)) else convert(varchar(5),@dt,108) end)>=0
and n=3
百年树人 2012-03-03
  • 打赏
  • 举报
回复
declare @dt datetime
set @dt='2013-3-3 4:00'
select value
from(
select id,0 as f1,c1,0 as f2,c2,n,1 as value from test
union all
select id,0,c2,0,c3,n,2 from test
union all
select id,f1,g1,f2,g2,n,3 from test
union all
select id,f2,g2,f3,g3,n,4 from test
) t
where datediff(mi,case when f1=0 and f2=1 then dateadd(dd,1,c2) else c2 end,convert(varchar(5),@dt,108))<=0
and datediff(mi,c1,case when f1=0 and f2=1 then dateadd(dd,1,convert(varchar(5),@dt,108)) else convert(varchar(5),@dt,108) end)>=0
and n=3

/**
value
-----------
4

(1 行受影响)
**/
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
如上面,查询时间是 4:00 ,n=3时, 时间表示的是当天23:30之后,第二天5点前,所以返回值应该是 4
WeiWY 2012-03-03
  • 打赏
  • 举报
回复
再加几条测试用例
insert into Test(id,c1,c2,c3,f1,g1,f2,g2,f3,g3,n)
select 7,'7:15','8:05','8:20',0,'11:20',0,'11:40',0,'12:50',3 union all
select 8,'12:30','13:35','13:59',0,'17:10',0,'17:30',0,'17:59',3 union all
select 9,'20:10','20:30','20:59',0,'22:10',0,'23:30',1,'5:00',3

如上面,查询时间是 4:00时,n=3时, 时间应该是 在今天23:30之后,第二天5点钱,所有返回值是 4
加载更多回复(3)
SQL2012软件简介 SQL查询器流传着很多软件,但相信此软件会有它的特别之处,本软件是经多名工程师测试。功能以及效率是有见证。本软件体积优势非常轻巧功能强大,1.4M的大小携带方便。如有建议请发邮件到75934092@qq.com,谢谢你的宝贵建议。话不多说,请看以下功能介绍! 特色功能: (一) 线程查询数据,可看查询结果完成的进度。 (二) 语句编辑强大的语法自动填充功能,可快速填充表或字段 (三) EXCEL导出后台完成。可选中语句导出EXCEL内容! 一、主功能概述 1.shift+空格 内容自动填充 2.预计表总记录数 3.预计表前N行记录 4.生成表字段以豆号分割 字段1,字段2.... 可自定义换行数量 5.显示表的详细信息,字段长度。可直接在查询结果中显示。 6.对查询内容导出EXCEL,高级结果可快速导出。后台处理不会卡程序。在导出大量的数据时可以操作其他操作。软件在导出完成后会检查EXCEL的行数完整性。可以EXCEL信息中查阅。 7.高级结果可拖放分组统计内容 8.对查询结果生成插入语句。可方便夸平台的数据插入 9.查询结果中快速定位查找内容字段位置,可模糊查找 10.可执行存储过程。执行过程中会在对应的GO中报错。可以得知是那行的错误 11.ctrl+1~5 的快捷语句记录。在使用过程中可以按对应的快捷键,在语句编号窗口中插入。临时代码记录10条。可方便用户临时记录编号语句。 12.直接对内容生成IN语句方便查询。 13.快速查询数据库中的表信息,快速查询对应的的字段,可直接筛选想要的字段内容。此功能可让使用者快速知道对应的字段类型! 14.可直接对字段列表的字段进行操作。插入,修改,删除!! 15.查询结果可以直接显示数据库的二进制图片内容 16.排量更新后台,此功能可以对当你手上有大量后台的时候不需要一个个去打开执行,只需要插入到软件的批量更新窗口中执行。执行成功会OK,编辑后再执行,直到全部OK。Ok过的不需重量执行放心! 17.文件传送。可以利用这软件传送文件。如果两个软件连到同一个SQL服务器上。此两个电脑可以互传文件了! 18.EXCEL导入数据库。可以把对应的EXCEL导入到数据库中。EXCEL头为列名。表名定义 19.远程复制,粘贴文件! 20.SQL中实用的语句帮助文档! 二、辅助功能 1.对IP或域名进行端口测试 2.获取当前外网IP地址 3.直接远程连接当前连接IP 4.网整测试当前连接的情况 注:本软件使用次数为10次,因本软件需要大家的建议。功能还在继续完善中,所以免费取得注册码!需要请发邮件或加QQ联系作者。多谢大家的支持! 联系方式:75934092@qq.com
SQL2012软件简介 SQL查询器流传着很多软件,但相信此软件会有它的特别之处,本软件是经多名工程师测试。功能以及效率是有见证。本软件体积优势非常轻巧功能强大,1.4M的大小携带方便。如有建议请发邮件到75934092@qq.com,谢谢你的宝贵建议。话不多说,请看以下功能介绍! 特色功能: (一) 线程查询数据,可看查询结果完成的进度。 (二) 语句编辑强大的语法自动填充功能,可快速填充表或字段 (三) EXCEL导出后台完成。可选中语句导出EXCEL内容! V1.0.0.138 本版本调整了: 1. 汇总后,表头分组统计选择字段显示不正常 2. 汇总后,复制列名时复制不正常 3. 拖动语句编辑时,字体错位的问题 4. TOP查询时,进度不正确 5. 关联查询行数错误的问题 6. 数据库选项的排序问题 7. 临时记录的打开按钮位置 新增功能: 1. 界面大量调整把所有右击出来的菜单都显示在对应的功能位置上,操作上来更新顺手 2. 高级查询的窗口全部显示置前,整理排序窗口 3. 主查询窗口的求和功能和排序功能 4. 高级查询加入,可筛选,可分组统计。可汇总 5. 语句编辑框中直接导出查询结果到EXCEL 一、主功能概述 1.shift+空格 内容自动填充 2.预计表总记录数 3.预计表前N行记录 4.生成表字段以豆号分割 字段1,字段2.... 可自定义换行数量 5.显示表的详细信息,字段长度。可直接在查询结果中显示。 6.对查询内容导出EXCEL,高级结果可快速导出。后台处理不会卡程序。在导出大量的数据时可以操作其他操作。软件在导出完成后会检查EXCEL的行数完整性。可以EXCEL信息中查阅。 7.高级结果可拖放分组统计内容 8.对查询结果生成插入语句。可方便夸平台的数据插入 9.查询结果中快速定位查找内容字段位置,可模糊查找 10.可执行存储过程。执行过程中会在对应的GO中报错。可以得知是那行的错误 11.ctrl+1~5 的快捷语句记录。在使用过程中可以按对应的快捷键,在语句编号窗口中插入。临时代码记录10条。可方便用户临时记录编号语句。 12.直接对内容生成IN语句方便查询。 13.快速查询数据库中的表信息,快速查询对应的的字段,可直接筛选想要的字段内容。此功能可让使用者快速知道对应的字段类型! 14.可直接对字段列表的字段进行操作。插入,修改,删除!! 15.查询结果可以直接显示数据库的二进制图片内容 16.排量更新后台,此功能可以对当你手上有大量后台的时候不需要一个个去打开执行,只需要插入到软件的批量更新窗口中执行。执行成功会OK,编辑后再执行,直到全部OK。Ok过的不需重量执行放心! 17.文件传送。可以利用这软件传送文件。如果两个软件连到同一个SQL服务器上。此两个电脑可以互传文件了! 18.EXCEL导入数据库。可以把对应的EXCEL导入到数据库中。EXCEL头为列名。表名定义 19.远程复制,粘贴文件! 20.SQL中实用的语句帮助文档! 二、辅助功能 1.对IP或域名进行端口测试 2.获取当前外网IP地址 3.直接远程连接当前连接IP 4.网整测试当前连接的情况 注:本软件使用次数为10次,因本软件需要大家的建议。功能还在继续完善中,所以免费取得注册码!需要请发邮件或加QQ联系作者。多谢大家的支持! 联系方式:75934092@qq.com 或可直接加QQ:75934092 510567321
SQL2012软件简介 SQL查询器流传着很多软件,但相信此软件会有它的特别之处,本软件是经多名工程师测试。功能以及效率是有见证。本软件体积优势非常轻巧功能强大,1.7M的大小携带方便。如有建议请发邮件到75934092@qq.com,谢谢你的宝贵建议。话不多说,请看以下功能介绍! 特色功能: (一) 线程查询数据,可看查询结果完成的进度。 (二) 语句编辑强大的语法自动填充功能,可快速填充表或字段 (三) EXCEL导出后台完成。可选中语句导出EXCEL内容! (四) 自带远程桌面控制功能 (五) 自定义保存二进制数据内容成文件 (六) 查询结果可直接显示字段类型等信息 V2.0.0.0 本版本调整了: 1. 启动程序更快 2. 加入远程桌面远程控制功能 3. 加入格式化JSON功能 4. 加入查询结果二进制内容导批量导出成文件 5. 查询显示中可显示对应的字段类型及长度等信息 新增功能: 1. 界面大量调整把所有右击出来的菜单都显示在对应的功能位置上,操作上来更新顺手 2. 高级查询的窗口全部显示置前,整理排序窗口 3. 主查询窗口的求和功能和排序功能 4. 高级查询加入,可筛选,可分组统计。可汇总 5. 语句编辑框中直接导出查询结果到EXCEL 一、主功能概述 1.F8,F9 内容自动填充 2.预计表总记录数 3.预计表前N行记录 4.生成表字段以豆号分割 字段1,字段2....可自定义换行数量 5.显示表的详细信息,字段长度。可直接在查询结果中显示。 6.对查询内容导出EXCEL,高级结果可快速导出。后台处理不会卡程序。在导出大量的数据时可以操作其他操作。软件在导出完成后会检查EXCEL的行数完整性。可以EXCEL信息中查阅。 7.高级结果可拖放分组统计内容 8.对查询结果生成插入语句。可方便夸平台的数据插入 9.查询结果中快速定位查找内容字段位置,可模糊查找 10.可执行存储过程。执行过程中会在对应的GO中报错。可以得知是那行的错误 11.ctrl+1~5 的快捷语句记录。在使用过程中可以按对应的快捷键,在语句编号窗口中插入。临时代码记录10条。可方便用户临时记录编号语句。 12.直接对内容生成IN语句方便查询。 13.快速查询数据库中的表信息,快速查询对应的的字段,可直接筛选想要的字段内容。此功能可让使用者快速知道对应的字段类型! 14.可直接对字段列表的字段进行操作。插入,修改,删除!! 15.查询结果可以直接显示数据库的二进制图片内容 16.排量更新后台,此功能可以对当你手上有大量后台的时候不需要一个个去打开执行,只需要插入到软件的批量更新窗口中执行。执行成功会OK,编辑后再执行,直到全部OK。Ok过的不需重量执行放心! 17.文件传送。可以利用这软件传送文件。如果两个软件连到同一个SQL服务器上。此两个电脑可以互传文件了! 18.EXCEL导入数据库。可以把对应的EXCEL导入到数据库中。EXCEL头为列名。表名定义 19.远程复制,粘贴文件! 20.SQL中实用的语句帮助文档! 二、辅助功能 1.对IP或域名进行端口测试 2.获取当前外网IP地址 3.直接远程连接当前连接IP 4.网整测试当前连接的情况 5.格式化JSON格式内容 注:本软件使用次数为10次,因本软件需要大家的建议。功能还在继续完善中,所以免费取得注册码!需要请发邮件或加QQ联系作者。多谢大家的支持! 联系方式:75934092@qq.com或可直接加QQ:75934092 510567321

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧