示例架构:

RowID    Quantity    ModifiedPrice    GroupPrice    CustomPrice    SalePrice 
---------------------------------------------------------------------------- 
1        5           20.00            0             15.00          17.00 
2        2           14.00            7.00          22.00          0 
3        9           10.00            10.00         0              11.00 

基于此示例表，我希望能够以最有效/最简单的方式在四个 *Price 列之间选择最低的非零值。

示例输出:

RowID    Quantity    EndPrice 
------------------------------ 
1        5           15.00 
2        2           7.00 
3        9           10.00 

有关额外信息，数据库是 SQL Server 2005。

请您参考如下方法:

SELECT  RowId, Quantity, 
        ( 
        SELECT  MIN(price) 
        FROM    ( 
                SELECT  ModifiedPrice AS price 
                UNION ALL 
                SELECT  GroupPrice 
                UNION ALL 
                SELECT  CustomPrice 
                UNION ALL 
                SELECT  SalePrice 
                ) qi 
        WHERE   price > 0 
        ) 
FROM    mytable 

这比一堆 CASE 语句更具可读性。

但是请注意，这大约是 CASE 语句的 4 倍。

这是解析并产生正确结果的测试脚本:

CREATE TABLE #t_prices 
        ( 
        RowID INT NOT NULL, 
        Quantity INT NOT NULL, 
        ModifiedPrice FLOAT NOT NULL, 
        GroupPrice FLOAT NOT NULL, 
        CustomPrice FLOAT NOT NULL, 
        SalePrice FLOAT NOT NULL 
        ) 
 
INSERT 
INTO    #t_prices 
VALUES  (1, 5, 20.00, 0, 15.00, 17.00) 
INSERT 
INTO    #t_prices 
VALUES  (2, 2, 14.00, 7.00, 22.00, 0) 
INSERT 
INTO    #t_prices 
VALUES  (3, 9, 10.00, 10.00, 0, 11.000) 
 
 
SELECT  RowId, Quantity, 
        ( 
        SELECT  MIN(price) 
        FROM    ( 
                SELECT  ModifiedPrice AS price 
                UNION ALL 
                SELECT  GroupPrice 
                UNION ALL 
                SELECT  CustomPrice 
                UNION ALL 
                SELECT  SalePrice 
                ) qi 
        WHERE   price > 0 
        ) 
FROM    #t_prices