有这么一个表
------------------------------
cid ID TIME
1 甲 2007-01-09 8:00
2 甲 2007-01-09 9:10
3 甲 2007-01-09 15:10
4 乙 2007-01-09 8:00
5 乙 2007-01-09 9:10
6 乙 2007-01-09 17:10
7 乙 2007-01-09 18:10
8 丙 2007-01-09 6:10
9 丙 2007-01-09 18:10
10 甲 2007-01-20 9:10
11 甲 2007-01-20 15:10
有如上表
请问各位兄弟 我要想一天内每个ID只出现两次,一次取最早时间一次取最晚时间,剩下的都不要 记录数比较多 不想用游标
请问各位兄弟有没有好的办法
-----------------------------------------------------------------------------
上次发的帖子不带序号列,不好意思啊
------------------------------
cid ID TIME
1 甲 2007-01-09 8:00
2 甲 2007-01-09 9:10
3 甲 2007-01-09 15:10
4 乙 2007-01-09 8:00
5 乙 2007-01-09 9:10
6 乙 2007-01-09 17:10
7 乙 2007-01-09 18:10
8 丙 2007-01-09 6:10
9 丙 2007-01-09 18:10
10 甲 2007-01-20 9:10
11 甲 2007-01-20 15:10
有如上表
请问各位兄弟 我要想一天内每个ID只出现两次,一次取最早时间一次取最晚时间,剩下的都不要 记录数比较多 不想用游标
请问各位兄弟有没有好的办法
-----------------------------------------------------------------------------
上次发的帖子不带序号列,不好意思啊
union all
select * from tb t where not exists(select 1 from tb where id=t.id and TIME>t.TIME)
declare @s table (cid int,ID varchar(2),TIME datetime)
insert into @s
select 1,'甲','2007-01-09 8:00' union all
select 2,'甲','2007-01-09 9:10' union all
select 3,'甲','2007-01-09 15:10' union all
select 4,'乙','2007-01-09 8:00' union all
select 5,'乙','2007-01-09 9:10' union all
select 6,'乙','2007-01-09 17:10' union all
select 7,'乙','2007-01-09 18:10' union all
select 8,'丙','2007-01-09 6:10' union all
select 9,'丙','2007-01-09 18:10' union all
select 10,'甲','2007-01-20 9:10' union all
select 11,'甲','2007-01-20 15:10'select * from @s a
where not exists(select 1 from @s where id=a.id and time>a.time)
or not exists(select 1 from @s where id=a.id and time<a.time)
select * from
(select * from tb t where not exists(select 1 from tb where id=t.id and TIME<t.TIME)
union all
select * from tb t where not exists(select 1 from tb where id=t.id and TIME>t.TIME))t order by t.time
select ID,MAX(TIME),MIN(TIME) from tb group by ID
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-08-06 13:12:01
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (cid INT,ID VARCHAR(2),TIME DATETIME)
INSERT INTO @T
SELECT 1,'甲','2007-01-09 8:00' UNION ALL
SELECT 2,'甲','2007-01-09 9:10' UNION ALL
SELECT 3,'甲','2007-01-09 15:10' UNION ALL
SELECT 4,'乙','2007-01-09 8:00' UNION ALL
SELECT 5,'乙','2007-01-09 9:10' UNION ALL
SELECT 6,'乙','2007-01-09 17:10' UNION ALL
SELECT 7,'乙','2007-01-09 18:10' UNION ALL
SELECT 8,'丙','2007-01-09 6:10' UNION ALL
SELECT 9,'丙','2007-01-09 18:10' UNION ALL
SELECT 10,'甲','2007-01-20 9:10' UNION ALL
SELECT 11,'甲','2007-01-20 15:10'--SQL查询如下:SELECT B.ID,B.[TIME],
rowid=ROW_NUMBER() OVER(ORDER BY cid)
FROM (
SELECT ID,MAX([TIME]) AS maxTime,MIN([TIME]) AS minTIME
FROM @T
GROUP BY ID,CONVERT(VARCHAR(8),[TIME],112)
) AS A
RIGHT JOIN @T AS B
ON A.ID = B.ID AND (B.[TIME] = A.maxTime OR B.[TIME] = A.minTIME)
WHERE A.ID IS NOT NULL
ORDER BY B.ID,B.TIME;
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2009-08-06 13:14:24
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (cid INT,ID VARCHAR(2),TIME DATETIME)
INSERT INTO [tb]
SELECT 1,'甲','2007-01-09 8:00' UNION ALL
SELECT 2,'甲','2007-01-09 9:10' UNION ALL
SELECT 3,'甲','2007-01-09 15:10' UNION ALL
SELECT 4,'乙','2007-01-09 8:00' UNION ALL
SELECT 5,'乙','2007-01-09 9:10' UNION ALL
SELECT 6,'乙','2007-01-09 17:10' UNION ALL
SELECT 7,'乙','2007-01-09 18:10' UNION ALL
SELECT 8,'丙','2007-01-09 6:10' UNION ALL
SELECT 9,'丙','2007-01-09 18:10' UNION ALL
SELECT 10,'甲','2007-01-20 9:10' UNION ALL
SELECT 11,'甲','2007-01-20 15:10'select * from [tb]select * from tb t
where not exists(select 1 from tb where id=t.id and convert(varchar(10),time,120)=convert(varchar(10),t.time,120) and time<t.time)
union all
select * from tb t
where not exists(select 1 from tb where id=t.id and convert(varchar(10),time,120)=convert(varchar(10),t.time,120) and time>t.time)
order by id,time
union all
select max(cid),id,max(TIME) from tb1 group by id order by id