如何查询每天的第一条和最后一条数据

[killsome]

数据如下
kh date8 time8
001 2011/7/14 10：00
001 2011/7/14 11：00
001 2011/7/14 18：00
001 2011/7/15 10：00
002 2011/7/14 9：00
002 2011/7/14 11：00
002 2011/7/14 13：00
002 2011/7/14 15：00
002 2011/7/15 9：00
002 2011/7/15 11：00

取出的数据应该是
kh date8 time8 time9
001 2011/7/14 10：00 18：00
001 2011/7/15 10：00 10：00
002 2011/7/14 9：00 15：00
002 2011/7/15 9：00 11：00


求sql文

[killsome]

[Quote=引用 1 楼 maco_wang 的回复:]

SQL code
select kh,date8,min(time8),max(time8) as time9 from tablename group by kh,date8
[/Quote]

我是从两个表中查询到的上述的数据，这样写好像有点问题

[AcHerat]

[Quote=引用 11 楼 killsome 的回复:]

引用 1 楼 maco_wang 的回复:

SQL code
select kh,date8,min(time8),max(time8) as time9 from tablename group by kh,date8


我是从两个表中查询到的上述的数据，这样写好像有点问题
[/Quote]

楼主把你查询语句贴出来。 看看！

[叶子]

;with cte as
(
--你的查询语句
)
select 
kh,date8=convert(varchar(10),date8,120),
time8=min(convert(varchar(5),time8,108)),
time9 =max(convert(varchar(5),time8,108))
from cte 
group by kh,date8 order by kh

[killsome]

SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b WHERE a.kh=b.gzkh

上面的数据是我自己写的，其实要查询的是编号，姓名，卡号，部门，日期，时间
现在要每人的每天的第一条和最后一条数据

[叶子]

;with maco as 
(
select a.bh,a.xm,a.kh,a.bmmc,b.date8,b.time8 
from sys_employee as a, kaoq_sksj as b where a.kh=b.gzkh
)
select 
kh,date8=convert(varchar(10),date8,120),
time8=min(convert(varchar(5),time8,108)),
time9 =max(convert(varchar(5),time8,108))
from maco 
group by kh,date8 order by kh

[killsome]

;with maco as
这个是什么意思啊？

[cd731107]

SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b WHERE a.kh=b.gzkh
where not exists 
(select 1 from kaoq_sksj  c where b.date8=c.date8 and c.time8<b.time8)
or 
not exists 
(select 1 from kaoq_sksj  c where b.date8=c.date8 and c.time8<b.time8)
 

[AcHerat]

[Quote=引用 16 楼 killsome 的回复:]

;with maco as
这个是什么意思啊？
[/Quote]

这个是05及以上版本特有的用法，相当于一个查询一次的临时表。

[killsome]

我的数据库是access，貌似没有效果

[cd731107]

--上面一个<应改为>
SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b WHERE a.kh=b.gzkh
where not exists 
(select 1 from kaoq_sksj  c where b.date8=c.date8 and c.time8<b.time8)
or 
not exists 
(select 1 from kaoq_sksj  c where b.date8=c.date8 and c.time8>b.time8)

[叶子]

[Quote=引用 19 楼 killsome 的回复:]

我的数据库是access，貌似没有效果
[/Quote]

select kh,date8,time8=min(time8),time9 =max(time8)
from (select a.bh,a.xm,a.kh,a.bmmc,b.date8,b.time8 
from sys_employee as a, kaoq_sksj as b where a.kh=b.gzkh)
aa group by kh,date8 order by kh

[cd731107]

--还少了一个条件
SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b 
WHERE a.kh=b.gzkh
and not exists 
(select 1 from kaoq_sksj  c where c.gzkh=b.gzkh and b.date8=c.date8 and c.time8<b.time8)
or 
not exists 
(select 1 from kaoq_sksj  c where c.gzkh=b.gzkh and b.date8=c.date8 and c.time8>b.time8)

[killsome]

[Quote=引用 21 楼 maco_wang 的回复:]

引用 19 楼 killsome 的回复:

我的数据库是access，貌似没有效果

SQL code

