求这条sql怎么写?如下描述，我们dba也没有解决

Question

表结构：
create table usecar
（
 carno varchar(50),
listid varchar (100),
starttime datetime,
reachtime datetime,
finishtime datetime,
state
）
其中，state是状态，1表示正常。
现在要查询finishtime为空的数据
finishtime如果为空
表示为正忙状态
下面是的查询结果：
1 粤A63900 20110627101 2011-6-27 10:00:00 2011-6-27 12:00:00 1
2 粤AE6880 20110627001 2011-6-17 13:00:00 2011-6-17 14:00:00 1
3 粤AL5340 20110601103 2011-6-16 14:49:15 2011-6-16 17:30:00 1
4 粤AL5340 20110627001 2011-6-27 9:00:00
5 粤AL5340 20110601100 2011-6-14 18:47:15 2011-6-14 20:00:00 1
现在粤AL5340这个车呢
其实是表示正忙状态
请问
我如何查询才能查出粤AL5340这个车为正忙状态而不死空闲状态？
注意：如果我要查出空闲状态的车的时候，也不能出现粤AL5340这个车，因为该车实际是正忙状态。

Answer 1

select  *  from  usecar where  finishtime  is null 

Answer 2

3楼您好，如果我要查出空闲状态的车，那就是：select  *  from  usecar where  finishtime  is not null
这样就出现了一个问题，就是：正忙和空闲里面都出现了粤AL5340这车，这不就矛盾了？ 

Answer 3

实际的结果应该是粤AL5350这个车处于正忙状态，所以如果我要查空闲的车的时候，粤AL5340不能出现在结果里面

Answer 4

select  *  from  usecar as A
where  finishtime  is not null 
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime  is null)

Answer 5

忙的：
select  *  from  usecar as A
where  finishtime  is not null 
闲的：
select  *  from  usecar as A
where  finishtime  is not null 
and not exists(select 1 from usecar where carno=A.carno and finishtime  is null)

Answer 6

错了，应该是:select  *  from  usecar as A
where  finishtime  is null 闲的：
select  *  from  usecar as A
where  finishtime  is not null 
and not exists(select 1 from usecar where carno=A.carno and finishtime  is null)

Answer 7

我对此进行了合并
如下的sql：
得到了一个结果
select  *  from  usecar A
where  finishtime  is not  null 
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime  is null)
union
select  *  from  usecar A
where  finishtime  is null 
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime  is null)1 粤A63900 20110627101 2011-6-27 10:00:00 2011-6-27 12:00:00 1
2 粤AE6880 20110627001 2011-6-17 13:00:00 2011-6-17 14:00:00 1
3 粤AL5340 20110627001 2011-6-27 9:00:00
这就是全部查询的结果，这样有什么bug？

Answer 8

忙的:
select  *  from  usecar 
where  finishtime  is null 闲的：
select  *  from  usecar 
where  finishtime  is not null 
and not exists(select 1 from usecar where  finishtime  is null)

Answer 9

select * from usecar A
where finishtime is not null  
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime is null)
union
select * from usecar A
where finishtime is null  
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime is null)我这样写是因为客户可能直接全部查询所有的车，这样的结果也要去区分闲和忙的车。
这样的结果集貌似也有问题。

Answer 10

select * from usecar A
where finishtime is not null  
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime is null)
union
select * from usecar A
where finishtime is null  
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and finishtime is not null)

Answer 11

你应该是要捞最大时间的那笔资料吧？
捞出来再判断吧
搞不清楚你的table设计逻辑

Answer 12

select * from usecar a
where a.finishtime is null
and not exists
(select 1 from usecar b where a.carno=b.carno and b.listid>a.listid)

Answer 13

表的逻辑是：车，车做到任务ID。每次每个车做到任务都会写到这个表中来，当然一个车可能也做多个任务。
经过我测试。
这样查询会出另外的问题比如：粤AL5340 在9点05分分配一个任务Id 02，它比9点整接到的任务 01 先完成。这样不就出问题了？
这是测试数据：
1 粤A63900 20110627101 2011-6-27 10:00:00 2011-6-27 12:00:00 1
2 粤AE6880 20110627001 2011-6-17 13:00:00 2011-6-17 14:00:00 1
3 粤AL5340 20110627001 2011-6-27 9:00:00 1
4 粤AL5340 20110601103 2011-6-16 14:49:15 2011-6-16 17:30:00 1
5 粤AL5340 20110601100 2011-6-14 18:47:15 2011-6-14 20:00:00 1
6 粤AL5340 20110627002 2011-6-27 9:05:00 2011-6-27 11:30:00 1

