SELECT * FROM TB T
WHERE inoutid=(SELECT MAX(inoutid) FROM TB WHERE goodsid =T.goodsid )
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-16 12:07:45
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([goodsid] int,[price] int,[num] int,[inoutid] int)
insert #TB
select 1,5,10,6 union all
select 2,8,10,6 union all
select 3,10,10,6 union all
select 4,20,10,7 union all
select 5,50,10,7 union all
select 1,6,10,7 union all
select 3,9,10,8 union all
select 4,18,10,8 union all
select 5,48,10,8
--------------开始查询--------------------------
SELECT * FROM #TB T
WHERE inoutid=(SELECT MAX(inoutid) FROM #TB WHERE goodsid =T.goodsid )
ORDER BY goodsid
----------------结果----------------------------
/*
（所影响的行数为 9 行）
goodsid price num inoutid
----------- ----------- ----------- -----------
1 6 10 7
2 8 10 6
3 9 10 8
4 18 10 8
5 48 10 8
（所影响的行数为 5 行）
*/
select goodsid,price from inout a where not exists(select 1 from inout
where goodsid=a.goodsid and inoutid>a.inoutid)
---------------
2 8
1 6
3 9
4 18
5 48
SELECT * FROM TB T
WHERE inoutid=(SELECT MAX(inoutid) FROM TB WHERE goodsid =T.goodsid )
select * from 表名 a
where not exists(select 1 from 表名 where goodsid=a.goodsid and inoutid>a.inoutid)
1> if object_id('TB') is not null drop table TB
2> go
1> create table TB(goodsid int,price int,num int,inoutid int)
2> insert TB
3> select 1,5,10,6 union all
4> select 2,8,10,6 union all
5> select 3,10,10,6 union all
6> select 4,20,10,7 union all
7> select 5,50,10,7 union all
8> select 1,6,10,7 union all
9> select 3,9,10,8 union all
10> select 4,18,10,8 union all
11> select 5,48,10,8
12>
13> select * from tb t where inoutid=
14> (select max(inoutid) from tb where goodsid=t.goodsid) order by goodsid
15> go
(9 行受影响)
goodsid price num inoutid
----------- ----------- ----------- -----------
1 6 10 7
2 8 10 6
3 9 10 8
4 18 10 8
5 48 10 8
(5 行受影响)
1>
-->测试数据
IF OBJECT_ID('Inout') IS NOT NULL DROP TABLE Inout
CREATE TABLE Inout
(
GoodSid VARCHAR(40),
Price DECIMAL(20,2),
Num INT,
inoutid INT
)
GO
INSERT INTO Inout
SELECT '1',5,10,6 UNION ALL
SELECT '2',8,10,6 UNION ALL
SELECT '3',10,10,6 UNION ALL
SELECT '4',20,10,7 UNION ALL
SELECT '5',50,10,7 UNION ALL
SELECT '1',6,10,7 UNION ALL
SELECT '3',9,10,8 UNION ALL
SELECT '4',18,10,8 UNION ALL
SELECT '5',48,10,8
-->思路：先聚合再求值
SELECT GoodSid,Price
FROM Inout a
LEFT OUTER JOIN(SELECT GoodSid,[inoutid]=MAX(inoutid) FROM Inout) b ON a.GoodSid=b.GoodSid AND a.inoutid=b.inoutid
