状态历史的问题

Question

--这样?
select * from tablename where status in(1,2,3) and AsofDate = '2007-7-31'

Answer 1

select * from StatusHistory where Status in(1,2,3) and datediff(dd,AsofDate,'2007-07-31')=0

Answer 2

?
Select * From StatusHistory
Where Status In (1,2,3,4) And DateDiff(dd, AsofDate, '2007-7-31') = 0

Answer 3

慢了，整出個一樣的了。換種寫法。
Select * From StatusHistory
Where Status In (1,2,3,4) And Convert(Varchar(10), AsofDate, 120) = '2007-07-31'

Answer 4

要查到某个日期比如2007-7-31 Status为1,2或者3的数据.
------------------------------------------------------------------------------------
楼主是指状态一直保持1/2/3其中的一种且不变化？如果是这样，那么select 
    s.* 
from 
    StatusHistory s
where 
    s.Status in(1,2,3) 
    and 
    datediff(dd,s.AsofDate,'2007-07-31')=0
    and
    not exists(select 1 from StatusHistory where Status!=s.Status and datediff(dd,AsofDate,s.AsofDate)=0)

Answer 5

--状态为1、2、3其中之一，日期为2007-07-01
select * 
from StatusHistory 
where Status in (1,2,3) 
  and datediff(d,AsofDate,'2007-07-31') = 0

Answer 6

1 2007-07-10 18:09:19.360 4dc5d43f-f243-dc11-b600-0003ff35de1a
2 2007-07-10 10:56:52.403 4dc5d43f-f243-dc11-b600-0003ff35de1a
3 2007-07-09 18:21:19.327 4dc5d43f-f243-dc11-b600-0003ff35de1a
4 2007-07-09 16:44:55.733 4dc5d43f-f243-dc11-b600-0003ff35de1a
1 2007-07-13 11:26:14.920 5636ae28-4949-dc11-b600-0003ff35de1a
2 2007-07-13 11:09:35.357 5636ae28-4949-dc11-b600-0003ff35de1a
3 2007-07-13 11:02:58.513 5636ae28-4949-dc11-b600-0003ff35de1a
1 2007-07-13 18:47:50.950 aa7c9f8f-7f49-dc11-b600-0003ff35de1a
2 2007-07-13 17:50:08.060 aa7c9f8f-7f49-dc11-b600-0003ff35de1a

Answer 7

--状态为1、2、3其中之一，日期为2007-07-01
select * 
from StatusHistory 
where Status in (1,2,3) 
  and datediff(d,AsofDate,'2007-07-31') = 0
-------------------------------------------------------------------------------
这样不对啊,比如我给的数据
4 2007-07-09 16:44:55.733 4dc5d43f-f243-dc11-b600-0003ff35de1a
就不应该包括这条记录

Answer 8

