为什么是IN操作显示错误的结果

Question

我有这个表BarCode，当我做这样的查询

SELECT Barcode 
FROM BarcodeTable 
WHERE BarCode IN 
            (
                '53-3-1',
                '51-1-1',
                '51-2-1',
                '50-10-1',
                '50-8-1',
                '51-4-1',
                '50-1-1'
            )

为什么我得到

53-3-1
50-1-1
50-8-1
50-10-1
51-1-1
51-2-1
51-4-1

代替????

53-3-1
51-1-1
51-2-1
50-10-1
50-8-1
51-4-1
50-1-1

为什么SQL改变条形码的顺序?因为SQL对它们排序的方式(自己)会导致sp_executesql执行的动态查询出现错误，该查询依赖于我发送输入的顺序。为什么SQL自己改变输入顺序?

51-4-1

Answer 1

IN并不意味着最终结果的任何排序:

这个SQL:

... WHERE x IN (1, 2, 3)

应该产生与下面相同的结果:

... WHERE x IN (3, 2, 1)

IN子句只过滤行，基本上，对于每一行，您可以认为它只是问"这行应该是结果集的一部分吗?"。

要得到一个特定的顺序，你必须在你的语句中添加一个ORDER BY子句。

要获得特定的顺序，可以使用内联表:

SELECT Barcode
FROM BarcodeTable
    inner join (values
        ('*53-3-1*',  1),
        ('*51-1-1*',  2),
        ('*51-2-1*',  3),
        ('*50-10-1*', 4),
        ('*50-8-1*',  5),
        ('*51-4-1*',  6),
        ('*50-1-1*',  7)) as DummyTable (value, sortorder)
    on BarCode = value
order by
    sortorder

这将按照DummyTable的sortorder字段排序。

你不能欺骗SQL Server使用IN子句本身进行排序，你必须以某种形式添加ORDER BY。

您还可以使用CASE WHEN ...表达式来根据条形码生成排序顺序值:

SELECT Barcode
FROM BarcodeTable
WHERE BarCode IN ('*53-3-1*', '*51-1-1*', '*51-2-1*', '*50-10-1*', '*50-8-1*', '*51-4-1*', '*50-1-1*')
ORDER BY
    CASE BarCode
        WHEN '*53-3-1*'  THEN 1
        WHEN '*51-1-1*'  THEN 2
        WHEN '*51-2-1*'  THEN 3
        WHEN '*50-10-1*' THEN 4
        WHEN '*50-8-1*'  THEN 5
        WHEN '*51-4-1*'  THEN 6
        WHEN '*50-1-1*'  THEN 7
    END

正如Lieven的评论所建议的，在SQL Server 2005上有一个替代方案，使用WITH子句:

WITH DummyTable (value, sortorder) AS (
    SELECT '*53-3-1*' AS value,  1 AS sortorder
    UNION ALL
    SELECT '*51-1-1*',  2
    UNION ALL
    SELECT '*51-2-1*',  3
    UNION ALL
    SELECT '*50-10-1*', 4
    UNION ALL
    SELECT '*50-8-1*',  5
    UNION ALL
    SELECT '*51-4-1*',  6
    UNION ALL
    SELECT '*50-1-1*',  7
)
SELECT Barcode
FROM BarcodeTable
    inner join DummyTable
    on BarCode = value
order by
    sortorder

(注意，我不是WITH使用方面的专家，上面只是我拼凑的东西，但似乎有效)

Answer 2

除非指定了顺序语句，否则不应该依赖于输出中的特定顺序。您必须提供ORDER BY来获得您所期望的。

Answer 3

要使您的查询顺序如您所期望的那样，您需要添加ORDER BY，否则排序将不可靠。这样的东西应该工作，但根据你的条形码量，它可能最终更容易在代码中正确订购，一旦结果返回;

SELECT Barcode 
FROM BarcodeTable 
WHERE BarCode IN ('*53-3-1*','*51-1-1*','*51-2-1*','*50-10-1*',
                  '*50-8-1*','*51-4-1*','*50-1-1*')
ORDER BY CASE BarCode WHEN '*53-3-1*'  THEN 1
                      WHEN '*51-1-1*'  THEN 2
                      WHEN '*51-2-1*'  THEN 3
                      WHEN '*50-10-1*' THEN 4
                      WHEN '*50-8-1*'  THEN 5
                      WHEN '*51-4-1*'  THEN 6
                      WHEN '*50-1-1*'  THEN 7
                      END

Answer 4

尝试以下代码

SELECT Barcode ,CASE Col_Order WHEN '53-3-1'  THEN 1
                      WHEN '51-1-1'  THEN 2
                      WHEN '51-2-1'  THEN 3
                      WHEN '50-10-1' THEN 4
                      WHEN '50-8-1'  THEN 5
                      WHEN '51-4-1'  THEN 6
                      WHEN '50-1-1'  THEN 7 
                      END     
FROM BarcodeTable 
WHERE BarCode IN 
            (
                '53-3-1',
                '51-1-1',
                '51-2-1',
                '50-10-1',
                '50-8-1',
                '51-4-1',
                '50-1-1'
            )    
ORDER BY Col_Order