27,579
社区成员
发帖
与我相关
我的任务
分享
create TABLE tb(station int,card int,time datetime,flag int)
INSERT INTO tb
SELECT 257,111,'2009-10-01 08:00:00',1 UNION ALL
SELECT 257,111,'2009-10-01 08:00:10',0 UNION ALL
SELECT 258,111,'2009-10-01 08:00:20',1 UNION ALL
SELECT 259,111,'2009-10-01 08:00:30',1 UNION ALL
SELECT 259,111,'2009-10-01 08:00:30',2 UNION ALL
SELECT 259,111,'2009-10-01 08:00:40',0 UNION ALL
SELECT 257,222,'2009-10-01 08:00:00',1 UNION ALL
SELECT 257,222,'2009-10-01 08:00:10',0 UNION ALL
SELECT 258,222,'2009-10-01 08:00:20',1 UNION ALL
SELECT 259,222,'2009-10-01 08:00:30',1 UNION ALL
SELECT 260,222,'2009-10-01 08:00:40',1
go
--我的。
select t.* from tb t where flag = 1 and time = (select max(time) from tb where flag = 1 and card = t.card) order by t.card
select t.* from tb t where flag = 1 and not exists(select 1 from tb where flag = 1 and card = t.card and time > t.time) order by t.card
/*
station card time flag
----------- ----------- ------------------------------------------------------ -----------
259 111 2009-10-01 08:00:30.000 1
260 222 2009-10-01 08:00:40.000 1
(所影响的行数为 2 行)
station card time flag
----------- ----------- ------------------------------------------------------ -----------
259 111 2009-10-01 08:00:30.000 1
260 222 2009-10-01 08:00:40.000 1
(所影响的行数为 2 行)
*/
--小P梁的。
SELECT * FROM tb AS A
WHERE time =(SELECT MAX(time) FROM tb WHERE card=A.card
AND flag = 1)
/*
station card time flag
----------- ----------- ------------------------------------------------------ -----------
259 111 2009-10-01 08:00:30.000 1
259 111 2009-10-01 08:00:30.000 2
260 222 2009-10-01 08:00:40.000 1
(所影响的行数为 3 行)
*/
--小F的。
select
*
from
tb t
where
time=(select max(time) from tb where card=t.card) and flag=1
/*
station card time flag
----------- ----------- ------------------------------------------------------ -----------
260 222 2009-10-01 08:00:40.000 1
(所影响的行数为 1 行)
*/
drop table tb
select * from # A where time = (select max(time) from # B where card =A.card and flag = 1)
259 111 2009-10-01 08:00:30 1
260 222 2009-10-01 08:00:40 1
select * into # from (
select 257 as station, 111 as card , '2009-10-01 08:00:00'as time, 1 as flag union all
select 257 , 111 , '2009-10-01 08:00:10', 0 union all
select 258 , 111 , '2009-10-01 08:00:20', 1 union all
select 259 , 111 , '2009-10-01 08:00:30', 1 union all
select 259 , 111 , '2009-10-01 08:00:40', 0 union all
select 257 , 222 , '2009-10-01 08:00:00', 1 union all
select 257 , 222 , '2009-10-01 08:00:10', 0 union all
select 258 , 222 , '2009-10-01 08:00:20', 1 union all
select 259 , 222 , '2009-10-01 08:00:30', 1 union all
select 260 , 222 , '2009-10-01 08:00:40', 1 )
A
select * from # A where time = (select max(time) from # B where card =A.card)
create TABLE tb(station int,card int,time datetime,flag int)
INSERT INTO tb
SELECT 257,111,'2009-10-01 08:00:00',1 UNION ALL
SELECT 257,111,'2009-10-01 08:00:10',0 UNION ALL
SELECT 258,111,'2009-10-01 08:00:20',1 UNION ALL
SELECT 259,111,'2009-10-01 08:00:30',1 UNION ALL
SELECT 259,111,'2009-10-01 08:00:40',0 UNION ALL
SELECT 257,222,'2009-10-01 08:00:00',1 UNION ALL
SELECT 257,222,'2009-10-01 08:00:10',0 UNION ALL
SELECT 258,222,'2009-10-01 08:00:20',1 UNION ALL
SELECT 259,222,'2009-10-01 08:00:30',1 UNION ALL
SELECT 260,222,'2009-10-01 08:00:40',1
go
select t.* from tb t where flag = 1 and time = (select max(time) from tb where flag = 1 and card = t.card) order by t.card
select t.* from tb t where flag = 1 and not exists(select 1 from tb where flag = 1 and card = t.card and time > t.time) order by t.card
drop table tb
/*
station card time flag
----------- ----------- ------------------------------------------------------ -----------
259 111 2009-10-01 08:00:30.000 1
260 222 2009-10-01 08:00:40.000 1
(所影响的行数为 2 行)
station card time flag
----------- ----------- ------------------------------------------------------ -----------
259 111 2009-10-01 08:00:30.000 1
260 222 2009-10-01 08:00:40.000 1
(所影响的行数为 2 行)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-08 09:49:18
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([station] int,[card] int,[time] datetime,[flag] int)
insert [tb]
select 257,111,'2009-10-01 08:00:00',1 union all
select 257,111,'2009-10-01 08:00:10',0 union all
select 258,111,'2009-10-01 08:00:20',1 union all
select 259,111,'2009-10-01 08:00:30',1 union all
select 259,111,'2009-10-01 08:00:40',0 union all
select 257,222,'2009-10-01 08:00:00',1 union all
select 257,222,'2009-10-01 08:00:10',0 union all
select 258,222,'2009-10-01 08:00:20',1 union all
select 259,222,'2009-10-01 08:00:30',1 union all
select 260,222,'2009-10-01 08:00:40',1
--------------开始查询--------------------------
select
*
from
tb t
where
time=(select max(time) from tb where card=t.card and flag=1)
----------------结果----------------------------
/*station card time flag
----------- ----------- ----------------------- -----------
259 111 2009-10-01 08:00:30.000 1
260 222 2009-10-01 08:00:40.000 1
(2 行受影响)
*/
select t.* from tb t where flag = 1 and time = (select max(time) from tb where flag = 1 and card = t.card)
select t.* from tb t where flag = 1 and not exists(select 1 from tb where flag = 1 and card = t.card and time > t.time)
select
*
from
tb t
where
time=(select max(time) from tb where card=t.card) and flg=1
-------------------------------------
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-12-08 09:47:45
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (station int,card int,time datetime,flag int)
INSERT INTO @tb
SELECT 257,111,'2009-10-01 08:00:00',1 UNION ALL
SELECT 257,111,'2009-10-01 08:00:10',0 UNION ALL
SELECT 258,111,'2009-10-01 08:00:20',1 UNION ALL
SELECT 259,111,'2009-10-01 08:00:30',1 UNION ALL
SELECT 259,111,'2009-10-01 08:00:40',0 UNION ALL
SELECT 257,222,'2009-10-01 08:00:00',1 UNION ALL
SELECT 257,222,'2009-10-01 08:00:10',0 UNION ALL
SELECT 258,222,'2009-10-01 08:00:20',1 UNION ALL
SELECT 259,222,'2009-10-01 08:00:30',1 UNION ALL
SELECT 260,222,'2009-10-01 08:00:40',1
--SQL查询如下:
SELECT * FROM @tb AS A
WHERE time =(SELECT MAX(time) FROM @tb WHERE card=A.card
AND flag = 1)