求一分组取最大值的疑难语句
本帖最后由 zhongguohld 于 2013-08-14 11:48:30 编辑 原始表
vender_idvender_nameITEMcx_from_datecx_to_dateprice
WH001微软55-0012013/1/12013/3/300.5
WH001微软55-0012013/4/12013/5/310.6
WH001微软55-0012013/6/12013/10/310.5
WH002IBM55-0022013/1/12013/3/301
WH002IBM55-0022013/4/12013/6/301.1
WH002IBM55-0022013/7/12013/10/311.2
WH001微软55-0022012/1/12012/3/302
WH001微软55-0022012/4/12012/6/302.1
WH001微软55-0022012/7/12012/12/312
WH001微软55-0022013/1/12013/3/302.2
WH001微软55-0022013/4/12013/6/302.3
WH001微软55-0022013/7/12013/10/312.5
结果表
WH001微软55-0012013/6/12013/10/310.5
WH002IBM55-0022013/7/12013/10/311.2
WH001微软55-0022013/7/12013/10/312.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
*/
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_idvender_nameitemcx_from_datecx_to_dateprice
WH001微软55-0012013-06-012013-10-310.5
WH002IBM55-0022013-07-012013-10-311.2
WH001微软55-0022013-07-012013-10-312.5
*/