Answer 14

出现的问题是：查询所有的车时，粤AL5340这个车已经处于空闲状态了，而实际的是该车仍然处于忙时状态，因为 9点接到的任务id 01 还没有完成。

Answer 15

那你就应该用state栏位来判断，为什么要用finish time 来判断？

Answer 16

select * from usecar A
where state=1 
and not exists(select 1 from usecar where carno=A.carno and starttime>A.starttime and state is null)

Answer 17

state 是用来表示 listid是否是正常的
如果取消这个任务 state就是0。
finishtime 是完成任务时间。
只要没有完成任务，finishtime就一直是空的
举个例子：比如粤AL5340处于广州，在9点整时接到去北京执行任务的id 01
又同时在9点5分接到去郑州执行任务的id 02
去北京的途中经过郑州，因此任务02比01就先完成了
这样01的starttime比02的starttime大，但是却没完成，所以用starttime这样就出现了bug 

Answer 18

也就是一句话，finishtime为空的车都是忙的，其余的都是闲的，是不是这个逻辑？

Answer 19

继续等...大家帮忙。谢谢
我们dba没弄出这个sql。
上周5出现这个bug。

Answer 20

select  *  from  usecar 
where  finishtime  is null 
union
select  *  from  usecar 
where  finishtime  is not null 
and  carno not in(select carno from usecar where  finishtime  is null)

Answer 21

--忙
select * from usecar
where finishtime is null--闲
select A.* from usecar as A
inner join 
  (select max(finishtime) as max_finish,carno from usecar group by carno) B
on A.carno=B.carno and A.finishtime=B.max_finish
and not exists(select 1 from usecar where carno=A.carno and finishtime is null)

Answer 22

经过与迩的综合，得到如下的sql：select * from usecar where starttime in (select max(starttime) from usecar where finishtime is null group by carno) 
and  finishtime is null 
union
select A.* from usecar A
inner join  
  (select max(finishtime) as max_finish,carno from usecar group by carno) B
on A.carno=B.carno and A.finishtime=B.max_finish
and not exists(select 1 from usecar where carno=A.carno and finishtime is null)感觉效率比较底，还可以修改么?select * from usecar where starttime in (select max(starttime) from usecar where finishtime is null group by carno) 
and  finishtime is null 这个是为了查忙时车的最后记录

Answer 23

因为在加上与其他的表关联的话，这条sql比较长了

Answer 24

只要有未完成任务的都是忙得      只要没有未完成任务的就是闲的
忙得 select * from usecar where finishtime is null
闲的 
select * from usecar where carno not in
(select carno from usecar where finishtime is null)

Answer 25

-- finishtime is null为忙
-- 忙（结束时间为空的车）
select distinct cardno from usecar where finishtime is null-- 闲(结束时间不为空的车，且这辆车不在结束时间为空的车里面)
select distinct t.cardno t from usecar t where t.finishtime is not null
    and not exists(select 1 from usecar tmp where t.cardno=tmp.cardno and tmp.finishtime is null)-- 看上去有重复的车，需要去重复distinct一下。感觉表没设计好，这个表用于记录出车情况日志。应该再用一个表记录车当前的状态和当前的任务。是想混在一起用的感觉。

Answer 26

SELECT a.carno,a.listid,a.starttime,a.finishtime,'空闲' 
FROM usecar a WHERE a.finishtime IS NOT NULL 
AND NOT EXISTS( SELECT 1 FROM usecar b WHERE a.carno=b.carno AND b.finishtime IS NULL)
UNION 
SELECT a.carno,a.listid,a.starttime,a.finishtime,'忙' FROM usecar a WHERE a.finishtime IS NULL;

Answer 27

SELECT a.carno,a.listid,a.starttime,a.finishtime,'空闲' 
FROM usecar a WHERE a.finishtime IS NOT NULL 
AND NOT EXISTS( SELECT 1 FROM usecar b WHERE a.carno=b.carno AND b.finishtime IS NULL)
UNION 
SELECT a.carno,a.listid,a.starttime,a.finishtime,'忙' FROM usecar a WHERE a.finishtime IS NULL;

Answer 28

SELECT a.carno,a.listid,a.starttime,a.finishtime,'空闲' 
FROM usecar a WHERE a.finishtime IS NOT NULL 
AND NOT EXISTS( SELECT 1 FROM usecar b WHERE a.carno=b.carno AND b.finishtime IS NULL)
UNION 
SELECT a.carno,a.listid,a.starttime,a.finishtime,'忙' FROM usecar a WHERE a.finishtime IS NULL;
查询结果是如下，不知道是否是你的结果
1 粤A63900 20110627101 2011-6-27 10:00:00 2011-6-27 12:00:00 空闲
2 粤AE6880 20110627001 2011-6-17 13:00:00 2011-6-17 14:00:00 空闲
3 粤AL5340 20110627001 2011-6-27 9:00:00                     忙

