客户代码 客户型号 客户订单号 本厂型号 币种 单价 数量 下单日期
YTILZ 2X01900FC1 YT-1303028 FP2-3883 USD 1.326 15447 2013-04-02 18:15:00
YTILZ 2X01900FC1 YT-1304001 FP2-3883 USD 1.326 3000 2013-04-08 09:42:00
YTILZ 2X01900FC1 YT-1304001 FP2-3883 USD 1.326 4460 2013-04-08 09:44:00
想得到同一客户，同一型号，最新的客户订单号，以及最新的单价和数量信息。下面是效果：
YTILZ 2X01900FC1 YT-1304001 FP2-3883 USD 1.326 4460 2013-04-08 09:44:00
请问SQL语句怎么写？
------解决方案--------------------
select *
from tab a
where exists(select 1 from (select 客户代码,max(下单日期)下单日期 from tab group by 客户代码)b where a.客户代码=b.客户代码 and a.下单日期=b.下单日期)
------解决方案--------------------
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-21 12:05:30
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise 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]([客户代码] varchar(5),[客户型号] varchar(10),[客户订单号] varchar(10),[本厂型号] varchar(8),[币种] varchar(3),[单价] numeric(4,3),[数量] int,[下单日期] datetime)
insert [huang]
select 'YTILZ','2X01900FC1','YT-1303028','FP2-3883','USD',1.326,15447,'2013-04-02 18:15:00' union all
select 'YTILZ','2X01900FC1','YT-1304001','FP2-3883','USD',1.326,3000,'2013-04-08 09:42:00' union all
select 'YTILZ','2X01900FC1','YT-1304001','FP2-3883','USD',1.326,4460,'2013-04-08 09:44:00'
--------------开始查询--------------------------
select *
from [huang] a
where exists(select 1 from (select 客户代码,max(下单日期)下单日期 from [huang] group by 客户代码)b where a.客户代码=b.客户代码 and a.下单日期=b.下单日期)
----------------结果----------------------------
/*
客户代码 客户型号 客户订单号 本厂型号 币种 单价 数量 下单日期
----- ---------- ---------- -------- ---- --------------------------------------- ----------- -----------------------
YTILZ 2X01900FC1 YT-1304001 FP2-3883 USD 1.326 4460 2013-04-08 09:44:00.000
*/
------解决方案--------------------
select
*
from
tb t
where
not exists(select 1 from tb where 客户代码=t.客户代码 and 客户型号=t.客户型号 and 下单日期>t.下单日期)
------解决方案--------------------
if object_id('tb') is not null drop table tb
go
create table tb([客户代码] varchar(5),[客户型号] varchar(10),[客户订单号] varchar(10),[本厂型号] varchar(8),[币种] varchar(3),[单价] numeric(4,3),[数量] int,[下单日期] datetime)
insert tb
select 'YTILZ','2X01900FC1','YT-1303028','FP2-3883','USD',1.326,15447,'2013-04-02 18:15:00' union all
select 'YTILZ','2X01900FC1','YT-1304001','FP2-3883','USD',1.326,3000,'2013-04-08 09:42:00' union all
select 'YTILZ','2X01900FC1','YT-1304001','FP2-3883','USD',1.326,4460,'2013-04-08 09:44:00'
select 客户代码,客户型号,客户订单号,本厂型号,币种,单价,数量,下单日期
from
(
select *,
row_number() over(partition by 客户代码,客户型号
order by 下单日期 desc) as rownum
from tb
)a
where rownum = 1
/*
客户代码 客户型号 客户订单号 本厂型号 币种 单价 数量 下单日期
YTILZ 2X01900FC1 YT-1304001 FP2-3883 USD 1.326 4460 2013-04-08 09:44:00.000