求一SQL语句

[HU0L0NG]

有一进出库表 inout

goodsid price num inoutid
1 5 10 6
2 8 10 6
3 10 10 6
4 20 10 7
5 50 10 7
1 6 10 7
3 9 10 8
4 18 10 8
5 48 10 8

要查询所有品种的最新单价（inoutid最大）
只会
select goodsid , max(inoutid) from inout group by goodsid
查出每种商品最后一次inoutid

[gxjwqm]

SELECT * FROM TB T 
WHERE inoutid=(SELECT MAX(inoutid) FROM TB  WHERE goodsid =T.goodsid )

[pt1314917]

select * from 表名 a
where not exists(select 1 from 表名 where goodsid=a.goodsid and inoutid>a.inoutid)

[sinpoal]

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>

[yjh53615728]

-->测试数据
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

[SQL77]

SELECT * FROM TB T 
WHERE inoutid=(SELECT MAX(inoutid) FROM TB  WHERE goodsid =T.goodsid )

[SQL77]

----------------------------------------------------------------
-- 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 行）


*/

[yananguo_1985]

select goodsid,price from inout a where not exists(select 1 from inout 
where goodsid=a.goodsid and inoutid>a.inoutid)

[yananguo_1985]

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

2	8
1	6
3	9
4	18
5	48

[yilvchaxiang]

以上都是不错的见解