SQLServer基础语法实例应用（二）

-- 测试数据：[a] if object_id('[a]') is not null drop table [a] go create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime) insert [a] select 1,'矿泉水',100,'2013-01-02' union all select 2,'方便面',60,'2013-01-03' un

--> 测试数据：[a]

if object_id('[a]') is not null drop table [a]

go

create table [a]([ID] int,[品名] varchar(6),[入库数量] int,[入库时间] datetime)

insert [a]

select 1,'矿泉水',100,'2013-01-02' union all

select 2,'方便面',60,'2013-01-03' union all

select 3,'方便面',50,'2013-01-03' union all

select 4,'矿泉水',80,'2013-01-04' union all

select 5,'方便面',50,'2013-01-05'

 

select a.[品名],a.[入库数量],b.[最后入库时间] from [test] a  ,

(select  [品名],max([入库时间]) as '最后入库时间' from [test]  group by [品名]) b

where a.[品名]=b.[品名]

 

/*

品名     入库数量        最后入库时间

------ ----------- -----------------------

方便面    60          2013-01-05 00:00:00.000

方便面    50          2013-01-05 00:00:00.000

方便面    50          2013-01-05 00:00:00.000

矿泉水    100         2013-01-04 00:00:00.000

矿泉水    80          2013-01-04 00:00:00.000

 

(5 行受影响)

 

*/

，