日期:2014-05-18  浏览次数:20535 次

怎样查询 数据库 某段 时间 某个表的 数据
如题!

------解决方案--------------------
删除的数据怎么查回来?历史表?
------解决方案--------------------
SQL code

---->>TravyLee生成测试数据:
if OBJECT_ID('test')is not null
drop table test
go
create table test(
  
  id int,
  value varchar(5),
  dates datetime
  
)
go
insert test
select 1,'aaa','2012-05-31 21:31:28' union all
select 2,'bbb','2012-05-28 21:31:28' union all
select 3,'ccc','2012-05-27 21:31:28' union all
select 4,'ddd','2012-05-26 21:31:28' union all
select 5,'eee','2012-05-25 21:31:28'
go

if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
  
  id int,
  value varchar(5),
  dates datetime
  
)
go
--创建触发器,当删除数据是把删除的数据插入到删除数据表tbl:
if OBJECT_ID('tri_test')is not null
drop trigger tri_test
go
create trigger tri_test on test
for delete
as
insert tbl
select * from deleted
go

--测试:
delete from test where dates in('2012-05-31 21:31:28','2012-05-27 21:31:28')
go

select * from test 
where dates between '2012-05-27 21:31:28' and '2012-05-31 21:31:28'
union all
select * from tbl 
where dates between '2012-05-27 21:31:28' and '2012-05-31 21:31:28'
order by id

/*
id    value    dates
1    aaa    2012-05-31 21:31:28.000
2    bbb    2012-05-28 21:31:28.000
3    ccc    2012-05-27 21:31:28.000
*/

--想直接从一张表中查询出来是不大现实的

------解决方案--------------------
改状态,is_deleted=0 删除是等于1