select cardno,max(iotime),type from table where type = 'in' group by cardno,type order by cardno
解决方案 »
- 请教大家,如何检查并整理SQL 2000数据库碎片?
- 使用触发器,提示对象名‘****’无效,怎么回事?
- 如何按价格分时间段求和?求一SQL语句.
- 急!!!请教在SQL SERVER中如何判断一个表中某一字段中包含另一个字段的内容?
- sql server2005 中的疑惑,急急急,邹大哥有空指教一下
- 我读文件写入数据库的image字段,但是报错,哪位大虾可以仗义相救,先谢了
- 存储过程里如果改表名呢?
- 高手进,MS SQL建库的问题
- VFP数据类型转换函数问题
- 排序难题改错,有生成脚本和代码,求解
- 关于登陆到远程SQL服务器的问题,只是觉得麻烦,有没有好的方法?
- SOS! 链接服务器的问题,救命啊~~ 大哥大姐们
试图执行的查询中不包含作为合计函数一部分的特定表达式 'TYPE' 。
001 13:29:56.000 IN
002 13:26:22.000 IN
003 13:28:01.000 IN
select carno,max(iotime),'IN' as TYPE from table where type='IN' group by carno
insert into temp values('001', #13:20:30#, 'IN')
insert into temp values( '002', #13:25:20#, 'OUT')
insert into temp values( '002', #13:26:22#, 'IN')
insert into temp values( '001', #13:27:30#, 'OUT')
insert into temp values( '003', #13:28:01#, 'IN')
insert into temp values( '001', #13:29:56#, 'IN')
insert into temp values( '001', #13:30:33#, 'OUT')测试
select * from temp
where
exists(select 1 from
(
SELECT cardno,max(iotime) as iotime1 from temp group by cardno
) A where cardno=A.cardno and iotime=A.iotime1
) and type='in'结果
CardNO IOtime TYPE
002 13:26:22 IN
003 13:28:01 IN
002 13:26:22 IN
003 13:28:01 IN
不好意思,你的是对的!
select *
from temp a
where exists(select 1
from ( select cardno,max(iotime) as iotime1
from temp
group by cardno
) b
where a.cardno=b.cardno
and a.iotime=b.iotime1
)
and [type]='IN'
改一下
select b.*
from (select cardno,max(iotime) as iotem
from temp
group by cardno ) a inner join temp b on b.cardno=a.cardno and b.iotime=a.iotime
where [type]='IN'
select a.cardno,a.iotime,#test_1.type
from
(
select cardno,max(iotime) as iotime
from #test_1
group by cardno
) a ,#test_1
where a.cardno=#test_1.cardno and a.iotime=#test_1.iotime and #test_1.type='in'
where
iotime=(select max(iotime) from tmpe
where cardno=t1.cardno) and
type='in'
order by cardno
我试过了,这种方法能行!
insert into t
select '001','13:20:30 ','IN' union all
select ' 002','13:25:20','OUT' union all
select '002','13:26:22','IN' union all
select '001','13:27:30','OUT' union all
select '003','13:28:01','IN' union all
select '001','13:29:56','IN' union all
select '001','13:30:33','OUT'
goselect *
from t t1
where IOtime=(select max(IOtime) from t where t.cardNO=t1.cardNO)
and TYPE='IN'
order by cardNO asc
drop table t
我的方法和楼上的一样啊