Answer 29

select distinct(carno),listid,starttime,finishtime,'忙' from usecar where finishtime is null
union
select distinct(carno),listid,starttime,finishtime,'闲' from usecar where 
finishtime is not null and 
carno not in
(select carno from usecar where finishtime is null)

Answer 30

select * from tbname a where finishtime is null and reachtime >=(select max(finishtime) from tbname b where a.车牌=b.车牌 )

Answer 31

正忙的车
select distinct(carno) from usecar where finishtime is null空闲的车
select distinct(carno) from usecar where carno not in (select distinct(carno) from usercar where finishtime is null)

Answer 32

我整半天没明白楼主想说什么,不过感觉这个不难,你们的DBA要加油

Answer 33

select carno, case  sum(isnull(state,0))/count(isnull(state,0)) when  1 then  '闲'  else '忙' end  from usercar group by carno 
自行运行结果

Answer 34

declare @usecar table 
(
carno varchar(50),
listid varchar (100),
starttime datetime,
reachtime datetime,
finishtime datetime,
state varchar
)
insert into @usecar(carno,listid,starttime,finishtime,state)
select '粤A63900','20110627101',cast('2011-6-27 10:00:00'as datetime),cast('2011-6-27 12:00:00'as datetime),'1' union
select '粤AE6880','20110627001',cast('2011-6-17 13:00:00'as datetime),cast('2011-6-17 14:00:00'as datetime),'1' union
select '粤AL5340','20110601103',cast('2011-6-16 14:49:15'as datetime),cast('2011-6-16 17:30:00'as datetime),'1' union
--去北京未完成
select '粤AL5340','20110627001',cast('2011-6-27 9:00:00'as datetime),null,'1'  union 
--路过郑州完成任务二
select '粤AL5340','20110627002',cast('2011-6-28 9:00:00'as datetime),cast('2011-6-29 9:00:00'as datetime),'1'  union
select '粤AL5340','20110601100',cast('2011-6-14 18:47:15'as datetime),cast('2011-6-14 20:00:00'as datetime),'1'
--读者的查询
--空闲的车子，判断车子是否为空闲时不应该用时间作为条件
select * from @usecar A
where finishtime is not null and a.state=1
and not exists(select 1 from @usecar b where b.carno=A.carno /*and starttime>A.starttime */ and finishtime is null and b.state=1)
union
--忙碌的车子
select * from @usecar A
where finishtime is null and a.state=1
and not exists(select 1 from @usecar b where b.carno=A.carno and starttime>A.starttime and finishtime is null and b.state=1)

Answer 35

 
结果如下：
(6 行受影响)
carno                                              listid                                                                                               starttime               reachtime               finishtime              state
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------- ----------------------- ----------------------- -----
粤A63900                                            20110627101                                                                                          2011-06-27 10:00:00.000 NULL                    2011-06-27 12:00:00.000 1
粤AE6880                                            20110627001                                                                                          2011-06-17 13:00:00.000 NULL                    2011-06-17 14:00:00.000 1
粤AL5340                                            20110627001                                                                                          2011-06-27 09:00:00.000 NULL                    NULL                    1(3 行受影响)

Answer 36

这样一个简单的问题如果DBA都解决不了那就说明表设计有问题

Answer 37

