您的位置: 首页  >  技术问答  >  选择条件查询条件

选择条件查询条件

分类: 技术问答 • 2022-04-15 09:29:26
问题描述：

亲爱的所有人，

感谢您阅读......

我有查询

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.........


相关推荐