php - 多个 SQL 选择和同一个表上的位置

Question

我需要对 MYSQL 数据库中的一张表进行查询。在此查询中，我需要使用不同的位置进行多个选择。

我当前的查询是:

$result = mysql_query("
    SELECT
        last_name, first_name, user_name,
        SUM(rthours) as rthours, SUM(othours) as others,
        SUM(trthours) as trthours, SUM(tothours) as tot hours
    FROM data
    WHERE labor_date BETWEEN '$start' AND '$end'
    ORDER BY last_name ASC
");

我还需要选择以下两项:

SUM(rthours) as vhours FROM data WHERE decription = 'vacation' AND labor_date BETWEEN '$start' AND '$end'

和

SUM(rthours) as shours FROM data WHERE decription = 'sick' AND labor_date BETWEEN '$start' AND '$end'

如何将所有三个选择合并到一个查询中，以便我可以在随后输出的表中使用它们。

感谢您的帮助!

Answer 1

您可以在SUM()中使用CASE

SELECT last_name, first_name, user_name,
SUM( CASE WHEN decription = 'vacation' AND labor_date BETWEEN '$start' AND '$end' THEN rthours ELSE 0 END) as rthours,
SUM(CASE WHEN decription = 'sick' AND labor_date BETWEEN '$start' AND '$end' THEN rthours  ELSE 0 END) as shours,    
SUM(othours) as othours, 
SUM(trthours) as trthours, 
SUM(tothours) as tothours 
FROM data 
WHERE labor_date BETWEEN '$start' AND '$end' 
/*missing group by*/
Order by last_name ASC

对于聚合函数，您应该使用组函数