如何从表中获得比较记录？ [英] How do I get comparative records from table?

问题描述

我想从表格中获取比较记录，其中我记录了不同项目的购买详情。假设一个项目ABC有一个唯一ID111为客户购买了很多时间现在的问题是如何在sql查询中为这个特定客户端检索此项目的最新和以前的记录？



例如：

ItemCode日期金额ClientID

111 01/01/2017 999 ABC

111 10/03/2017 1050 ABC

111 12/04/2017 1020 ABC



怎么办



For ClientID = ABC

ItemCode日期金额LastPurchase LastAmount

111 12/04/2017 1020 10/03/2017 1050 >


我尝试过：



不同的查询但没有运气。试过不同的论坛，但没有找到任何结果。

解决方案

这样的事情应该有效：

 < span class =code-keyword> WITH  cteSortedData  As  
（
  SELECT  
 ItemCode，
 日期，
金额，
 ClientID，
 ROW_NUMBER（）< span class =code-keyword> OVER （ PARTITION   BY  ClientID，ItemCode  ORDER   BY  日期  DESC ）作为 RN 
  FROM  
 YourTable 
）
  SELECT  
 ItemCode，
 MAX（ CASE  RN  WHEN   1  那么 日期  ELSE   Null   END ）作为 日期，
 MAX（ CASE  RN  WHEN   1  那么 Amount  ELSE   Null   END ）< span class =code-keyword> As 金额，
 MAX（ CASE  RN  WHEN   2  那么 日期  ELSE   Null   END ） As  LastPurchase，
 MAX（ CASE  RN  WHEN   2  那么金额 ELSE   Null   END ）  LastAmount，
 ClientID 
  FROM  
 cteSortedData 
  WHERE  
 RN < span class =code-keyword>在（ 1 ， 2 ）
  GROUP   BY  
 ItemCode，
 ClientID 
; 

I want to get the comparative records from table in which I am recording the purchase details of different items. Let say an Item "ABC" which has a unique ID "111" purchased many time for a client now the question is it that how to retrieve the latest and previous record for this item for this specific client in sql query?

Example:
ItemCode Date Amount ClientID
111 01/01/2017 999 ABC
111 10/03/2017 1050 ABC
111 12/04/2017 1020 ABC

how to get

For ClientID=ABC
ItemCode Date Amount LastPurchase LastAmount
111 12/04/2017 1020 10/03/2017 1050

What I have tried:

Different queries but no luck. Tried different forums but did not found any result.

解决方案

Something like this should work:

WITH cteSortedData As
(
    SELECT
        ItemCode,
        Date,
        Amount,
        ClientID,
        ROW_NUMBER() OVER (PARTITION BY ClientID, ItemCode ORDER BY Date DESC) As RN
    FROM
        YourTable
)
SELECT
    ItemCode,
    MAX(CASE RN WHEN 1 THEN Date ELSE Null END) As Date,
    MAX(CASE RN WHEN 1 THEN Amount ELSE Null END) As Amount,
    MAX(CASE RN WHEN 2 THEN Date ELSE Null END) As LastPurchase,
    MAX(CASE RN WHEN 2 THEN Amount ELSE Null END) As LastAmount,
    ClientID
FROM
    cteSortedData
WHERE
    RN In (1, 2)
GROUP BY
    ItemCode,
    ClientID
;

这篇关于如何从表中获得比较记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！