求一分组取最大值的疑难语句

[zhongguohld]

原始表
vender_id vender_name ITEM cx_from_date cx_to_date price
WH001 微软 55-001 2013/1/1 2013/3/30 0.5
WH001 微软 55-001 2013/4/1 2013/5/31 0.6
WH001 微软 55-001 2013/6/1 2013/10/31 0.5
WH002 IBM 55-002 2013/1/1 2013/3/30 1
WH002 IBM 55-002 2013/4/1 2013/6/30 1.1
WH002 IBM 55-002 2013/7/1 2013/10/31 1.2
WH001 微软 55-002 2012/1/1 2012/3/30 2
WH001 微软 55-002 2012/4/1 2012/6/30 2.1
WH001 微软 55-002 2012/7/1 2012/12/31 2
WH001 微软 55-002 2013/1/1 2013/3/30 2.2
WH001 微软 55-002 2013/4/1 2013/6/30 2.3
WH001 微软 55-002 2013/7/1 2013/10/31 2.5

结果表
WH001 微软 55-001 2013/6/1 2013/10/31 0.5
WH002 IBM 55-002 2013/7/1 2013/10/31 1.2
WH001 微软 55-002 2013/7/1 2013/10/31 2.5

如何实现按照物料分组取生效日期最大值的所有记录。

[fengxiaohan211]

感觉2楼的效率比较高

[fengxiaohan211]

错了 是1楼

[Shawn]

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
create table #TEMP([vender_id] varchar(5),[vender_name] varchar(4),[ITEM] varchar(6),[cx_from_date] datetime,[cx_to_date] datetime,[price] numeric(2,1))
insert #temp
select 'WH001','微软','55-001','2013/1/1','2013/3/30','0.5' union all
select 'WH001','微软','55-001','2013/4/1','2013/5/31','0.6' union all
select 'WH001','微软','55-001','2013/6/1','2013/10/31','0.5' union all
select 'WH002','IBM','55-002','2013/1/1','2013/3/30','1' union all
select 'WH002','IBM','55-002','2013/4/1','2013/6/30','1.1' union all
select 'WH002','IBM','55-002','2013/7/1','2013/10/31','1.2' union all
select 'WH001','微软','55-002','2012/1/1','2012/3/30','2' union all
select 'WH001','微软','55-002','2012/4/1','2012/6/30','2.1' union all
select 'WH001','微软','55-002','2012/7/1','2012/12/31','2' union all
select 'WH001','微软','55-002','2013/1/1','2013/3/30','2.2' union all
select 'WH001','微软','55-002','2013/4/1','2013/6/30','2.3' union all
select 'WH001','微软','55-002','2013/7/1','2013/10/31','2.5' 

SELECT vender_id,vender_name,item,cx_from_date=CONVERT(CHAR(10),cx_from_date,120),cx_to_date=CONVERT(CHAR(10),cx_to_date,120),price
FROM #TEMP a
WHERE NOT EXISTS
(
	SELECT 1
	FROM #TEMP B
	WHERE B.vender_id = a.vender_id
		AND B.vender_name = B.vender_name
		AND a.item = b.item
		AND b.cx_from_date > a.cx_from_date
)
/*
vender_id	vender_name	item	cx_from_date	cx_to_date	price
WH001	微软	55-001	2013-06-01	2013-10-31	0.5
WH002	IBM	55-002	2013-07-01	2013-10-31	1.2
WH001	微软	55-002	2013-07-01	2013-10-31	2.5
*/

[Andy__Huang]

SELECT a.*
FROM tb a
INNER JOIN (SELECT vender_id,ITEM,MAX(cx_from_date )cx_from_date FROM tb GROUP BY vender_id,ITEM) b
	ON a.vender_id=b.vender_id and a.ITEM=b.ITEM and a.cx_from_date=b.cx_from_date
