TSQL i无法在日期上添加适当的筛选器/
TSQL i无法在日期上添加适当的筛选器/
提问于 2016-11-24 10:57:36
在下面的查询中，您将看到一个Item: 4500520400500
在这个查询中，我只想列出这个项目的规则和最新的日期10-10-2016在这个例子中。或者使用最新的RecID
我无法使它与select查询中的select一起工作。在这种情况下，我得到了所有的项目，但是我只想要带有最后一个recid.的项目
我试过使用WHERE Select MAX( RECID )，但这只是从整个表中返回记录，而不是在已经做出选择的情况下返回1张记录。
SELECT A.DATAAREAID, A.ITEMID
, B.ACTIVATIONDATE, B.PRICE, B.CREATEDDATETIME, B.RECID
, 'DEFAULTORDERTYPE' = CASE
WHEN C.DEFAULTORDERTYPE = 0 THEN 'Purch'
WHEN C.DEFAULTORDERTYPE = 1 THEN 'Production'
WHEN C.DEFAULTORDERTYPE = 2 THEN 'Transfer'
WHEN C.DEFAULTORDERTYPE = 3 THEN 'Kanban'
ELSE 'Bestaat niet'
END
, 'PRODUCTTYPE' = CASE
WHEN D.PRODUCTTYPE = 1 THEN 'Item'
WHEN D.PRODUCTTYPE = 2 THEN 'Service'
ELSE 'Bestaat niet'
END
, E.MODELGROUPID AS 'Item Model Group'
FROM INVENTTABLE AS A
LEFT JOIN INVENTITEMPRICE AS B ON A.ITEMID = B.ITEMID AND A.PARTITION = B.PARTITION AND A.DATAAREAID = B.DATAAREAID
LEFT JOIN INVENTITEMSETUPSUPPLYTYPE AS C ON A.ITEMID = C.ITEMID AND A.DATAAREAID = C.ITEMDATAAREAID AND A.PARTITION = C.PARTITION
LEFT JOIN ECORESPRODUCT AS D ON A.PRODUCT = D.RECID AND A.PARTITION = D.PARTITION
LEFT JOIN INVENTMODELGROUPITEM AS E ON A.ITEMID = E.ITEMID AND A.DATAAREAID = E.ITEMDATAAREAID
WHERE A.KNK_ITEMHIDDEN = 0
AND (B.PRICE = 0 OR B.price IS NULL)
ORDER BY A.DATAAREAID, A.ITEMID, B.ACTIVATIONDATE 回答 1
Stack Overflow用户
回答已采纳
发布于 2016-11-24 17:30:07
您可以使用一个窗口函数来获取每个ITEMID的最大B.CREATEDDATETIME值，然后只返回B.CREATEDDATETIME值与最大值匹配的记录：
SELECT records.DATAAREAID,
records.ITEMID,
records.ACTIVATIONDATE,
records.PRICE
records.CREATEDDATETIME,
records.RECID,
records.DEFAULTORDERTYPE,
records.PRODUCTTYPE,
records.[Item Model Group]
FROM
(
SELECT A.DATAAREAID,
A.ITEMID,
B.ACTIVATIONDATE,
B.PRICE,
B.CREATEDDATETIME,
MAX(B.CREATEDDATETIME) OVER (PARTITION BY A.ITEMID) MAXCREATEDDATETIME,
B.RECID,
'DEFAULTORDERTYPE' = CASE WHEN C.DEFAULTORDERTYPE = 0 THEN 'Purch'
WHEN C.DEFAULTORDERTYPE = 1 THEN 'Production'
WHEN C.DEFAULTORDERTYPE = 2 THEN 'Transfer'
WHEN C.DEFAULTORDERTYPE = 3 THEN 'Kanban'
ELSE 'Bestaat niet'
END,
'PRODUCTTYPE' = CASE WHEN D.PRODUCTTYPE = 1 THEN 'Item'
WHEN D.PRODUCTTYPE = 2 THEN 'Service'
ELSE 'Bestaat niet'
END,
E.MODELGROUPID AS 'Item Model Group'
FROM INVENTTABLE AS A
LEFT JOIN INVENTITEMPRICE AS B ON A.ITEMID = B.ITEMID
AND A.PARTITION = B.PARTITION
AND A.DATAAREAID = B.DATAAREAID
LEFT JOIN INVENTITEMSETUPSUPPLYTYPE AS C ON A.ITEMID = C.ITEMID
AND A.DATAAREAID = C.ITEMDATAAREAID
AND A.PARTITION = C.PARTITION
LEFT JOIN ECORESPRODUCT AS D ON A.PRODUCT = D.RECID
AND A.PARTITION = D.PARTITION
LEFT JOIN INVENTMODELGROUPITEM AS E ON A.ITEMID = E.ITEMID
AND A.DATAAREAID = E.ITEMDATAAREAID
WHERE A.KNK_ITEMHIDDEN = 0
AND (B.PRICE = 0 OR B.price IS NULL)
) records
WHERE records.CREATEDDATETIME = records.MAXCREATEDDATETIME
ORDER BY records.DATAAREAID,
records.ITEMID,
records.ACTIVATIONDATE这类似于给出here的答案。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40784612
复制相关文章
相似问题
腾讯云开发者
