按指定顺序返回含重复项的SQL查询结果问题求助

我太懂这种明明逻辑对但结果就是不对的憋屈感了！你遇到的问题本质是：IN子句只会帮你筛选匹配的行，但完全不会在意你传入值的顺序，更不会自动保留重复项。别慌，给你几个适配不同数据库的解决方案，保证能严格按照你想要的顺序返回结果：

给你指定的每个目标货币项分配一个唯一的顺序编号，把这个带编号的列表和汇率表关联，最后按编号排序——这样就能完美保留你传入的顺序和重复项了。

方案1：适用于SQL Server、PostgreSQL #

用WITH子句构造带序号的临时货币列表，再关联查询：

WITH TargetCurrencies AS (
    SELECT 
        ToCurrencyCode, 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SortOrder
    FROM (VALUES 
        ('HKD'), ('HKD'), ('EUR'), ('HKD'), ('GBP'), ('EUR')
    ) AS T(ToCurrencyCode)
)
SELECT 
    fx.AsAtDate, 
    fx.FromCurrencyCode, 
    fx.ToCurrencyCode, 
    fx.Value
FROM tblFX fx
JOIN TargetCurrencies tc ON fx.ToCurrencyCode = tc.ToCurrencyCode
WHERE fx.FromCurrencyCode = 'USD' 
  AND fx.AsAtDate = '2018-04-24'
ORDER BY tc.SortOrder;

如果是PostgreSQL，还可以用更简洁的unnest + generate_series写法：

SELECT 
    fx.AsAtDate, 
    fx.FromCurrencyCode, 
    fx.ToCurrencyCode, 
    fx.Value
FROM tblFX fx
JOIN (
    SELECT unnest(ARRAY['HKD', 'HKD', 'EUR', 'HKD', 'GBP', 'EUR']) AS ToCurrencyCode,
           generate_series(1,6) AS SortOrder
) tc ON fx.ToCurrencyCode = tc.ToCurrencyCode
WHERE fx.FromCurrencyCode = 'USD' 
  AND fx.AsAtDate = '2018-04-24'
ORDER BY tc.SortOrder;

方案2：适用于MySQL #

MySQL可以用UNION ALL手动构造带序号的货币列表：

SELECT 
    fx.AsAtDate, 
    fx.FromCurrencyCode, 
    fx.ToCurrencyCode, 
    fx.Value
FROM tblFX fx
JOIN (
    SELECT 'HKD' AS ToCurrencyCode, 1 AS SortOrder UNION ALL
    SELECT 'HKD', 2 UNION ALL
    SELECT 'EUR', 3 UNION ALL
    SELECT 'HKD', 4 UNION ALL
    SELECT 'GBP', 5 UNION ALL
    SELECT 'EUR', 6
) tc ON fx.ToCurrencyCode = tc.ToCurrencyCode
WHERE fx.FromCurrencyCode = 'USD' 
  AND fx.AsAtDate = '2018-04-24'
ORDER BY tc.SortOrder;

额外需求：保留空值位置 #

如果某个货币在tblFX里没有对应数据，上面的写法会自动跳过。要是你想保留这个位置（显示NULL值），把JOIN改成LEFT JOIN即可：

-- 以SQL Server为例
WITH TargetCurrencies AS (
    SELECT 
        ToCurrencyCode, 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS SortOrder
    FROM (VALUES 
        ('HKD'), ('HKD'), ('EUR'), ('HKD'), ('GBP'), ('EUR')
    ) AS T(ToCurrencyCode)
)
SELECT 
    tc.SortOrder,
    '2018-04-24' AS AsAtDate,
    'USD' AS FromCurrencyCode,
    tc.ToCurrencyCode,
    fx.Value
FROM TargetCurrencies tc
LEFT JOIN tblFX fx ON fx.ToCurrencyCode = tc.ToCurrencyCode
                   AND fx.FromCurrencyCode = 'USD' 
                   AND fx.AsAtDate = '2018-04-24'
ORDER BY tc.SortOrder;

内容的提问来源于stack exchange，提问作者SmokingGun