自定义SQL GROUP BY子句 [英] Custom SQL GROUP BY Clause

问题描述

我有一个非常自定义的SQL查询，实现时遇到了问题.我正在使用SQL-Server-2008.

I have a very customized SQL query that I am having problems implementing. I am using SQL-Server-2008.

此查询中只有一个表，但是我正在寻找非常具体的数据.此查询的要求是:

I have only one table in this query, but I am looking for very specific data. The requirements for this query are:

对于每个DISTINCT零件号(列)，我需要选择要选择的最新(最大)PO(列).但是，还有一个名为"Receipt"的列，如果该列完全包含一个值，则应该将PartNumber一起排除.

For each DISTINCT PartNumber (column), I need to select the NEWEST (max) PO (column) to be selected. However, there is another column named "Receipt" where if it contains a value at all, then the PartNumber should be excluded all together.

我对选择的GROUP BY子句和CASES有点熟悉，但是我不确定如何将我所知道的全部结合到一个有效的查询中...

I am somewhat familiar with GROUP BY clauses and CASES for selections, but I'm not sure how to tie all I know together into one working query...

任何帮助将不胜感激！预先感谢=).

Any help is greatly appreciated! Thanks in advance =).

推荐答案

SELECT Partnumber, MAX(PO)
FROM MyTable t1
WHERE NOT EXISTS (SELECT 1
                  FROM MyTable
                  WHERE (Receipt <> '0'
                         OR Receipt <> '')
                  AND Partnumber = t1.partnumber)
GROUP BY PartNumber

此处的NOT EXISTS将排除部件号在表中任何位置填充了收据的任何行.

The NOT EXISTS here will exclude any row that has a partnumber for which a receipt is populated anywhere in the table.

这篇关于自定义SQL GROUP BY子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！