Question

SQL如何筛选出客户最后一次购买商品记录

表XSCK_H
BILLNONO(单号) date(日期) ClntCode(客户代码) ClntName(客户名称)
XSCK0001 2012-02-05 001 张三
XSCK0002 2012-02-05 002 李四
XSCK0003 2012-02-10 002 李四
XSCK0004 2012-02-10 001 张三

表XSCK_B
BILLNONO(单号) GoodCode(商品代码) GoodName(商品名称) Quat(数量) Price(价格)
XSCK0001 PG0001 苹果 100 1.5
XSCK0001 XJ0001 香蕉 50 2.0
XSCK0001 HMG001 哈密瓜 10 5.5
XSCK0002 MG0001 芒果 25 3.2
XSCK0002 HMG001 哈密瓜 8 5.5
XSCK0003 PG0001 苹果 150 1.6
XSCK0003 HMG001 哈密瓜 8 5.5
XSCK0004 PG0001 苹果 20 1.8
XSCK0004 XJ0001 香蕉 30 2.1

要得出的结果为
BILLNONO(单号) date(日期) ClntCode(客户代码) ClntName(客户名称) GoodCode(商品代码) GoodName(商品名称) Quat(数量) Price(价格)
XSCK0004 2012-02-10 001 张三 PG0001 苹果 20 1.8
XSCK0004 2012-02-10 001 张三 XJ0001 香蕉 30 2.1
XSCK0001 2012-02-05 001 张三 HMG001 哈密瓜 10 5.5
XSCK0003 2012-02-10 002 李四 PG0001 苹果 150 1.6
XSCK0003 2012-02-10 002 李四 HMG001 哈密瓜 8 5.5
XSCK0002 2012-02-05 002 李四 MG0001 芒果 25 3.2

Answer 1

范例如下：

--> 测试数据：[XSCK_H]
if object_id('[XSCK_H]') is not null 
drop table [XSCK_H]
create table [XSCK_H](
[BILLNONO] varchar(8),
[date] datetime,
[ClntCode] varchar(3),
[ClntName] varchar(4)
)
insert [XSCK_H]
select 'XSCK0001','2012-02-05','001','张三' union all
select 'XSCK0002','2012-02-05','002','李四' union all
select 'XSCK0003','2012-02-10','002','李四' union all
select 'XSCK0004','2012-02-10','001','张三'
 
--> 测试数据：[XSCK_B]
if object_id('[XSCK_B]') is not null 
drop table [XSCK_B]
create table [XSCK_B](
[BILLNONO] varchar(8),
[GoodCode] varchar(6),
[GoodName] varchar(6),
[Quat] int,
[Price] numeric(2,1)
)
insert [XSCK_B]
select 'XSCK0001','PG0001','苹果',100,1.5 union all
select 'XSCK0001','XJ0001','香蕉',50,2.0 union all
select 'XSCK0001','HMG001','哈密瓜',10,5.5 union all
select 'XSCK0002','MG0001','芒果',25,3.2 union all
select 'XSCK0002','HMG001','哈密瓜',8,5.5 union all
select 'XSCK0003','PG0001','苹果',150,1.6 union all
select 'XSCK0003','HMG001','哈密瓜',8,5.5 union all
select 'XSCK0004','PG0001','苹果',20,1.8 union all
select 'XSCK0004','XJ0001','香蕉',30,2.1 
 
select 
      BILLNONO,[date],ClntCode,ClntName,GoodCode,GoodName,Quat,Price
from
     (
select 
      id=row_number()over(partition by [ClntName],[GoodName] order by [date] desc),
      a.*,b.GoodCode,b.GoodName,b.Quat,b.Price
from  
    [XSCK_H] a 
full join 
    [XSCK_B] b 
on 
    a.BILLNONO=b.BILLNONO
    )t
where 
    id=1
order by 
    ClntCode
