ID 工作单号 出库时间 出库单位
1 1729805932 2011-01-27 08:17:00.000 4789
2 1729805932 2011-01-27 06:12:00.000 167
3 1729805932 2011-01-26 10:13:00.000 2734
4 1729805932 2011-01-21 09:17:00.000 2734
5 1729805932 2010-11-17 09:15:00.000 2734
6 1729805932 2010-11-11 21:57:00.000 167
7 1729805932 2010-11-11 18:10:00.000 4789
8 1729805932 2011-01-27 08:17:00.000 4789
9 1729805932 2011-01-27 06:12:00.000 167
10 1729805932 2011-01-26 10:13:00.000 2734
11 1729805932 2011-01-21 09:17:00.000 2734
12 1729805932 2010-11-17 09:15:00.000 2734
13 1729805932 2010-11-11 21:57:00.000 167
14 1729805932 2010-11-11 18:10:00.000 4789
15 1783021166 2011-01-31 10:45:00.000 4639
16 1783021166 2011-01-31 00:31:00.000 167
17 1783021166 2011-01-27 14:12:00.000 171
18 1783021166 2010-12-15 22:49:00.000 167
19 1783021166 2010-12-14 01:42:00.000 4470
20 1783021166 2010-12-13 01:09:00.000 4667如何查询一个单号最大出库时间,结果包含对应的单位
1 1729805932 2011-01-27 08:17:00.000 4789
2 1729805932 2011-01-27 06:12:00.000 167
3 1729805932 2011-01-26 10:13:00.000 2734
4 1729805932 2011-01-21 09:17:00.000 2734
5 1729805932 2010-11-17 09:15:00.000 2734
6 1729805932 2010-11-11 21:57:00.000 167
7 1729805932 2010-11-11 18:10:00.000 4789
8 1729805932 2011-01-27 08:17:00.000 4789
9 1729805932 2011-01-27 06:12:00.000 167
10 1729805932 2011-01-26 10:13:00.000 2734
11 1729805932 2011-01-21 09:17:00.000 2734
12 1729805932 2010-11-17 09:15:00.000 2734
13 1729805932 2010-11-11 21:57:00.000 167
14 1729805932 2010-11-11 18:10:00.000 4789
15 1783021166 2011-01-31 10:45:00.000 4639
16 1783021166 2011-01-31 00:31:00.000 167
17 1783021166 2011-01-27 14:12:00.000 171
18 1783021166 2010-12-15 22:49:00.000 167
19 1783021166 2010-12-14 01:42:00.000 4470
20 1783021166 2010-12-13 01:09:00.000 4667如何查询一个单号最大出库时间,结果包含对应的单位
where not exists(select 1 from tb where 工作单号=a.工作单号 and 出库时间>a.出库时间)
select *
from tb t
where not exists(select 1 from tb where 工作单号 = t.工作单号 and 出库时间 > t.出库时间)select *
from tb t
where 出库时间 = (select max(出库时间) from tb where 工作单号 = t.工作单号)
use tempdb;
/*
create table t2
(
ID int not null,
工作单号 nvarchar(10) not null,
出库时间 datetime not null,
出库单位 int not null
);
insert into t2(ID,工作单号,出库时间,出库单位)
values
(1,'1729805932','2011-01-27 08:17:00.000',4789),
(2,'1729805932','2011-01-27 06:12:00.000',167),
(3,'1729805932','2011-01-26 10:13:00.000',2734),
(4,'1729805932','2011-01-21 09:17:00.000',2734),
(5,'1729805932','2010-11-17 09:15:00.000',2734),
(6,'1729805932','2010-11-11 21:57:00.000',167),
(7,'1729805932','2010-11-11 18:10:00.000',4789),
(8,'1729805932','2011-01-27 08:17:00.000',4789),
(9,'1729805932','2011-01-27 06:12:00.000',167),
(10,'1729805932','2011-01-26 10:13:00.000',2734),
(11,'1729805932','2011-01-21 09:17:00.000',2734),
(12,'1729805932','2010-11-17 09:15:00.000',2734),
(13,'1729805932','2010-11-11 21:57:00.000',167),
(14,'1729805932','2010-11-11 18:10:00.000',4789),
(15,'1783021166','2011-01-31 10:45:00.000',4639),
(16,'1783021166','2011-01-31 00:31:00.000',167),
(17,'1783021166','2011-01-27 14:12:00.000',171),
(18,'1783021166','2010-12-15 22:49:00.000',167),
(19,'1783021166','2010-12-14 01:42:00.000',4470),
(20,'1783021166','2010-12-13 01:09:00.000',4667);
*/select t2.工作单号,MAX(t2.出库时间) as [出库时间],MAX(t2.出库单位) as [出库单位]
from t2
group by t2.工作单号;
use tempdb;
/*
create table t2
(
ID int not null,
工作单号 nvarchar(10) not null,
出库时间 datetime not null,
出库单位 int not null
);
insert into t2(ID,工作单号,出库时间,出库单位)
values
(1,'1729805932','2011-01-27 08:17:00.000',4789),
(2,'1729805932','2011-01-27 06:12:00.000',167),
(3,'1729805932','2011-01-26 10:13:00.000',2734),
(4,'1729805932','2011-01-21 09:17:00.000',2734),
(5,'1729805932','2010-11-17 09:15:00.000',2734),
(6,'1729805932','2010-11-11 21:57:00.000',167),
(7,'1729805932','2010-11-11 18:10:00.000',4789),
(8,'1729805932','2011-01-27 08:17:00.000',4789),
(9,'1729805932','2011-01-27 06:12:00.000',167),
(10,'1729805932','2011-01-26 10:13:00.000',2734),
(11,'1729805932','2011-01-21 09:17:00.000',2734),
(12,'1729805932','2010-11-17 09:15:00.000',2734),
(13,'1729805932','2010-11-11 21:57:00.000',167),
(14,'1729805932','2010-11-11 18:10:00.000',4789),
(15,'1783021166','2011-01-31 10:45:00.000',4639),
(16,'1783021166','2011-01-31 00:31:00.000',167),
(17,'1783021166','2011-01-27 14:12:00.000',171),
(18,'1783021166','2010-12-15 22:49:00.000',167),
(19,'1783021166','2010-12-14 01:42:00.000',4470),
(20,'1783021166','2010-12-13 01:09:00.000',4667);
*/
--方法1
select t2.工作单号,MAX(t2.出库时间) as [出库时间],MAX(t2.出库单位) as [出库单位]
from t2
group by t2.工作单号;
--方法2
select distinct t.工作单号,t.出库时间,t.出库单位
from t2 as t
where not exists(select * from t2 where 工作单号 = t.工作单号 and 出库时间 > t.出库时间)
*
from
tb t
where 出库时间 = (select max(出库时间) from tb where 工作单号 = t.工作单号)