我有一个包含多列的表。我需要创建一个SQL查询，根据日期选择器和选择框中的用户输入来显示某些列。我做得很好。但是，我需要在显示表的底部合计其中一个结果列。我不知道如何在当前查询中创建一个结果来显示合计列。从本质上讲，我希望将WTTotal列合计到一个单独的单元格中。我当前的查询如下。我真的认为这是一件我似乎看不到的简单的事情。
SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal
FROM WorkTicket
WHERE Date BETWEEN '2014-10-01' AND '2014-10-31' AND Invoiced IS NULL
ORDER BY Client
SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal
FROM WorkTicket
WHERE Date BETWEEN '2014-10-01' AND '2014-10-31' AND Invoiced IS NULL
ORDER BY Client
UNION ALL
SELECT 'Total', NULL, NULL, NULL, NULL, SUM(WTTotal)
FROM WorkTicket
WHERE Date BETWEEN '2014-10-01' AND '2014-10-31' AND Invoiced IS NULL;
您不能用一个查询就拥有它。将SUM()放入查询中需要GROUP BY子句，并将所有原始结果行折叠为一个结果行，这意味着表将消失。您想要的是客户端解决方案。例如，在客户端代码中启动计数器，然后手动将结果相加。伪代码：
$sum = 0;
while( $row = fetch_from_db() ) {
$sum += $row['field_to_sum'];
display_row();
}
echo "Total: $sum";
如果你想把它放在一个单独的列中，而不是放在一行中，你可以在一个子查询中计算它，并把它加入到你的结果中：
SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy, my_calculated_sum AS WTTotal
FROM WorkTicket
JOIN (SELECT SUM(whatever_you_want_to_sum) as my_calculated_sum FROM WorkTicket) subquery_alias
WHERE Date BETWEEN '2014-10-01' AND '2014-10-31' AND Invoiced IS NULL
ORDER BY Client
或者你只是用变量来计算它，就像一个连续的总数。结果在列的最后一行。
SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy, @sum_variable := @sum_variable + whatever_you_want_to_sum AS WTTotal
FROM WorkTicket
, (SELECT @sum_variable := 0) var_init_subquery
WHERE Date BETWEEN '2014-10-01' AND '2014-10-31' AND Invoiced IS NULL
ORDER BY Client
但实际上Kevin的解决方案非常好。
我也有类似于Kevin的解决方案：
;with cte_result
as
(
SELECT REPLACE(Client,',',' ') AS Client,WorkTicketNum,Lease,Date,OrderedBy,WTTotal
FROM WorkTicket
WHERE Date BETWEEN '2014-10-01' AND '2014-10-31' AND Invoiced IS NULL
)
select *
from cte_result
union
select 'TotalAmount',NULL,NULL,NULL,NULL,sum(WTTotal)
from cte_result
order by client
但只有当您的DB引擎支持CTE时，此解决方案才能工作。使用CTE，我们可以使用相同的结果集来获得total的行。