自定义SQL GROUP BY子句

本文介绍了自定义SQL GROUP BY子句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个非常自定义的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.