使用不同的条件,在同一个表的一个SQL查询中使用多个聚合函数
I'm working on creating a SQL query that will pull records from a table based on the value of two aggregate functions. These aggregate functions are pulling data from the same table, but with different filter conditions. The problem that I run into is that the results of the SUMs are much larger than if I only include one SUM function. I know that I can create this query using temp tables, but I'm just wondering if there is an elegant solution that requires only a single query.
我正在创建一个SQL查询,该查询将根据两个聚合函数的值从表中提取记录。这些聚合函数从同一个表中提取数据,但是使用不同的过滤条件。我遇到的问题是,这些求和的结果比只包含一个和函数的结果要大得多。我知道我可以使用临时表创建这个查询,但我想知道是否有一种优雅的解决方案只需要一个查询。
I've created a simplified version to demonstrate the issue. Here are the table structures:
我创建了一个简化版本来演示这个问题。下面是表格结构:
EMPLOYEE TABLE
EMPID
1
2
3
ABSENCE TABLE
EMPID DATE HOURS_ABSENT
1 6/1/2009 3
1 9/1/2009 1
2 3/1/2010 2
And here is the query:
这是查询:
SELECT
E.EMPID
,SUM(ATOTAL.HOURS_ABSENT) AS ABSENT_TOTAL
,SUM(AYEAR.HOURS_ABSENT) AS ABSENT_YEAR
FROM
EMPLOYEE E
INNER JOIN ABSENCE ATOTAL ON
ATOTAL.EMPID = E.EMPID
INNER JOIN ABSENCE AYEAR ON
AYEAR.EMPID = E.EMPID
WHERE
AYEAR.DATE > '1/1/2010'
GROUP BY
E.EMPID
HAVING
SUM(ATOTAL.HOURS_ABSENT) > 10
OR SUM(AYEAR.HOURS_ABSENT) > 3
Any insight would be greatly appreciated.
如有任何见解,我们将不胜感激。
3 个解决方案
#1
21
SELECT
E.EMPID
,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR
FROM
EMPLOYEE E
INNER JOIN ABSENCE ON
ABSENCE.EMPID = E.EMPID
GROUP BY
E.EMPID
HAVING
SUM(ATOTAL.HOURS_ABSENT) > 10
OR SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) > 3
edit:
编辑:
It's not a big deal, but I hate repeating conditions so we could refactor like:
这没什么大不了的,但我讨厌重复的条件,所以我们可以重构:
Select * From
(
SELECT
E.EMPID
,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR
FROM
EMPLOYEE E
INNER JOIN ABSENCE ON
ABSENCE.EMPID = E.EMPID
GROUP BY
E.EMPID
) EmployeeAbsences
Where ABSENT_TOTAL > 10 or ABSENT_YEAR > 3
This way, if you change your case condition, it's in one spot only.
这样,如果你改变你的情况,它只是在一个地方。
更多相关文章
- PHP OOP - 调用非obj上的成员函数[重复]
- sqlserver的常用函数
- SQLSERVER2005调试存储过程跟函数
- SQL Server中的TextPtr函数
- PLSQL乱码解决方案
- MySQL字符串相加函数如何运行?似曾相识还是记一笔吧
- mysql自定义排序规则函数——field()
- 数据库不支持中文解决方案(mysql)
- oracle基础知识总结 part 3 : 三范式,PLSQL,存储过程,函数,触发器