求几个字段里最大值
数据类型是decimal(18, 2)
IDVAL1VAL2VAL3myVal
12.348.125.203.00
21.846.315.822.26
我想得到这样的值 myVal-(VAL1 VAL2 VAL3 3个字段里值最大的那个数) 查询语句改怎么写？
[解决办法]
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( ID INT, VAL1 DECIMAL(18,2), VAL2 DECIMAL(18,2), VAL3 DECIMAL(18,2), myVal DECIMAL(18,2))GOINSERT INTO tbaSELECT 1,2.34,8.12,5.20,3.00 UNIONSELECT 2,1.84,6.31,5.82,2.26GOWITH 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 valueFROM maxvalGROUP BY ID,myValID value2 -4.051 -5.12
[解决办法]
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 CostDownfrom 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
[解决办法]
--借用1楼数据IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( ID INT, VAL1 DECIMAL(18,2), VAL2 DECIMAL(18,2), VAL3 DECIMAL(18,2), myVal DECIMAL(18,2))GOINSERT INTO tbaSELECT 1,2.34,8.12,5.20,3.00 UNIONSELECT 2,1.84,6.31,5.82,2.26GOselect id,myVal-(case when val1>val2 and val1>val3 then val1 when val2>val3 then val2 else val3 end)as decmax from tbagodrop table tba/*id decmax----------- ---------------------------------------1 -5.122 -4.05(2 行受影响)*/
[解决办法]
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
