select
A.ITEM_CODE as 品号,
A.ITEM_NAME as 品名,
A.ITEM_SPECIFICATION as 规格,
E.FEATURE_GROUP_NAME as 群组名称,
case A.UNIT_MODE when 'S' then '单一计量' when 'D' then '双存货计量' else '' end  as 计量体系,
CONVERT(varchar(10),L.CreateDate,121) AS 最后一次采购日期,
K.UNIT_NAME as 采购单位,
C.UNIT_NAME as 库存单位,
B.QTY_BU AS 库存单位数量,
D.UNIT_NAME as 换算单位,
B.QTY_CBU  as 换算单位数量,
F.USER_NAME as 创建者,
CONVERT(varchar(10),A.CreateDate,121) as 创建日期,
G.USER_NAME as 最后修改者,
CONVERT(varchar(10),A.LastModifiedDate,121) as 最后修改日期,
H.USER_NAME as 修改者,
CONVERT(varchar(10),A.ModifiedDate,121) as 修改日期,
I.USER_NAME as 审核人,
CONVERT(varchar(10),A.ApproveDate,121) as 审核日期

from ITEM A  --品号主表
left join ITEM_UNITS_CONVERSION B on A.ITEM_BUSINESS_ID=B.ITEM_BUSINESS_ID  --双存货计量明细
left join UNIT C on B.BASE_UNIT_ID=C.UNIT_ID  --单位档案 关联 库存单位
left join UNIT D on B.CONVERSION_UNIT_ID=D.UNIT_ID  --单位档案 关联 换算单位
left join FEATURE_GROUP E on A.FEATURE_GROUP_ID=E.FEATURE_GROUP_ID --品号群组
left join [USER] F ON A.CreateBy = F.USER_ID --用户档案 关联 创建人
left join [USER] G ON A.LastModifiedBy = G.USER_ID --用户档案 关联 最后修改人
left join [USER] H ON A.ModifiedBy = H.USER_ID --用户档案 关联 修改人
left join [USER] I ON A.ApproveBy= I.USER_ID --用户档案 关联 审核人
left join ITEM_PURCHASE J on A.ITEM_BUSINESS_ID=J.ITEM_ID  --品号采购明细
left join UNIT K on J.PUR_UNIT_ID=K.UNIT_ID  --单位档案 关联 采购单位
LEFT JOIN (
  SELECT ITEM_CODE, CreateDate FROM (
    SELECT C.ITEM_CODE, C.ITEM_NAME, C.ITEM_SPECIFICATION, B.CreateDate,ROW_NUMBER() OVER (PARTITION BY C.ITEM_CODE ORDER BY B.CreateDate DESC) AS rn
    FROM PURCHASE_ORDER_D A LEFT JOIN PURCHASE_ORDER B ON A.PURCHASE_ORDER_ID = B.PURCHASE_ORDER_ID LEFT JOIN ITEM C ON A.ITEM_ID = C.ITEM_BUSINESS_ID) AS T WHERE T.rn = 1
) AS L ON A.ITEM_CODE = L.ITEM_CODE

WHERE A.ApproveStatus = 'Y'
AND (
    (A.UNIT_MODE = 'D' AND B.BASE_UNIT_ID <> B.CONVERSION_UNIT_ID)
    OR (A.UNIT_MODE = 'S' AND B.BASE_UNIT_ID = B.CONVERSION_UNIT_ID)
)
--启用过双存货计后台修改后可能即使是变成单一计量后台还是有记录，所以要过滤下
ORDER BY A.CreateDate DESC