选择条件查询条件 [英] Select Case Query for condition

问题描述

亲爱的所有人，

感谢您阅读......

我有查询

Dear All,

Thank you for reading this...

I have query

SELECT  CONVERT(DECIMAL(18,3), ISNULL(CAST(60 AS DECIMAL(18,3))/CAST( 30 AS DECIMAL(18,3)),0))* 100 AS Efficiency
 FROM ProductionResultDisplyMst PRDM
 JOIN (SELECT PRDMID, SUM(Actual) Actual, SUM(Plans) Plans FROM ProductionResultDisplyDtls
   WHERE PRDMID IN (SELECT ID FROM ProductionResultDisplyMst WHERE PlantID=2
   AND StageID IN(2 ,3, 4, 5) AND shift=2)
   GROUP BY PRDMID ) PRDD
   ON PRDD.PRDMID = PRDM.ID
 JOIN StageMaster SM ON SM.StageID = PRDM.StageID
 WHERE PRDM.STAGEID IN (2,3, 4, 5)
 AND PRDM.PlantID = 2 AND PRDM.Shift = 2
 AND CONVERT(VARCHAR(10), PRDM.ProductionDate, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)

对于此查询，我得到如下记录

效率
80
126
109
91

如果效率> 100，我想将效率值更改为100
如果效率值小于100，则将显示相同的值.

即，
预期结果

效率
80
100
100
91

我怎样才能得到这个答案?...
期待您的答复.

For this Query I am getting the record as below

Efficiency
80
126
109
91

I want to change the Efficiency value as 100 if Efficiency >100
if Efficiency value less than 100, the same value will display.

ie,
Expected Result

Efficiency
80
100
100
91

How can i reach in this answer?...
Expecting your reply.

推荐答案

在SQL下使用CASE语句尝试以下解决方案.

Try out below solution with CASE statement in SQL.

SELECT CASE WHEN CONVERT(DECIMAL(18,3), ISNULL(CAST(60 AS DECIMAL(18,3))/CAST( 30 AS DECIMAL(18,3)),0))* 100 AS E1 > 100 THEN 100 ELSE CONVERT(DECIMAL(18,3), ISNULL(CAST(60 AS DECIMAL(18,3))/CAST( 30 AS DECIMAL(18,3)),0))* 100 AS E2 END AS Efficiency
 FROM ProductionResultDisplyMst PRDM
 JOIN (SELECT PRDMID, SUM(Actual) Actual, SUM(Plans) Plans FROM ProductionResultDisplyDtls
   WHERE PRDMID IN (SELECT ID FROM ProductionResultDisplyMst WHERE PlantID=2
   AND StageID IN(2 ,3, 4, 5) AND shift=2)
   GROUP BY PRDMID ) PRDD
   ON PRDD.PRDMID = PRDM.ID
 JOIN StageMaster SM ON SM.StageID = PRDM.StageID
 WHERE PRDM.STAGEID IN (2,3, 4, 5)
 AND PRDM.PlantID = 2 AND PRDM.Shift = 2
 AND CONVERT(VARCHAR(10), PRDM.ProductionDate, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)

希望对您有所帮助.

Hope it helps.

谢谢您阿拉贡 <code>，
与上述查询略有不同
删除"As E1"和"As E2"
即，

Thank you Aragon<code>,
A small difference from the above query
Remove "As E1" and "As E2"
ie,

SELECT CASE WHEN CONVERT(DECIMAL(18,3), ISNULL(CAST(60 AS DECIMAL(18,3))/CAST( 30 AS DECIMAL(18,3)),0))* 100  > 100 THEN 100 ELSE CONVERT(DECIMAL(18,3), ISNULL(CAST(60 AS DECIMAL(18,3))/CAST( 30 AS DECIMAL(18,3)),0))* 100  END AS Efficiency
 FROM ProductionResultDisplyMst PRDM
 JOIN (SELECT PRDMID, SUM(Actual) Actual, SUM(Plans) Plans FROM ProductionResultDisplyDtls
   WHERE PRDMID IN (SELECT ID FROM ProductionResultDisplyMst WHERE PlantID=2
   AND StageID IN(2 ,3, 4, 5) AND shift=2)
   GROUP BY PRDMID ) PRDD
   ON PRDD.PRDMID = PRDM.ID
 JOIN StageMaster SM ON SM.StageID = PRDM.StageID
 WHERE PRDM.STAGEID IN (2,3, 4, 5)
 AND PRDM.PlantID = 2 AND PRDM.Shift = 2
 AND CONVERT(VARCHAR(10), PRDM.ProductionDate, 120) = CONVERT(VARCHAR(10), GETDATE(), 120)

谢谢大家.........

Thank you guys.........

这篇关于选择条件查询条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！