求一SQL语句

HU0L0NG 2010-01-16 12:01:46
有一进出库表 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


...全文
156 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
yilvchaxiang 2010-01-19
  • 打赏
  • 举报
回复
以上都是不错的见解
yananguo_1985 2010-01-16
  • 打赏
  • 举报
回复

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

2 8
1 6
3 9
4 18
5 48


yananguo_1985 2010-01-16
  • 打赏
  • 举报
回复

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

SQL77 2010-01-16
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- 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 行)


*/
SQL77 2010-01-16
  • 打赏
  • 举报
回复
SELECT * FROM TB T 
WHERE inoutid=(SELECT MAX(inoutid) FROM TB WHERE goodsid =T.goodsid )
yjh53615728 2010-01-16
  • 打赏
  • 举报
回复

-->测试数据
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
sinpoal 2010-01-16
  • 打赏
  • 举报
回复


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>
pt1314917 2010-01-16
  • 打赏
  • 举报
回复


select * from 表名 a
where not exists(select 1 from 表名 where goodsid=a.goodsid and inoutid>a.inoutid)
gxjwqm 2010-01-16
  • 打赏
  • 举报
回复
SELECT * FROM TB T 
WHERE inoutid=(SELECT MAX(inoutid) FROM TB WHERE goodsid =T.goodsid )

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