/*
BILLNONO    date    ClntCode    ClntName    GoodCode    GoodName    Quat    Price
XSCK0001    2012-02-05 00:00:00.000    001    张三    HMG001    哈密瓜    10    5.5
XSCK0004    2012-02-10 00:00:00.000    001    张三    PG0001    苹果    20    1.8
XSCK0004    2012-02-10 00:00:00.000    001    张三    XJ0001    香蕉    30    2.1
XSCK0003    2012-02-10 00:00:00.000    002    李四    HMG001    哈密瓜    8    5.5
XSCK0002    2012-02-05 00:00:00.000    002    李四    MG0001    芒果    25    3.2
XSCK0003    2012-02-10 00:00:00.000    002    李四    PG0001    苹果    150    1.6
*/

Answer 2

CREATE TABLE XSCK_H (
BILLNONO varchar(10),
hdate varchar(10),
ClntCode varchar(10),
ClntName varchar(10)
)
GO

INSERT INTO XSCK_H
SELECT 'XSCK0001', '2012-02-05', '001', '张三' UNION ALL
SELECT 'XSCK0002', '2012-02-05', '002', '李四' UNION ALL
SELECT 'XSCK0003', '2012-02-10', '002', '李四' UNION ALL
SELECT 'XSCK0004', '2012-02-10', '001', '张三'
GO

CREATE TABLE XSCK_B (
BILLNONO varchar(10),
GoodCode varchar(10),
GoodName varchar(10),
Quat int,
Price decimal(5,2)
)
GO

INSERT INTO XSCK_B
SELECT 'XSCK0001', 'PG0001', '苹果', 100 , 1.5 UNION ALL
SELECT 'XSCK0001', 'XJ0001', '香蕉', 50 , 2.0 UNION ALL
SELECT 'XSCK0001', 'HMG001', '哈密瓜', 10 , 5.5 UNION ALL
SELECT 'XSCK0002', 'MG0001', '芒果', 25 , 3.2 UNION ALL
SELECT 'XSCK0002', 'HMG001', '哈密瓜', 8 , 5.5 UNION ALL
SELECT 'XSCK0003', 'PG0001', '苹果', 150 , 1.6 UNION ALL
SELECT 'XSCK0003', 'HMG001', '哈密瓜', 8 , 5.5 UNION ALL
SELECT 'XSCK0004', 'PG0001', '苹果', 20 , 1.8 UNION ALL
SELECT 'XSCK0004', 'XJ0001', '香蕉', 30 , 2.1
GO

With MyCTE AS (
SELECT
XSCK_H.BILLNONO,
XSCK_H.hdate,
XSCK_H.ClntCode,
XSCK_H.ClntName,
XSCK_B.GoodCode,
XSCK_B.GoodName,
XSCK_B.Quat,
XSCK_B.Price
FROM
XSCK_H JOIN XSCK_B
ON (XSCK_H.BILLNONO = XSCK_B.BILLNONO)
)
SELECT * FROM MyCTE
WHERE
NOT EXISTS (
SELECT 1
FROM MyCTE newest
WHERE
MyCTE.ClntCode = newest.ClntCode
AND MyCTE.GoodCode = newest.GoodCode
AND MyCTE.hdate < newest.hdate
)
ORDER BY
ClntCode,
hdate DESC

BILLNONO hdate ClntCode ClntName GoodCode GoodName Quat Price
---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------------------------------------
XSCK0004 2012-02-10 001 张三 PG0001 苹果 20 1.80
XSCK0004 2012-02-10 001 张三 XJ0001 香蕉 30 2.10
XSCK0001 2012-02-05 001 张三 HMG001 哈密瓜 10 5.50
XSCK0003 2012-02-10 002 李四 PG0001 苹果 150 1.60
XSCK0003 2012-02-10 002 李四 HMG001 哈密瓜 8 5.50
XSCK0002 2012-02-05 002 李四 MG0001 芒果 25 3.20

(6 行受影响)

SQL Server 2008 下测试通过