英文:
How to set formula with condition query in SQL Server
问题
如何查询发票只有prodID = 1或2，然后执行price * amount，否则执行price * amount + 1%。
英文:
| invoice | ProdId | Price | amount | result |
|---|---|---|---|---|
| 1 | 1 | 10 | 2 | 20.2 |
| 1 | 2 | 20 | 1 | 20.2 |
| 1 | 3 | 10 | 2 | 20.2 |
| 2 | 1 | 10 | 1 | 10 |
| 2 | 2 | 20 | 2 | 20 |
How to query if an invoice has only prodID = 1 or 2 then price * Amount else price * amount + 1%.
答案1
得分: 2
如果发票只需要具有1或2中的一个或两者，而没有其他内容，那么您可以使用以下代码：
select *,
result1 = price * amount * max(iif(ProdId not in (1,2), 1.01, 1)) over (partition by invoice)
from YourTable;
如果发票需要确切包含1和2两个ProdId，可以使用SQL Server 2022的方法：
select *,
result2 = price * amount * least(max(iif(ProdId = 1, 1.01, 1)) over W, max(iif(ProdId = 2, 1.01, 1)) over W)
from YourTable
WINDOW W AS ( PARTITION BY invoice)
英文:
If the invoice just needs to have either ProdId of 1 or 2 or both but nothing else then you can use
select *,
result1 = price * amount * max(iif(ProdId not in (1,2), 1.01, 1)) over (partition by invoice)
from YourTable;
If the invoice needs exactly both ProdId of 1 or 2 to be present a SQL Server 2022 method might be
select *,
result2 = price * amount * least(max(iif(ProdId = 1, 1.01, 1)) over W, max(iif(ProdId = 2, 1.01, 1)) over W)
from YourTable
WINDOW W AS ( PARTITION BY invoice)
答案2
得分: 1
你可以使用条件表达式来确定乘数，并使用 exists 来检查条件：
select *,
result = price * amount *
case when exists (
select * from t t2
where t2.invoice = t.invoice
having Min(prodid) = 1 and Max(prodid) = 2
) then 1 else 1.1 end
from t;
英文:
You could use a conditional expression to determine the multiplier and exists to check the criteria:
select *,
result = price * amount *
case when exists (
select * from t t2
where t2.invoice = t.invoice
having Min(prodid) = 1 and Max(prodid) = 2
) then 1 else 1.1 end
from t;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论