日期:2014-05-17  浏览次数:20603 次

求几个字段里最大值
数据类型是decimal(18, 2)

ID VAL1 VAL2 VAL3 myVal

1 2.34 8.12 5.20 3.00

2 1.84 6.31 5.82 2.26



我想得到这样的值 myVal-(VAL1 VAL2 VAL3 3个字段里值最大的那个数) 查询语句改怎么写?

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    ID INT,
    VAL1 DECIMAL(18,2),
    VAL2 DECIMAL(18,2),
    VAL3 DECIMAL(18,2),
    myVal DECIMAL(18,2)
)
GO
INSERT INTO tba
SELECT 1,2.34,8.12,5.20,3.00 UNION
SELECT 2,1.84,6.31,5.82,2.26
GO

WITH maxval AS
(
    SELECT ID,VAL1,myVal
    FROM tba
    UNION ALL
    SELECT ID,VAL2,myVal
    FROM tba
    UNION ALL
    SELECT ID,VAL3,myVal
    FROM tba
)

SELECT ID,myVal - MAX(VAL1) AS value
FROM maxval
GROUP BY ID,myVal

ID    value
2    -4.05
1    -5.12

------解决方案--------------------
SQL code

select top 10 A.id,A.Dept,A.Line,A.ProductName,A.Model,A.FixedAssetCode,A.Symptom1,A.Remark,A.scheme_1,A.Remark_1,QuotedPrice1_1,QuotedPrice1_2,A.scheme_2,
A.Remark_2,QuotedPrice2_1,QuotedPrice2_2,A.scheme_3,A.Remark_3,QuotedPrice3_1,QuotedPrice3_2,A.scheme_Name,A.USE_Price,A.USE_Price- MAX(VAL1) AS CostDown

from maxval AS A INNER JOIN dbo.Repair ON A.id = Repair.id 
GROUP by A.id,A.Dept,A.Line,A.ProductName,A.Model,A.FixedAssetCode,A.Symptom1,A.Remark,A.scheme_1,A.Remark_1,QuotedPrice1_1,QuotedPrice1_2,A.scheme_2,
A.Remark_2,QuotedPrice2_1,QuotedPrice2_2,A.scheme_3,A.Remark_3,QuotedPrice3_1,QuotedPrice3_2,A.scheme_Name,A.USE_Price

------解决方案--------------------
SQL code
--借用1楼数据
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    ID INT,
    VAL1 DECIMAL(18,2),
    VAL2 DECIMAL(18,2),
    VAL3 DECIMAL(18,2),
    myVal DECIMAL(18,2)
)
GO
INSERT INTO tba
SELECT 1,2.34,8.12,5.20,3.00 UNION
SELECT 2,1.84,6.31,5.82,2.26
GO
select id,myVal-(
case when val1>val2 and val1>val3 then val1
    when val2>val3 then val2
    else val3 end
)as decmax from tba
go
drop table tba
/*
id          decmax
----------- ---------------------------------------
1           -5.12
2           -4.05

(2 行受影响)

*/

------解决方案--------------------
SQL code

select id,
       myVal-(
       case when val1>val2 and val2>val3 then val1
            when val2>val3 and val2>val1 then val2
       else val3 end
)from tab