英文:
SQL Row Select Based on Condition
问题
在MSSQL中，你可以使用以下SQL语句来实现你的需求：
SELECT TOP 1
LineStatus,
SUM(OrderQty) AS TotalOrderQty
FROM
YourTableName
WHERE
LineStatus IN (80, 10)
GROUP BY
LineStatus
ORDER BY
CASE
WHEN LineStatus = 80 THEN 1
ELSE 2
END;
这将会首先选择具有LineStatus为80的行并计算OrderQty的总和。如果没有符合条件的行，它会选择LineStatus为10的行并计算OrderQty的总和。
请将上述YourTableName替换为你实际使用的表格名。
希望对你有所帮助！
英文:
I am trying to find a way to select a row based on condition in MSSQL, I have two rows
| SO | LineNumber | LineStatus | OrderQty |
|---|---|---|---|
| A | 1 | 10 | 4 |
| A | 1 | 80 | 4 |
How should I write in SQL -- First Select the row with line status 80 and SUM(OrderQty), if there is no such row with Line Status = 80 then select Line Status= 10 and SUM(OrderQty).
Can you please suggest something, Thanks in advance
I have tried AND OR condition, not working as expected. Can you kindly suggest something to help me.
答案1
得分: 0
我喜欢在这种情况下使用连接：
SELECT *
FROM table1 t1
JOIN table2 t2
ON t2.id = (
SELECT TOP 1 id
FROM table2 innerT2
WHERE innerT2.condition = condition
)
英文:
I like to use a join for such a thing:
SELECT *
FROM table1 t1
JOIN table2 t2
ON t2.id = (
SELECT TOP 1 id
FROM table2 innerT2
WHERE innerT2.condition = condition
)
答案2
得分: 0
这个对你是否有效？
选择 LineStatus, SUM(OrderQty) 作为 SumOrder
从 表_1
分组按 LineStatus
按 LineStatus 降序排序
英文:
Would this work for you?
select LineStatus, SUM(OrderQty) as SumOrder
from Table_1
group by LineStatus
order by LineStatus desc
答案3
得分: 0
选择
SO
, 使用 coalesce(SUM(case when LineStatus=80 then OrderQty end)
,SUM(case when LineStatus=10 then OrderQty end)) as SumOrder
从 Table_1
按 SO 分组
英文:
Can we assume that you are grouping by SO? If so, something like this should work:
select
SO
, coalesce(SUM(case when LineStatus=80 then OrderQty end)
,SUM(case when LineStatus=10 then OrderQty end)) as SumOrder
from Table_1
group by SO
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论