补充50楼自己的错误，去掉重复的空闲车辆:declare @usecar table 
(
carno varchar(50),
listid varchar (100),
starttime datetime,
reachtime datetime,
finishtime datetime,
state varchar
)
insert into @usecar(carno,listid,starttime,finishtime,state)
select    '粤A63900','20110627101',cast('2011-6-27 10:00:00'as datetime),cast('2011-6-27 12:00:00'as datetime),'1' union
--多次完成任务
select    '粤AE6880','20110627001',cast('2011-6-17 13:00:00'as datetime),cast('2011-6-17 14:00:00'as datetime),'1' union
select    '粤AE6880','20110627001',cast('2012-6-17 13:00:00'as datetime),cast('2011-6-17 14:00:00'as datetime),'1' union
select    '粤AL5340','20110601103',cast('2011-6-16 14:49:15'as datetime),cast('2011-6-16 17:30:00'as datetime),'1' union
--去北京未完成
select    '粤AL5340','20110627001',cast('2011-6-27 9:00:00'as datetime),null,'1'     union 
--路过郑州完成任务二
select    '粤AL5340','20110627002',cast('2011-6-28 9:00:00'as datetime),cast('2011-6-29 9:00:00'as datetime),'1'     union
select    '粤AL5340','20110601100',cast('2011-6-14 18:47:15'as datetime),cast('2011-6-14 20:00:00'as datetime),'1'
--读者的查询
--空闲的车子，判断车子是否为空闲时不应该用时间作为条件
select * from @usecar A
where finishtime is not null and a.state=1
and not exists(select 1 from @usecar b where b.carno=A.carno /*and starttime>A.starttime */ and finishtime is null and b.state=1)
and not exists(select 1 from @usecar b where b.carno=A.carno and starttime>A.starttime  and finishtime is not null and b.state=1)
union
--忙碌的车子
select * from @usecar A
where finishtime is null and a.state=1
and not exists(select 1 from @usecar b where b.carno=A.carno and starttime>A.starttime and finishtime is null and b.state=1)
-- carno        listid          starttime               reachtime finishtime                state                
粤A63900 20110627101 2011-06-27 10:00:00.000 NULL   2011-06-27 12:00:00.000 1
粤AE6880 20110627001 2012-06-17 13:00:00.000 NULL   2011-06-17 14:00:00.000 1
粤AL5340 20110627001 2011-06-27 09:00:00.000 NULL   NULL 1

Answer 38

不得不说,你们的这个DBA不称职:
create table usecar(
 carno varchar(50),
listid varchar (100),
starttime datetime,
reachtime datetime,
finishtime datetime,
state int
)
insert into usecar
select 1,'粤A63900','2011-06-27 10:10','2011-6-27 10:00:00','2011-6-27 12:00:00',1 union all
select 2,'粤AE6880','2011-06-27 00:10','2011-6-17 13:00:00','2011-6-17 14:00:00',1 union all
select 3,'粤AL5340','2011-06-01 10:30','2011-6-16 14:49:15','2011-6-16 17:30:00',1 union all
select 4,'粤AL5340','2011-06-27 00:10','2011-6-27 9:00:00',null,1 union all
select 5,'粤AL5340','2011-06-01 10:00','2011-6-14 18:47:15','2011-6-14 20:00:00',1
go
--查闲:
select distinct listid from usecar a where not exists(select 1 from usecar where listid=a.listid and finishtime is null)
/*
listid
----------------------------------------------------------------------------------------------------
粤A63900
粤AE6880(2 行受影响)
*/
--查忙:
select distinct listid from usecar a where exists(select 1 from usecar where listid=a.listid and finishtime is null)
/*
listid
----------------------------------------------------------------------------------------------------
粤AL5340(1 行受影响)*/
go
drop table usecar

Answer 39

create table usecar(
 carno varchar(50),
listid varchar (100),
starttime datetime,
reachtime datetime,
finishtime datetime,
state int
)
insert into  usecar(carno,listid,starttime,finishtime,state)
select    '粤A63900','20110627101',cast('2011-6-27 10:00:00'as datetime),cast('2011-6-27 12:00:00'as datetime),'1' union
select    '粤AE6880','20110627001',cast('2011-6-17 13:00:00'as datetime),cast('2011-6-17 14:00:00'as datetime),'1' union
select    '粤AL5340','20110601103',cast('2011-6-16 14:49:15'as datetime),cast('2011-6-16 17:30:00'as datetime),'1' union
--去北京未完成
select    '粤AL5340','20110627001',cast('2011-6-27 9:00:00'as datetime),null,'1'     union 
--路过郑州完成任务二
select    '粤AL5340','20110627002',cast('2011-6-28 9:00:00'as datetime),cast('2011-6-29 9:00:00'as datetime),'1'     union
select    '粤AL5340','20110601100',cast('2011-6-14 18:47:15'as datetime),cast('2011-6-14 20:00:00'as datetime),'1'
goselect carno, case sum(case  utime when 0  then 0 else 1 end)/count(case  utime when 0  then 0 else 1 end) when 1 then '闲' else '忙' end from 
(select carno,case  isnull(finishtime,0) when 0  then 0 else 1 end  utime  from usecar) usercar group by carno  粤A63900 闲
粤AE6880 闲
粤AL5340 忙

Answer 40

关键是要理解自己的业务逻辑，然后把自己的业务逻辑转化成sql应该就搞定了。

Answer 41

SELECT DISTINCT CARNO FROM USECAR 
WHERE CARNO NOT IN (
SELECT CARNO FROM USECAR WHERE FINISHTIME IS NULL
)