需要基于某些条件的 sql 查询 [英] Need the sql query based on some condition

问题描述

select CardCode, ItemCode, T0.DocDate, ''[CustomerType]  from OINV T0 inner Join INV1 T1 on T1.DocEntry = T0.DocEntry and year(T0.DocDate) >= year(getdate()) -4 

我又添加了一个名为CustomerType"的列，看起来像新"、现有"、另一种产品"

I have added one more column called 'CustomerType' will look like 'New', 'Existing', 'One more Product'

根据docdate 和itemcode 列检查的条件:

Conditions to check based on the docdate and itemcode column:

1. 例如现在是 2020 年，如果 2019 年没有销售，那么 customertype 是 = ‘New’ else 'Existing' .需要根据'CardCode'列进行检查

1. e.g. Now is year 2020, if 2019 there is no sales, then customertype is = ‘New’ else 'Existing' . Need to check based on 'CardCode' column

例如:那些客户类型现有"我需要检查他们之前是否购买过商品代码，如果他们购买了，那么客户类型保持为现有"，如果不是，则应反映为另外一个产品"'.需要根据'ItemCode'列进行检查

eg: Those customer type 'existing' i need to check if they purchase the itemcode before or not if they purchased then customertype remains as 'existing' if not should reflect as 'One more Product'. Need to check based on 'ItemCode' column

样本结果集:

CardCode    ItemCode    DocDate     CustomerType
C-SGD-2748  V0796-0038  2017-01-24 
C-SGD-1489  V0796-0066  2020-06-10 
C-SGD-2748  V0796-0106  2019-01-15  
C-SGD-1489  V0796-0130  2019-05-17 
C-SGD-2652  V0805-0001  2016-12-08 

推荐答案

可以使用case和窗口函数:

select t.*,
       (case when min(docdate) over (partition by cardcode) >= '2020-01-01'
             then 'New'
             when row_number() over (partition by cardcode, itemcode order by docdate) > 1
             then 'existing'
             else 'one more product'
        end) as customertype

我发现这些类型相当混乱.但是，这似乎正是您所描述的.

I find these types to be rather confusing. However, this seems to be exactly what you are describing.

这篇关于需要基于某些条件的 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！