22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE t1
(
huohao INT,
shijian DATETIME,
shuliang INT
)
INSERT INTO t1
SELECT 1123, '2012-06-10', 10 UNION ALL
SELECT 1124, '2012-06-11', 13 UNION ALL
SELECT 1123, '2012-06-15', 22 UNION ALL
SELECT 1125, '2012-06-19', 9 UNION ALL
SELECT 1122, '2012-06-01', 14 UNION ALL
SELECT 1123, '2012-06-19', 20
SELECT * FROM t1
SELECT * FROM t1 AS a WHERE NOT EXISTS (SELECT 1 FROM t1 WHERE huohao=a.huohao AND shijian>a.shijian)
ORDER BY huohao
---------------------------
huohao shijian shuliang
1122 2012-06-01 00:00:00.000 14
1123 2012-06-19 00:00:00.000 20
1124 2012-06-11 00:00:00.000 13
1125 2012-06-19 00:00:00.000 9
select [货号],MAX([收货时间]) 收货时间,[收货数量] from tb group by [货号],[收货数量]
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([货号] int,[收货时间] datetime,[收货数量] int)
insert [test]
select 1123,'2012-06-10',10 union all
select 1124,'2012-06-11',13 union all
select 1123,'2012-06-15',22 union all
select 1125,'2012-06-19',9 union all
select 1122,'2012-06-01',14 union all
select 1123,'2012-06-19',20
--看错了,改好了
select distinct * from test a
where a.收货时间=(select MAX(b.收货时间) from test b where a.货号=b.货号)
/*
货号 收货时间 收货数量
---------------------------------------------
1122 2012-06-01 00:00:00.000 14
1123 2012-06-19 00:00:00.000 20
1124 2012-06-11 00:00:00.000 13
1125 2012-06-19 00:00:00.000 9
*/
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([货号] int,[收货时间] datetime,[收货数量] int)
insert [test]
select 1123,'2012-06-10',10 union all
select 1124,'2012-06-11',13 union all
select 1123,'2012-06-15',22 union all
select 1125,'2012-06-19',9 union all
select 1122,'2012-06-01',14 union all
select 1123,'2012-06-19',20
select distinct * from test a
where a.收货时间=(select MAX(b.收货时间) from test b where a.货号=b.货号)
or a.收货时间=(select MIN(c.收货时间) from test c where a.货号=c.货号)
/*
货号 收货时间 收货数量
---------------------------------------------
1122 2012-06-01 00:00:00.000 14
1123 2012-06-10 00:00:00.000 10
1123 2012-06-19 00:00:00.000 20
1124 2012-06-11 00:00:00.000 13
1125 2012-06-19 00:00:00.000 9
*/
;with T as
(
select row_number() over( PARTITION BY [货号] order by [收货时间] desc ) as Row, * from [table]
)
select * from t where Row = 1
if object_id('[test]') is not null drop table [test]
create table [test]([货号] int,[收货时间] datetime,[收货数量] int)
insert [test]
select 1123,'2012-06-10',10 union all
select 1124,'2012-06-11',13 union all
select 1123,'2012-06-15',22 union all
select 1124,'2012-06-15',30 union all
select 1125,'2012-06-19',9 union all
select 1122,'2012-06-01',14 union all
select 1125,'2012-06-20',19 union all
select 1123,'2012-06-19',20
;with T as
(
select row_number() over( PARTITION BY [货号] order by [收货时间] desc ) as Row, * from [test]
)
select * from t where Row = 1
(8 行受影响)
Row 货号 收货时间 收货数量
-------------------- ----------- ----------------------- -----------
1 1122 2012-06-01 00:00:00.000 14
1 1123 2012-06-19 00:00:00.000 20
1 1124 2012-06-15 00:00:00.000 30
1 1125 2012-06-20 00:00:00.000 19
(4 行受影响)