有一个表A：编号-ID(char)，读卡时间readtime(Datetime)（包含进/出记录），读头地址-did(integer)(1~8:其中奇数为进们记录、偶数为出门记录)，要求：查询每人每天最早进们及最晚出门记录。查询结果需要字段：编号、日期、最早进们、最晚出门。小弟已经分别查出两个表，但不知如何合并（union,*join??）？？SQL:
//最早进们记录
select id,date(A.readtime) as indate,min(A.readtime)
from A A
where did in ('1','3','5','7')
group by id,indate
//最晚出们记录
select id,date(A.readtime) as indate,max(A.readtime)
from A A
where did in ('2','4','6','8')
group by id,indate
望各位高手赐教！！！
//最早进们记录
select id,date(A.readtime) as indate,min(A.readtime)
from A A
where did in ('1','3','5','7')
group by id,indate
//最晚出们记录
select id,date(A.readtime) as indate,max(A.readtime)
from A A
where did in ('2','4','6','8')
group by id,indate
望各位高手赐教！！！
where `IndexNo`="1"
union
SELECT `CataName`, `IndexNo`FROM `catainfo`
where `IndexNo`="2"向上面的语句就完全可以在mysql上通过。
用UNION 试过，失败
select id,A.readtime as indate,min(A.readtime)
into tmpTable
from A
where did in ('1','3','5','7')
group by id,indate
insert into tmpTable
select id,date(A.readtime) as indate,max(A.readtime)
from A A
where did in ('2','4','6','8')
group by id,indate
我今天测了一下午都没实现，主要再复杂查询这方面没经验。
大家多指导！！！
用临时表有个问题：查询的数据无法保存。我采用的是静态查询，直接在TADOQUERY。SQL中写。
另外，主要想用临时表以外的方法。
from
(
select ID
,Min(readtime) as 最早进门
from a
where did in(1,3,5,7)
group by ID
) as aa
left join
(select ID,Max(readtime) as 时间
from a
where did in(2,4,6,8)
group by ID) as bb on (aa.ID=bb.ID)
看看是不是这样
select aa.ID,aa.indate,aa.最早进门,bb.最晚出门
from
(
select ID，Date(A.readtime) as indate
,Min(readtime) as 最早进门
from a
where did in(1,3,5,7)
group by ID
) as aa
left join
(select ID,Date(A.readtime) as outdate,Max(readtime) as 最晚出门
from a
where did in(2,4,6,8)
group by ID) as bb on (aa.ID=bb.ID and aa.indate=bb.outdate)
to: everyone我最终目的：
“查询结果需要字段：编号、日期、最早进们、最晚出门”:
000002 2003-10-31 10:20:30 1（Ex:3个字段，n条记录,1,3-in,2,4-out）
000002 2003-10-31 12:10:12 4
000002 2003-10-31 15:10:08 3
000002 2003-10-31 17:30:20 2
...
将以上》4条的记录最终结果如下：(end:3个字段，n条记录)
000002 2003-10-31 2003-10-31 10:20:30
000002 2003-10-31 2003-10-31 17:30:20
系统提示语法错误，但我将‘’LEFT JOIN‘前后分开单独执行没问题呀？！
000002 2003-10-31 2003-10-31 10:20:30 2003-10-31 17:30:20
我整理了这个语句，在SqlServer2000运行没问提：
select aa.ID
,cast(aa.进门时间 as varchar(10)) as 日期
,aa.进门时间,bb.出门时间 as 出门时间
from
(
select ID
,Min(readtime) as 进门时间
from a
where did in(1,3,5,7)
group by ID--,cast(datepart(Year,ReadTime) as Varchar(4))+'-'+cast(datepart(month,ReadTime) as Varchar(4))+'-'+cast(datepart(day,ReadTime) as Varchar(4))) as aa
left join
(select ID,Max(readtime) as 出门时间
from a
where did in(2,4,6,8)
group by ID) as bb on (aa.ID=bb.ID and cast(aa.进门时间 as varchar(10))=cast(bb.出门时间 as varchar(10)))你的Date函数在我这里总是报错：不是可以识别的 函数名，所以用 cast(bb.出门时间 as varchar(10)) 临时代替 见谅!
to:：NewPrince(Mack狼)
try for n ,好象是 SQL Anywhere 不支持嵌套查询-中奖啦，感激！！！看来数据库要更换。
另外，能否告诉小弟你的E_mail，向您多学习...？？！my E_mail:([email protected]) and in shanghai.
希望以后多多关照！！！别忘了你的E_mail。
是 SQL Anywhere 不支持嵌套查询