写一条sql语句将12:00:00之前分为一列AM,12:00:00之后为pm
time
2010-06-06 8:00:00
2010-06-06 12:00:00
2010-06-07 8:00:00
2010-06-07 12:00:00
2010-06-08 7:00:00
2010-06-08 13:00:00查询以后为
am pm
2010-06-06 8:00:00 2010-06-06 12:00:00
2010-06-07 8:00:00 2010-06-07 12:00:00
2010-06-08 7:00:00 2010-06-08 13:00:00
time
2010-06-06 8:00:00
2010-06-06 12:00:00
2010-06-07 8:00:00
2010-06-07 12:00:00
2010-06-08 7:00:00
2010-06-08 13:00:00查询以后为
am pm
2010-06-06 8:00:00 2010-06-06 12:00:00
2010-06-07 8:00:00 2010-06-07 12:00:00
2010-06-08 7:00:00 2010-06-08 13:00:00
解决方案 »
- 紧急求助如何写一条SQL命令
- 按月份统计
- 出错在哪?已有打开的与此命令相关联的 DataReader,必须首先将它关闭
- 一句比较复杂的SQL语句不知如何写?又有字符合并(有两个字段),一个字段已经会了,又有数值相加!具体见内!谢谢!
- 如何恢复sybase备份数据
- 跨数据库连表
- 在存储过程中,动态拼 Sql ,Sql 超过 8000 怎么办
- 急急急!!!数据库置疑(恳请邹健大哥和各位高手帮忙)
- 各位大虾,帮帮我吧!!关于重装SQL Server 的问题。急,在线等待,一经解决,马上给分!!
- 触发器能实现吗???
- 存储过程的问题,请大侠们帮忙
- 在order by 里写 case when 有啥作用呢
where datediff(s,time,convert(varchar(11),time,120)+'12:00:00')>0
max(case when datepart(hh,time)<12 then time else null end) am,
max(case when datepart(hh,time)>=12 then time else null end) pm
from tb group by convert(varchar(10),time,23)
full join (select time from table where datepart(hh,time)>=12) on convert(varchar(10),a.time,120)=convert(varchar(10),b.time,120)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([time] datetime)
insert [tb]
select '2010-06-06 8:00:00' union all
select '2010-06-06 12:00:00' union all
select '2010-06-07 8:00:00' union all
select '2010-06-07 12:00:00' union all
select '2010-06-08 7:00:00' union all
select '2010-06-08 13:00:00'
---查询---
select
am=max(case when convert(varchar(8),[time],108)<'12:00:00' then [time] end),
pm=max(case when convert(varchar(8),[time],108)>='12:00:00' then [time] end)
from tb
group by convert(varchar(10),[time],120)---结果---
am pm
----------------------- -----------------------
2010-06-06 08:00:00.000 2010-06-06 12:00:00.000
2010-06-07 08:00:00.000 2010-06-07 12:00:00.000
2010-06-08 07:00:00.000 2010-06-08 13:00:00.000
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
where datediff(s,time,convert(varchar(11),time,120)+'12:00:00')>0
create table tb (date_time datetime)
insert into tb
select '2010-06-06 8:00:00' union all
select '2010-06-06 12:00:00' union all
select '2010-06-07 8:00:00' union all
select '2010-06-07 12:00:00' union all
select '2010-06-08 7:00:00' union all
select '2010-06-08 13:00:00';with t
as
(
select (case when CONVERT(varchar(12) , date_time, 108 )<'12:00:00' then date_time else null end) AM,
(case when CONVERT(varchar(12) , date_time, 108 )>='12:00:00' then date_time else null end) PM
from tb
)
select AM,PM
from (select am
from t
where AM is not null) a full join (select pm
from t
where PM is not null)b
on CONVERT(varchar(10),AM,120)=CONVERT(varchar(10),pM,120)
AM PM
2010-06-06 08:00:00.000 2010-06-06 12:00:00.000
2010-06-07 08:00:00.000 2010-06-07 12:00:00.000
2010-06-08 07:00:00.000 2010-06-08 13:00:00.000
create table #tb(dt datetime)
insert into #tb
select '2010-06-06 8:00:00' union all
select '2010-06-06 12:00:00' union all
select '2010-06-07 8:00:00' union all
select '2010-06-07 12:00:00' union all
select '2010-06-08 7:00:00' union all
select '2010-06-08 13:00:00' select am,pm
from (select convert(varchar(10),dt,121)+' '+case when convert(varchar(10),dt,108)<'12:00:00' then convert(varchar(10),dt,108) end am
from #tb
where convert(varchar(10),dt,121)+' '+case when convert(varchar(10),dt,108)<'12:00:00' then convert(varchar(10),dt,108) end is not null) a,
(select convert(varchar(10),dt,121)+' '+case when convert(varchar(10),dt,108)>='12:00:00' then convert(varchar(10),dt,108) end pm
from #tb
where convert(varchar(10),dt,121)+' '+case when convert(varchar(10),dt,108)>='12:00:00' then convert(varchar(10),dt,108) end is not null) b
where convert(char(10),a.am,23)=convert(char(10),b.pm,23)
am pm
2010-06-06 08:00:00 2010-06-06 12:00:00
2010-06-07 08:00:00 2010-06-07 12:00:00
2010-06-08 07:00:00 2010-06-08 13:00:00