查询显示比 #temp 表中可用的少 1 [英] Query shows 1 less than what's available in the #temp table

问题描述

假设#temp 有 4 行，输出为 3(应该是 4)，如果有 1，则输出为 0.

So let's say #temp has 4 rows, the output will be 3(should be 4), if it has 1, then output will be 0.

我不太确定发生了什么.想知道是否有人可以通过查看查询来判断.

I'm not quite sure what's going on. Wondering if anyone can tell by looking at the query.

SELECT TH.TnnNumber,
    CASE WHEN COUNT(DISTINCT TL.DiscountCodeID) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(DC.Value) AS NVARCHAR(50)) END AS Discount,
    CASE WHEN TS.SpinID > 4 THEN 'Has Specifics, View Tnn' ELSE TS.Value END AS Spin,
    CASE WHEN COUNT(DISTINCT TL.Commission_HMM) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission_HMM, 12), 'Default Comm')) AS NVARCHAR(50)) END AS Commission_HMM,
    CASE WHEN COUNT(DISTINCT TL.Commission) > 1 THEN 'Varies, View Tnn' ELSE CAST(MAX(ISNULL(str(TL.Commission, 12), 'Default Comm')) AS NVARCHAR(50)) END AS Commission,
    TL.TnnID
FROM [DBSERV].Tnn.DBO.Tnn_Header AS TH
LEFT JOIN [DBSERV].Tnn.DBO.Tnn_LINE AS TL
    ON TH.TnnID = TL.TnnID
LEFT JOIN [DBSERV].Tnn.DBO.Tnn_Spin AS TS
    ON TH.SpinID = TS.SpinID
LEFT JOIN [DBSERV].Tnn.DBO.Tnn_DiscountCode AS DC
    ON TL.DiscountCodeID = DC.DiscountCodeID
INNER JOIN #temp AS T
    ON T.Tnn = TH.TnnNumber
GROUP BY TH.TnnNumber,
    TS.SpinID,
    TS.Value,
    TL.TnnID

所需输出:

+-----------+----------+-------------------------+----------------+--------------+-------+
| TnnNumber | Discount |          Spin           | Commission_HMM |  Commission  | TnnID |
+-----------+----------+-------------------------+----------------+--------------+-------+
|    902054 | 50-20-9  | Has Specifics, View Tnn | Default Comm   | Default Comm |  5855 |
|    907616 | 50-20-20 | Half                    | Default Comm   |            2 |  6111 |
|    910019 | 50-20-9  | Half                    | Default Comm   | Default Comm |  7015 |
|    915919 | 50-20-9  | Half                    | Default Comm   | Default Comm |  7015 |
+-----------+----------+-------------------------+----------------+--------------+-------+

实际输出:

+-----------+----------+-------------------------+----------------+--------------+-------+
| TnnNumber | Discount |          Spin           | Commission_HMM |  Commission  | TnnID |
+-----------+----------+-------------------------+----------------+--------------+-------+
|    902054 | 50-20-9  | Has Specifics, View Tnn | Default Comm   | Default Comm |  5855 |
|    907616 | 50-20-20 | Half                    | Default Comm   |            2 |  6111 |
|    910019 | 50-20-9  | Half                    | Default Comm   | Default Comm |  7015 |
+-----------+----------+-------------------------+----------------+--------------+-------+

#temp 中的值

Tnn
902054
907616
910019
915919

右连接:

+-----------+----------+-------------------------+----------------+--------------+-------+
| TnnNumber | Discount |          Spin           | Commission_HMM |  Commission  | TnnID |
+-----------+----------+-------------------------+----------------+--------------+-------+
| NULL      | NULL     | NULL                    | Default Comm   | Default Comm | NULL  |
| 902054    | 50-20-9  | Has Specifics, View Tnn | Default Comm   | Default Comm | 5855  |
| 907616    | 50-20-20 | Half                    | Default Comm   |            2 | 6111  |
| 910019    | 50-20-9  | Half                    | Default Comm   | Default Comm | 7015  |
+-----------+----------+-------------------------+----------------+--------------+-------+

推荐答案

当您的 RIGHT JOIN 查询显示您的

As your RIGHT JOIN query show your

[DBSERV].Tnn.DBO.Tnn_Header AS TH

没有 ID = 915919 的行，这就是 RIGHT JOIN 返回 NULL 和 INNER JOIN 的原因> 少返回一行

Doesn't have the row with ID = 915919 that is why RIGHT JOIN return NULL and INNER JOIN return a row less

试试

SELECT *
FROM [DBSERV].Tnn.DBO.Tnn_Header AS TH
WHERE TH.TnnNumber = 915919

现在您必须检查您如何创建 #temp 并验证您使用的是与 Tnn_Header

Now you have to check how are you creating #temp and validate you are using the same ID related to Tnn_Header

这篇关于查询显示比 #temp 表中可用的少 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！