create table StatusHistory(Status int,AsofDate datetime,TransactionId varchar(40))
insert into StatusHistory values(1,'2007-07-10 18:09:19.360','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(2,'2007-07-10 10:56:52.403','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(3,'2007-07-09 18:21:19.327','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(4,'2007-07-09 16:44:55.733','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(1,'2007-07-13 11:26:14.920','5636ae28-4949-dc11-b600-0003ff35de1a')
insert into StatusHistory values(2,'2007-07-13 11:09:35.357','5636ae28-4949-dc11-b600-0003ff35de1a')
insert into StatusHistory values(3,'2007-07-13 11:02:58.513','5636ae28-4949-dc11-b600-0003ff35de1a')
insert into StatusHistory values(1,'2007-07-13 18:47:50.950','aa7c9f8f-7f49-dc11-b600-0003ff35de1a')
insert into StatusHistory values(2,'2007-07-13 17:50:08.060','aa7c9f8f-7f49-dc11-b600-0003ff35de1a')select * from StatusHistory where Status in(1,2,3) and datediff(dd,AsofDate,'2007-07-10')=0
/*
Status      AsofDate                                               TransactionId                            
----------- ------------------------------------------------------ ---------------------------------------- 
1           2007-07-10 18:09:19.360                                4dc5d43f-f243-dc11-b600-0003ff35de1a
2           2007-07-10 10:56:52.403                                4dc5d43f-f243-dc11-b600-0003ff35de1a
*/
drop table StatusHistory

Answer 9

select Status,AsofDate,TransactionId from StatusHistory 
where status in(1,2,3) and datediff(day,AsofDate, '2007-7-31') = 0

Answer 10

--建立测试环境
create table #StatusHistory(Status int,AsofDate datetime,TransactionId varchar(40))
insert #StatusHistory(Status,AsofDate,TransactionId)
select '1','2007-07-10 18:09:19.360','4dc5d43f-f243-dc11-b600-0003ff35de1a' union all
select '2','2007-07-10 10:56:52.403','4dc5d43f-f243-dc11-b600-0003ff35de1a' union all
select '3','2007-07-09 18:21:19.327','4dc5d43f-f243-dc11-b600-0003ff35de1a' union all
select '4','2007-07-09 16:44:55.733','4dc5d43f-f243-dc11-b600-0003ff35de1a' union all
select '1','2007-07-13 11:26:14.920','5636ae28-4949-dc11-b600-0003ff35de1a' union all
select '2','2007-07-13 11:09:35.357','5636ae28-4949-dc11-b600-0003ff35de1a' union all
select '3','2007-07-13 11:02:58.513','5636ae28-4949-dc11-b600-0003ff35de1a' union all
select '1','2007-07-13 18:47:50.950','aa7c9f8f-7f49-dc11-b600-0003ff35de1a' union all
select '2','2007-07-13 17:50:08.060','aa7c9f8f-7f49-dc11-b600-0003ff35de1a'
go
--执行测试语句
select Status,AsofDate,TransactionId from #StatusHistory 
where status in(1,2,3) and datediff(day,AsofDate, '2007-7-13') = 0
go
--删除测试环境
drop table #StatusHistory
go
/*--测试结果
Status      AsofDate                                               TransactionId                            
----------- ------------------------------------------------------ ---------------------------------------- 
1           2007-07-13 11:26:14.920                                5636ae28-4949-dc11-b600-0003ff35de1a
2           2007-07-13 11:09:35.357                                5636ae28-4949-dc11-b600-0003ff35de1a
3           2007-07-13 11:02:58.513                                5636ae28-4949-dc11-b600-0003ff35de1a
1           2007-07-13 18:47:50.950                                aa7c9f8f-7f49-dc11-b600-0003ff35de1a
2           2007-07-13 17:50:08.060                                aa7c9f8f-7f49-dc11-b600-0003ff35de1a(5 row(s) affected)
*/

Answer 11

select * from StatusHistory where Status<>4--更好
  and datediff(d,AsofDate,'2007-07-31') = 0

Answer 12

create table StatusHistory(Status int,AsofDate datetime,TransactionId varchar(40))
insert into StatusHistory values(1,'2007-07-10 18:09:19.360','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(2,'2007-07-10 10:56:52.403','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(3,'2007-07-09 18:21:19.327','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(4,'2007-07-09 16:44:55.733','4dc5d43f-f243-dc11-b600-0003ff35de1a')
insert into StatusHistory values(1,'2007-07-09 11:26:14.920','5636ae28-4949-dc11-b600-0003ff35de1a')
insert into StatusHistory values(2,'2007-07-09 11:09:35.357','5636ae28-4949-dc11-b600-0003ff35de1a')
insert into StatusHistory values(3,'2007-07-09 11:02:58.513','5636ae28-4949-dc11-b600-0003ff35de1a')
insert into StatusHistory values(1,'2007-07-09 18:47:50.950','aa7c9f8f-7f49-dc11-b600-0003ff35de1a')
insert into StatusHistory values(2,'2007-07-09 17:50:08.060','aa7c9f8f-7f49-dc11-b600-0003ff35de1a')select 
    s.* 
from 
    StatusHistory s
where 
    s.Status in(1,2,3) and datediff(dd,s.AsofDate,'2007-07-09')=0
    and
    not exists(select 1 from StatusHistory where TransactionId=s.TransactionId and Status not in(1,2,3) and datediff(dd,AsofDate,s.AsofDate)=0)
/*
Status      AsofDate                    TransactionId                            
----------- --------------------------- ---------------------------------------- 
1           2007-07-09 11:26:14.920     5636ae28-4949-dc11-b600-0003ff35de1a
2           2007-07-09 11:09:35.357     5636ae28-4949-dc11-b600-0003ff35de1a
3           2007-07-09 11:02:58.513     5636ae28-4949-dc11-b600-0003ff35de1a
1           2007-07-09 18:47:50.950     aa7c9f8f-7f49-dc11-b600-0003ff35de1a
2           2007-07-09 17:50:08.060     aa7c9f8f-7f49-dc11-b600-0003ff35de1a
*/
drop table StatusHistory

Answer 13

nices(和善) ( ) 信誉：100  2007-08-14 11:10:34  得分: 0  
 
 
   --状态为1、2、3其中之一，日期为2007-07-01
select * 
from StatusHistory 
where Status in (1,2,3) 
  and datediff(d,AsofDate,'2007-07-31') = 0
-------------------------------------------------------------------------------
这样不对啊,比如我给的数据
4 2007-07-09 16:44:55.733 4dc5d43f-f243-dc11-b600-0003ff35de1a
就不应该包括这条记录
  
 
----------------
不大可能啊，這個語句會查詢出這樣的結果？

Answer 14

老大排除了同一天，包含状态4的TransactionId (不知楼主是不是这意思)
可能日期差还得改>=0楼主是到