select kh,date8,time8=min(time8),time9 =max(time8)
from (select a.bh,a.xm,a.kh,a.bmmc,b.date8,b.time8
from sys_employee as a, kaoq_sksj as ……
[/Quote]

报错说试图执行的查询中不包含作为聚合函数一部分的特定表达式‘time8=min(time8)’

[killsome]

[Quote=引用 22 楼 cd731107 的回复:]

SQL code
--还少了一个条件
SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b
WHERE a.kh=b.gzkh
and not exists
(select 1 from kaoq_sksj c where c.gzkh=b.gzkh and b.da……
[/Quote]

我查询出来顺序非常的乱，我试着order by 一下，发觉数据多出非常多，好像不正确

[叶子]

[Quote=引用 23 楼 killsome 的回复:]

引用 21 楼 maco_wang 的回复:

引用 19 楼 killsome 的回复:

我的数据库是access，貌似没有效果

SQL code

select kh,date8,time8=min(time8),time9 =max(time8)
from (select a.bh,a.xm,a.kh,a.bmmc,b.date8,b.time8
from sys_……
[/Quote]
access中有些东西和ms-sql server中不同

select kh,date8,min(time8),max(time8)
from (select a.bh,a.xm,a.kh,a.bmmc,b.date8,b.time8 
from sys_employee as a, kaoq_sksj as b where a.kh=b.gzkh)
aa group by kh,date8 order by kh

再试一下

[cd731107]

--对的，要再加一对括号
SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b 
WHERE a.kh=b.gzkh
and 
(not exists 
(select 1 from kaoq_sksj  c where c.gzkh=b.gzkh and b.date8=c.date8 and c.time8<b.time8)
or 
not exists 
(select 1 from kaoq_sksj  c where c.gzkh=b.gzkh and b.date8=c.date8 and c.time8>b.time8))

[killsome]

[Quote=引用 25 楼 maco_wang 的回复:]

引用 23 楼 killsome 的回复:

引用 21 楼 maco_wang 的回复:

引用 19 楼 killsome 的回复:

我的数据库是access，貌似没有效果

SQL code

select kh,date8,time8=min(time8),time9 =max(time8)
from (select a.bh,a.xm,a.kh,a.bmmc,b……
[/Quote]


ok，非常感谢，也谢谢其他人，谢谢。要不我现在就结贴了

[killsome]

[Quote=引用 26 楼 cd731107 的回复:]

SQL code
--对的，要再加一对括号
SELECT a.bh, a.xm, a.kh, a.bmmc,b.date8, b.time8 FROM sys_employee AS a, kaoq_sksj AS b
WHERE a.kh=b.gzkh
and
(not exists
(select 1 from kaoq_sksj c where c.gzkh=b.gzkh and……
[/Quote]

查询出来了，但是第一条数据和最后一条数据 没有整合在一条数据中，我试试看整合一下把

[liangyong1107]

declare @table table (kh varchar(10),date8 datetime)
insert into @table
select '001','2011-7-14 10:00' union all
select '001','2011-7-14 11:00' union all
select '001','2011-7-14 18:00' union all
select '001','2011-7-15 10:00' union all
select '002','2011-7-14 9:00'  union all
select '002','2011-7-14 11:00' union all
select '002','2011-7-14 13:00' union all
select '002','2011-7-14 15:00' union all
select '002','2011-7-15 09:00'  union all
select '002','2011-7-15 11:00'


;with cte as 
(
select *,ROW_NUMBER() OVER(PARTITION BY kh,convert(varchar(10),date8,120) ORDER BY date8 asc) as RN
from @table
),cte2 as 
(
select *,ROW_NUMBER() OVER(PARTITION BY kh,convert(varchar(10),date8,120) ORDER BY date8 desc) as RN
from @table
)
select cte.kh,convert(varchar(10),cte.date8,120) as date8,
convert(varchar(10),cte.date8,108) as time8,
convert(varchar(10),cte2.date8,108) as time9
 from cte inner join cte2 on cte.kh=cte2.kh
and convert(varchar(10),cte.date8,120)=convert(varchar(10),cte2.date8,120)
where cte.RN=1 and cte2.RN=1


输出的结果如下:
001	2011-07-14	10:00:00	18:00:00
001	2011-07-15	10:00:00	10:00:00
002	2011-07-14	09:00:00	15:00:00
002	2011-07-15	09:00:00	11:00:00

[天才在左疯子在右]

很基本的 SQL