数据类型是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 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
------解决方案--------------------
- 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 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
------解决方案--------------------
- SQL code
--借用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 行受影响)*/
------解决方案--------------------
- 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