select distinct CardNo,date,time,
sb_time1,xb_time1
from T1 我想得到 CardNo,date,time 这三个字段不能有同样的值,同时time <12 值内都不能有重复如:00001 2009-06-01 09:00
00001 2009-06-01 09:10
00001 2009-06-01 18:00
00001 2009-06-02 09:00
00001 2009-06-02 09:10
00001 2009-06-02 18:00
我要得到的查询结果是如下:(也就是去得到时间<12点,同时是最早的一个上班时间) 00001 2009-06-01 09:00
00001 2009-06-01 18:00
00001 2009-06-02 09:00
00001 2009-06-02 18:00
sb_time1,xb_time1
from T1 我想得到 CardNo,date,time 这三个字段不能有同样的值,同时time <12 值内都不能有重复如:00001 2009-06-01 09:00
00001 2009-06-01 09:10
00001 2009-06-01 18:00
00001 2009-06-02 09:00
00001 2009-06-02 09:10
00001 2009-06-02 18:00
我要得到的查询结果是如下:(也就是去得到时间<12点,同时是最早的一个上班时间) 00001 2009-06-01 09:00
00001 2009-06-01 18:00
00001 2009-06-02 09:00
00001 2009-06-02 18:00
解决方案 »
- 【高分】Java 连接数据库SqlServer 2005 ,错误:java.sql.SQLException: I/O Error: Unknown packet type 0x44
- 怎么用代码从多个表中读取字段到新的表
- libin_ftsafe (子陌红尘),happyflystone (无枪狙击手)进来接分,为了再次感谢你们
- 怎么样用函数或其它办法实现以下功能?
- 表里面有两个主键,求分页存储过程,或者类似Oracle里面的 RowID 字段。谢谢。
- 如此简单SQL我居然不会了
- datename函数在SQL SERVER 中使用没问题,我如果用ACCESS数据库,应该用什么函数来代替datename呢?
- 按时间范围分组
- SQL server中,如何将getdate()所得到的值,只取日期部分呢?
- sql server 2000 客户端连接配置的问题
- 怎样外键关联两个表?
- 每隔一分钟添加2万条数据,速度问题,和已存在问题
from T1 t
where time=(select min(time) from T1 where CardNo=t.CardNo)
sb_time1,xb_time1
from T1 where...
where not exists(select 1 from tb where cardno=t.cardno and time<t.time)
from tb t
where not exists(select 1 from tb where cardno=t.cardno and t.date=date and time <t.time)
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-20 10:50:27
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([CardNo] int,[date] nvarchar(10),[time] nvarchar(5))
Insert tb
Select 00001,'2009-06-01','09:00' union all
Select 00001,'2009-06-01','09:10' union all
Select 00001,'2009-06-01','18:00' union all
Select 00001,'2009-06-02','09:00' union all
Select 00001,'2009-06-02','09:10' union all
Select 00001,'2009-06-02','18:00'
Go
--Select * from tb-->SQL查询如下:
select *
from(
select * from tb t where time=(select MIN(time) from tb where [CardNo]=t.[CardNo] and [date]=t.[date] and time<='12:00')
union all
select * from tb t where time=(select MIN(time) from tb where [CardNo]=t.[CardNo] and [date]=t.[date] and time>'12:00')
) as t
order by 1,2,3
/*
CardNo date time
----------- ---------- -----
1 2009-06-01 09:00
1 2009-06-01 18:00
1 2009-06-02 09:00
1 2009-06-02 18:00(4 行受影响)
*/
*
from
tb t
where
not exists(select 1 from tb where cardno=t.cardno and time <t.time)
and
datepart(hh,[time])<12
*
from
(select
*
from
tb t
where
not exists(select 1 from tb where cardno=t.cardno and time <t.time)
and
datepart(hh,[time])<=12
union all
select
*
from
tb t
where
not exists(select 1 from tb where cardno=t.cardno and time <t.time)
and
datepart(hh,[time])>12) t
order by 1,2,3
insert into tb select '00001', '2009-06-01', '09:00'
union all select '00001', '2009-06-01' , '09:10'
union all select '00001', '2009-06-01' , '09:20'
union all select '00001', '2009-06-01', '13:00'
union all select '00001', '2009-06-01', '15:00'
union all select '00001', '2009-06-01', '18:00'
union all select '00001', '2009-06-02', '09:00'
union all select '00001', '2009-06-02', '09:10'
union all select '00001', '2009-06-02', '18:00' select * from tb a
where not exists (select 1 from tb where [time]<a.[time])
or left([time],2)>12
/*
CardNo date time
---------- ----------------------- ----------
00001 2009-06-01 00:00:00.000 09:00
00001 2009-06-01 00:00:00.000 13:00
00001 2009-06-01 00:00:00.000 15:00
00001 2009-06-01 00:00:00.000 18:00
00001 2009-06-02 00:00:00.000 09:00
00001 2009-06-02 00:00:00.000 18:00(6 行受影响)
*/
select *
from tb t
where not exists(select 1 from tb where cardno=t.cardno and t.date=date and time <t.time)那如上的语句 00001 2009-06-01 09:00
00001 2009-06-01 09:10
这两条记录都出不来了,
我要得到是记录 00001 2009-06-01 09:00