wendu表
id Tempe datetime
1 12 2010-04-24 07:10:00
2 13 2010-04-24 08:10:00
3 15 2010-04-24 13:10:00
4 16 2010-04-24 14:10:00
我想查询出一下结果
上午 下午
Tempe datetime Tempe datetime
12 2010-04-24 07:10:00 15 2010-04-24 13:10:00
13 2010-04-24 08:10:00 16 2010-04-24 14:10:00
如何用SQL语句写,请各位高手帮帮忙????
id Tempe datetime
1 12 2010-04-24 07:10:00
2 13 2010-04-24 08:10:00
3 15 2010-04-24 13:10:00
4 16 2010-04-24 14:10:00
我想查询出一下结果
上午 下午
Tempe datetime Tempe datetime
12 2010-04-24 07:10:00 15 2010-04-24 13:10:00
13 2010-04-24 08:10:00 16 2010-04-24 14:10:00
如何用SQL语句写,请各位高手帮帮忙????
解决方案 »
- SQLServer2005中获取用户操作数据库表的日志?
- 请问SSIS包作业执行失败的问题
- 任务管理器中发现“sqlservr.exe”进程占内存800多兆,什么原因?
- 服务器上的两个数据库,想在B数据库中新建立与A数据库中定义完全相同的多个表怎么办?
- 导入XLS文件时如何读取本地而非服务器的文件
- 怎么设置money字段的小数位数?
- 求一SQL问题答案!!!!!
- 500分急救:Log Explorer 4.2.1 版可以还原sql2005数据吗 哪位兄弟有啊,可下载链接也行
- 忘记SQL SERVER的SA口令怎么办,有没有什么解决方法?
- 用什么方法可以判断SQL语句执行是否成功
- 有点难度的sql,不知道如何写
- SQL 2000中创建视图问题和not exists语句的分析(急求!!)
-- Author : htl258(Tony)
-- Date : 2010-04-27 10:19:26
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:wenduIF NOT OBJECT_ID('[wendu]') IS NULL
DROP TABLE [wendu]
GO
CREATE TABLE [wendu]([id] INT,[Tempe] INT,[datetime] DATETIME)
INSERT [wendu]
SELECT 1,12,N'2010-04-24 07:10:00' UNION ALL
SELECT 2,13,N'2010-04-24 08:10:00' UNION ALL
SELECT 3,15,N'2010-04-24 13:10:00' UNION ALL
SELECT 4,16,N'2010-04-24 14:10:00'
GO
--SELECT * FROM [wendu]-->SQL查询如下:
;with t as
(
select rn=ROW_NUMBER()over(PARTITION by case when convert(char(5),[datetime],8)<'12:00' then 1 else 2 end order by getdate())
,*
from wendu
)
select max(case when convert(char(5),[datetime],8)<'12:00' then [Tempe] else '' end) Tempe,
max(case when convert(char(5),[datetime],8)<'12:00' then [datetime] else '' end) [datetime],
max(case when convert(char(5),[datetime],8)>='12:00' then [Tempe] else '' end) Tempe,
max(case when convert(char(5),[datetime],8)>='12:00' then [datetime] else '' end) [datetime]
from t
group by rn
/*
Tempe datetime Tempe datetime
----------- ----------------------- ----------- -----------------------
12 2010-04-24 07:10:00.000 15 2010-04-24 13:10:00.000
13 2010-04-24 08:10:00.000 16 2010-04-24 14:10:00.000(2 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-27 10:19:26
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:wenduIF NOT OBJECT_ID('[wendu]') IS NULL
DROP TABLE [wendu]
GO
CREATE TABLE [wendu]([id] INT,[Tempe] INT,[datetime] DATETIME)
INSERT [wendu]
SELECT 1,12,N'2010-04-24 07:10:00' UNION ALL
SELECT 2,13,N'2010-04-24 08:10:00' UNION ALL
SELECT 3,15,N'2010-04-24 13:10:00' UNION ALL
SELECT 4,16,N'2010-04-24 14:10:00'
GO
--SELECT * FROM [wendu]-->SQL查询如下:
;with t as
(
select rn=ROW_NUMBER()over(PARTITION by case when convert(char(5),[datetime],8)<'12:00' then 1 else 2 end order by getdate())
,*,flag=case when convert(char(5),[datetime],8)<'12:00' then 1 else 2 end
from wendu
)
select max(case flag when 1 then [Tempe] else '' end) Tempe,
max(case flag when 1 then [datetime] else '' end) [datetime],
max(case flag when 2 then [Tempe] else '' end) Tempe,
max(case flag when 2 then [datetime] else '' end) [datetime]
from t
group by rn
/*
Tempe datetime Tempe datetime
----------- ----------------------- ----------- -----------------------
12 2010-04-24 07:10:00.000 15 2010-04-24 13:10:00.000
13 2010-04-24 08:10:00.000 16 2010-04-24 14:10:00.000(2 行受影响)
*/这样会快点
create table #tb(id int,Tempe int,[datetime] datetime)
insert #tb select 1 ,12 ,'2010-04-24 07:10:00'
insert #tb select 2 ,13 ,'2010-04-24 08:10:00'
insert #tb select 3 ,15 ,'2010-04-24 13:10:00'
insert #tb select 4 ,16 ,'2010-04-24 14:10:00'select max(case when id%2=1 then Tempe end) as Tempe1,
max(case when id%2=1 then [datetime] end) as [datetime1],
max(case when id%2=0 then Tempe end) as Tempe2 ,
max(case when id%2=0 then [datetime] end) as [datetime2]
from #tbgroup by (id+1)/2
Tempe1 datetime1 Tempe2 datetime2
----------- ----------------------- ----------- -----------------------
12 2010-04-24 07:10:00.000 13 2010-04-24 08:10:00.000
15 2010-04-24 13:10:00.000 16 2010-04-24 14:10:00.000
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)
if object_id('[wendu]') is not null drop table [wendu]
create table [wendu]([id] int,[Tempe] int,[datetime] datetime)
go
insert [wendu]
select 1,12,'2010-04-24 07:10:00' union all
select 2,13,'2010-04-24 08:10:00' union all
select 3,15,'2010-04-24 13:10:00' union all
select 4,16,'2010-04-24 14:10:00'select
t.tempe,t.datetime,r.tempe,r.datetime
from
(
select row_number() over(order by id) as id,tempe,datetime from [wendu]
where convert(varchar(8),datetime,108) >= '00;00:00' and
convert(varchar(8),datetime,108) <= '12:00:00'
) t join
(
select row_number() over(order by id) as id,tempe,datetime from [wendu]
where convert(varchar(8),datetime,108) > '12;00:00' and
convert(varchar(8),datetime,108) <= '24:00:00'
) r
on t.id = r.id
---------------------------
tempe datetime tempe datetime
----------- ----------------------- ----------- -----------------------
12 2010-04-24 07:10:00.000 15 2010-04-24 13:10:00.000
13 2010-04-24 08:10:00.000 16 2010-04-24 14:10:00.000(2 行受影响)
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
Tempe int,
datetime datetime
)
go
--插入测试数据
insert into tb select 1,12,'2010-04-24 07:10:00'
union all select 2,13,'2010-04-24 08:10:00'
union all select 3,15,'2010-04-24 13:10:00'
union all select 4,16,'2010-04-24 14:10:00'
go
--代码实现;with t as(
select idd=row_number()over(partition by WuBie order by getdate()),*
from (select WuBie=case when datepart(hh,datetime)>=0
and datepart(hh,datetime)<12 then '上午' else '下午' end,*
from tb)b)
select a.WuBie,a.Tempe,a.datetime,b.WuBie,b.Tempe,b.datetime
from t a join t b on a.idd=b.idd and a.id<>b.id and a.WuBie='上午'/*测试结果WuBie Tempe datetime WuBie Tempe datetime
-----------------------------------------------------------------------------
上午 12 2010-04-24 07:10:00.000 下午 15 2010-04-24 13:10:00.000
上午 13 2010-04-24 08:10:00.000 下午 16 2010-04-24 14:10:00.000(2 行受影响)
*/
insert #tb select 1 ,12 ,'2010-04-24 07:10:00'
insert #tb select 2 ,13 ,'2010-04-24 08:10:00'
insert #tb select 4 ,15 ,'2010-04-24 13:10:00'
insert #tb select 5 ,16 ,'2010-04-24 14:10:00'select max(case when id%2=1 then Tempe end) as Tempe1,
max(case when id%2=1 then [datetime] end) as [datetime1],
max(case when id%2=0 then Tempe end) as Tempe2 ,
max(case when id%2=0 then [datetime] end) as [datetime2]
from #tbgroup by (id+1)/2
/*
Tempe1 datetime1 Tempe2 datetime2
----------- ----------------------- ----------- -----------------------
12 2010-04-24 07:10:00.000 13 2010-04-24 08:10:00.000
NULL NULL 15 2010-04-24 13:10:00.000
16 2010-04-24 14:10:00.000 NULL NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(3 行受影响)
*/
具体数字忘了,可以把时间转换成为带 am,pm的时间可以,然后再根据时间就可以查找了