/*
vender_id vender_name ITEM   cx_from_date            cx_to_date              price 
WH002	IBM	55-002	2013-07-01 00:00:00.000	2013-10-31 00:00:00.000	1.2
WH001	微软	55-002	2013-07-01 00:00:00.000	2013-10-31 00:00:00.000	2.5
WH001	微软	55-001	2013-06-01 00:00:00.000	2013-10-31 00:00:00.000	0.5
*/

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-08-14 11:50:32
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([vender_id] varchar(5),[vender_name] varchar(4),[ITEM] varchar(6),[cx_from_date] datetime,[cx_to_date] datetime,[price] numeric(2,1))
insert [huang]
select 'WH001','微软','55-001','2013/1/1','2013/3/30',0.5 union all
select 'WH001','微软','55-001','2013/4/1','2013/5/31',0.6 union all
select 'WH001','微软','55-001','2013/6/1','2013/10/31',0.5 union all
select 'WH002','IBM','55-002','2013/1/1','2013/3/30',1 union all
select 'WH002','IBM','55-002','2013/4/1','2013/6/30',1.1 union all
select 'WH002','IBM','55-002','2013/7/1','2013/10/31',1.2 union all
select 'WH001','微软','55-002','2012/1/1','2012/3/30',2 union all
select 'WH001','微软','55-002','2012/4/1','2012/6/30',2.1 union all
select 'WH001','微软','55-002','2012/7/1','2012/12/31',2 union all
select 'WH001','微软','55-002','2013/1/1','2013/3/30',2.2 union all
select 'WH001','微软','55-002','2013/4/1','2013/6/30',2.3 union all
select 'WH001','微软','55-002','2013/7/1','2013/10/31',2.5
--------------开始查询--------------------------
SELECT *
FROM huang a
WHERE EXISTS (SELECT 1 FROM (
select  vender_id, vender_name, ITEM  , MAX(cx_from_date )cx_from_date
from [huang]
GROUP BY vender_id, vender_name, ITEM)b WHERE a.vender_id=b.vender_id
AND a.vender_name=b.vender_name AND a.item=b.item AND a.cx_from_date=b.cx_from_date)
----------------结果----------------------------
/* 
vender_id vender_name ITEM   cx_from_date            cx_to_date              price
--------- ----------- ------ ----------------------- ----------------------- ---------------------------------------
WH001     微软          55-001 2013-06-01 00:00:00.000 2013-10-31 00:00:00.000 0.5
WH002     IBM         55-002 2013-07-01 00:00:00.000 2013-10-31 00:00:00.000 1.2
WH001     微软          55-002 2013-07-01 00:00:00.000 2013-10-31 00:00:00.000 2.5
*/

[lzw_0736]

with a1 as ( select *,row_number() over (partition by vender_id order by cx_from_date desc) re from 原始表 ) select vender_id,vender_name,ITEM,cx_from_date,cx_to_date,price from a1 where re=1

[LongRui888]

引用了前面的建表语句：

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

create table [huang]([vender_id] varchar(5),[vender_name] varchar(4),[ITEM] varchar(6),[cx_from_date] datetime,[cx_to_date] datetime,[price] numeric(2,1))

insert [huang]
select 'WH001','微软','55-001','2013/1/1','2013/3/30',0.5 union all
select 'WH001','微软','55-001','2013/4/1','2013/5/31',0.6 union all
select 'WH001','微软','55-001','2013/6/1','2013/10/31',0.5 union all
select 'WH002','IBM','55-002','2013/1/1','2013/3/30',1 union all
select 'WH002','IBM','55-002','2013/4/1','2013/6/30',1.1 union all
select 'WH002','IBM','55-002','2013/7/1','2013/10/31',1.2 union all
select 'WH001','微软','55-002','2012/1/1','2012/3/30',2 union all
select 'WH001','微软','55-002','2012/4/1','2012/6/30',2.1 union all
select 'WH001','微软','55-002','2012/7/1','2012/12/31',2 union all
select 'WH001','微软','55-002','2013/1/1','2013/3/30',2.2 union all
select 'WH001','微软','55-002','2013/4/1','2013/6/30',2.3 union all
select 'WH001','微软','55-002','2013/7/1','2013/10/31',2.5
--------------开始查询--------------------------


select vender_id,vender_name,
       ITEM,cx_from_date,cx_to_date,price
from 
(
	select *,
		   row_number() over (partition by vender_id,vender_name,item 
								  order by cx_from_date desc) rownum 
	from [huang]
)r
where rownum=1