MS-Access SQL条件过滤的困难
MS-Access SQL条件过滤的困难
提问于 2012-12-15 00:39:58
我在MS-Access中有一个查询，我试图根据一个条件提取一些值，然后对结果求和。
SELECT SchedulingLog.UserID, SchedulingLog.Category, Sum(SchedulingLog.Value) AS Gain, Sum(SchedulingLog.Value) AS Used, [Gain]+[Used] AS [Left]
FROM SchedulingLog
GROUP BY SchedulingLog.UserID, SchedulingLog.Category, [Gain]+[Used]
HAVING ((
(SchedulingLog.Category) Like "DH*" Or
(SchedulingLog.Category) Like "Com*") AND
("Where[CatDetai]" Like "Gain*") AND
("Where[CatDetai]" Like "Used*")
);有了SchedulingLog中的这些数据...
Userid LogDate EventDate Category CatDetail Value
abc123 1-1-11 7-2-11 DH DH Used -1
abc123 1-1-11 7-4-11 DH DH Gain 1..。我想让我的查询得到这个结果集：
Userid Category Gain Used Left
abc123 DH 1 -1 0回答 1
Stack Overflow用户
回答已采纳
发布于 2012-12-15 01:12:29
下面的SQL根据在Access 2007中使用示例数据进行的测试返回您所要求的内容。
SELECT
Userid,
Category,
Sum(IIf(CatDetail ALike '%Gain', [Value], 0)) AS Gain,
Sum(IIf(CatDetail ALike '%Used', [Value], 0)) AS Used,
[Gain] + [Used] AS [Left]
FROM SchedulingLog
GROUP BY
Userid,
Category但是，如果您实际上不需要查看Gain和Used的不同值，那么这样做会更简单：
SELECT
Userid,
Category,
Sum([Value]) AS [Left]
FROM SchedulingLog
GROUP BY
Userid,
Category页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13882788
复制相关文章
相似问题
腾讯云开发者
