如果你可以在Linq中做某件事，那么它总是可以用SQL来做吗？

Question

我正在尝试编写一条SQL语句，它的功能应该类似于下面的Linq查询。主要，我想根据某个条件(键入== "Check")将一列(Value * Quantity)中的两列(Cash和Check)相加。因此，如果Type是" Check "，则将该值放入Check列，否则将其放入Cash列。

Linq：

from ac in m_DataContext.acquisitions
join ai in m_DataContext.acquireditems on ac.Identifier equals ai.AcquisitionIdentifier
group ai by ac.SessionIdentifier into g
select new
{
    SessionIdentifier = g.Key,
    Cash = g.Where(ai => ai.Type != "Check").Sum(ai => (double?)ai.Value * (int?)ai.Quantity) ?? 0,
    Check = g.Where(ai => ai.Type == "Check").Sum(ai => (double?)ai.Value * (int?)ai.Quantity) ?? 0,
    CheckQuantity = g.Where(ai => ai.Type == "Check").Sum(ai => (int?)ai.Quantity) ?? 0
};

SQL：

SELECT a.SessionIdentifier, a.Checks as CheckQuantity, ???? as Cash, ???? as Check
FROM Acquisitions ac
INNER JOIN AcquiredItems ai ON ai.AcquisitionIdentifier = ac.Identifier
GROUP BY a.SessionIdentifier

这个是可能的吗？

Answer 1

你可以这样做：

select sum(case when type = 'cash' then Value * Quantity else 0 end) as Cash,
    sum(case when type = 'check' then Value * Quantity else 0 end) as Check
from ...