将 NULL 视为可能的最大值

Question

我想获取基于Code分组的最大事务编号的行。

CREATE TABLE SaleOrder
(
TransactionNo Int,
SaleOrderDate DATE,
Code VARCHAR(25),
Quantity INT,
TotalAmount Numeric(18,2),
Remarks VARCHAR(25)
)
INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-001-OCT-18',  6, 2500, 'Hello');
INSERT INTO SaleOrder VALUES (1,    '2018-10-01', 'SO-001-OCT-18',  8, 2600, 'Hello');
INSERT INTO SaleOrder VALUES (2,    '2018-10-01', 'SO-001-OCT-18', 12, 3400, 'Hello');
INSERT INTO SaleOrder VALUES (3,    '2018-10-01', 'SO-001-OCT-18',  9, 2900, 'Hello');
INSERT INTO SaleOrder VALUES (4,    '2018-10-01', 'SO-001-OCT-18',  2,  900, 'Hello');
INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-002-OCT-18',  6, 2500, 'Hello');
INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-003-OCT-18',  6, 2500, 'Hello');
INSERT INTO SaleOrder VALUES (0,    '2018-10-01', 'SO-004-OCT-18',  6, 2500, 'Hello');

SELECT * FROM SaleOrder O
WHERE TransactionNo  = (SELECT MAX(ISNULL(TransactionNo, 1)) FROM SaleOrder GROUP BY Code)

在这里，当 TransactionNo 为 NULL 时，它不会针对它返回任何记录，而它也应该返回该记录。

Answer 1

绝对没有理由将 NULL 视为最大可能值。您可以随时使用ROW_NUMBER技巧：

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY TransactionNo DESC) AS RN
FROM SaleOrder
)
SELECT * FROM cte
WHERE RN = 1

结果：

| TransactionNo | SaleOrderDate | Code          | Quantity | TotalAmount | Remarks | RN |
|---------------|---------------|---------------|----------|-------------|---------|----|
| 4             | 2018-10-01    | SO-001-OCT-18 | 2        | 900.00      | Hello   | 1  |
| NULL          | 2018-10-01    | SO-002-OCT-18 | 6        | 2500.00     | Hello   | 1  |
| NULL          | 2018-10-01    | SO-003-OCT-18 | 6        | 2500.00     | Hello   | 1  |
| 0             | 2018-10-01    | SO-004-OCT-18 | 6        | 2500.00     | Hello   | 1  |

Answer 2

当 TransactionNo 为 NULL 并且查询返回多个无法分配给筛选器的行时

以下内容可能会有所帮助

SELECT * FROM SaleOrder O
WHERE TransactionNo  = (SELECT TOP 1 MAX(ISNULL(NULL, 1)) FROM SaleOrder GROUP BY Code)

请注意，这可能会占用任何具有 TransactionNo 具有 NULL 值的记录。 隔离 TransactionNo 筛选器的逻辑将更容易扩展和维护。示例如下：

DECLARE @TransactionNo int
SELECT TOP 1 @TransactionNo = MAX(ISNULL(TransactionNo, 1)) FROM SaleOrder GROUP BY Code -- (OR) Logic here
SELECT * FROM SaleOrder O
WHERE TransactionNo  = @TransactionNo

Answer 3

当你在where子句中使用=选择时，它是完全错误的，因为这可能是你有多个记录，所以你必须像这样更改你的代码：

SELECT MAX(ISNULL(TransactionNo, 1)),code FROM SaleOrder O
GROUP BY Code

但是如果你只想返回一条记录，你可以像这样使用它：

SELECT * FROM SaleOrder O
WHERE TransactionNo  = (SELECT TOP 1 MAX(ISNULL(NULL, 1)) FROM SaleOrder GROUP BY Code)

Answer 4

我认为这个ISNULL检查应该可以解决您的问题并将=替换为IN子查询可以返回多条记录

WHERE ISNULL(TransactionNo, 1) IN

Answer 5

试试这个：

select TransactionNo,SaleOrderDate,Code,Quantity,TotalAmount,Remarks from (
select TransactionNo,SaleOrderDate,Code,Quantity,TotalAmount,Remarks, 
row_number() over (partition by code order by transactionno desc) rn 
from (
select TransactionNo,SaleOrderDate,Code,Quantity,TotalAmount,Remarks,
coalesce(transactionno, count(*) over (partition by code) + 1) transactionno2
from SaleOrder
) a
) a where rn = 1

解释：

通过这一行coalesce(transactionno, count(*) over (partition by code) + 1) transactionno2我为每个组分配最大值(按code分区(，其中它为 null。但请注意，当您有两个NULL时，在这种情况下，行将被捆绑，这将是不确定的。

Answer 6

下面的代码将为您提供比您请求的更多的信息，您可以使用它，如果有任何问题，请添加一些评论。

CREATE TABLE #SaleOrder
(
TransactionNo Int,
#SaleOrderDate DATE,
Code VARCHAR(25),
Quantity INT,
TotalAmount Numeric(18,2),
Remarks VARCHAR(25)
)
INSERT INTO #SaleOrder VALUES (NULL, '2018-10-01', 'SO-001-OCT-18', 6, '2500', 'Hello');
INSERT INTO #SaleOrder VALUES (1, '2018-10-01', 'SO-001-OCT-18', 8, '2600', 'Hello');
INSERT INTO #SaleOrder VALUES (2, '2018-10-01', 'SO-001-OCT-18', 12, '3400', 'Hello');
INSERT INTO #SaleOrder VALUES (3, '2018-10-01', 'SO-001-OCT-18', 9, '2900', 'Hello');
INSERT INTO #SaleOrder VALUES (4, '2018-10-01', 'SO-001-OCT-18', 2, '900', 'Hello');
INSERT INTO #SaleOrder VALUES (NULL, '2018-10-01', 'SO-002-OCT-18', 6, '2500', 'Hello');
INSERT INTO #SaleOrder VALUES (NULL, '2018-10-01', 'SO-003-OCT-18', 6, '2500', 'Hello');
INSERT INTO #SaleOrder VALUES (0, '2018-10-01', 'SO-004-OCT-18', 6, '2500', 'Hello');
-- final select
SELECT top 1 -- optional, if you want to return 1 record
Code,
sum(Quantity) as totalQuantity, 
sum(TotalAmount) as totallAmount, 
count(1) as totalOrdersPerCode
FROM #SaleOrder O
group by Code
order by count(1) desc  
-- drop temp table
drop table #SaleOrder