Question

我需要查询这个数据库以获取每行数据，但也需要结果中一列数值的总和。我可以使用PHP获取总值，但然后我需要运行两个循环，一个用于获取总值（放置在结果上方的顶部）。所以我更喜欢查询来捕捉它并只创建一个“总计”行，但我使用的唯一方法是具有原始查询的重复子查询。是否有更好的方法？

SELECT 
CONCAT(u.firstname,    , u.lastname ) name, u.id, s.description, s.shiftstart, s.shiftend, 
    (SELECT 
    SUM( TIME_TO_SEC( TIMEDIFF( shiftend, shiftstart ) ) ) /3600
    FROM shifts
    WHERE id =   $user 
    AND DATE( shiftstart )
    BETWEEN  $start 
    AND  $end ) total
FROM shifts s
INNER JOIN users u ON ( s.id = u.id )
WHERE s.id =  $user 
AND DATE( shiftstart )
BETWEEN  $start 
AND  $end 
ORDER BY shiftstart

以上工作并输出：

name        id     description  shiftstart             shiftend               total
Joe User    joeuser    Stuff    2009-01-05 07:45:00    2009-01-05 12:15:00    39.5000
Joe User    joeuser    Stuff    2009-01-05 13:00:00    2009-01-05 17:00:00    39.5000
Joe User    joeuser    Stuff    2009-01-06 07:45:00    2009-01-06 10:45:00    39.5000
Joe User    joeuser    Stuff    2009-01-06 10:45:00    2009-01-06 12:45:00    39.5000
Joe User    joeuser    Stuff    2009-01-06 13:30:00    2009-01-06 14:30:00    39.5000
Joe User    joeuser    Stuff    2009-01-06 14:30:00    2009-01-06 17:00:00    39.5000
Joe User    joeuser    Stuff    2009-01-07 09:45:00    2009-01-07 14:00:00    39.5000
Joe User    joeuser    Stuff    2009-01-07 15:00:00    2009-01-07 17:00:00    39.5000
Joe User    joeuser    Stuff    2009-01-08 08:00:00    2009-01-08 12:15:00    39.5000
Joe User    joeuser    Stuff    2009-01-08 13:15:00    2009-01-08 17:00:00    39.5000
Joe User    joeuser    Stuff    2009-01-09 07:45:00    2009-01-09 10:45:00    39.5000
Joe User    joeuser    Stuff    2009-01-09 11:45:00    2009-01-09 15:15:00    39.5000
Joe User    joeuser    Stuff    2009-01-09 15:15:00    2009-01-09 17:00:00    39.5000

这就是我需要的，但可能不是最佳的获取方式。

Answer 1

MySQL支持一个特殊的分组聚合修饰符，称为ROLLUP。

SELECT CONCAT(u.firstname,    , u.lastname ) name, u.id, 
  s.description, s.shiftstart, s.shiftend, 
  SUM( TIME_TO_SEC( TIMEDIFF( shiftend, shiftstart ) ) ) /3600 total
FROM shifts s INNER JOIN users u ON ( s.id = u.id )
WHERE s.id = ? AND DATE( shiftstart ) BETWEEN ? AND ?
GROUP BY u.id, s.shiftstart WITH ROLLUP
ORDER BY shiftstart;

Answer 2

更好的方法是用代码实现。人们一直坚持使用关系代数的SQL来执行过程性任务。试图将过程性任务强行塞入SQL中，无论是从复杂性还是性能的角度来看，都是一个不好的想法。请听取专业DBA的建议。

从您的代码中运行两个查询。首先输出较大的集合，然后以您想要的任何格式输出总行。您的查询将更小和更简单，性能将得到改善，您将获得所需的输出。

一些其他的建议 - 硬盘空间很便宜，而大多数数据库表格的读取要比写入频繁得多。如果可能的话，在 MySQL 中设置一个插入/更新触发器来填充一个独立的列，计算出像“ CONCAT(u.firstname, ,u.lastname)”这样的字段，并在查询中使用它。逐行函数不可扩展，随着它变得越来越大，会破坏您的 DBMS 性能。

Answer 3

任何事物都可以通过比较变得更好；问题是与什么进行比较，对吧？

你指出的关键问题是你希望在结果的顶部获得总数；Crystal Reports和其他应用程序通过成为双通引擎来管理这种魔术。第一步获取总数。

任何解决方案都有权衡取舍。如果您获得单独的“总”行，则接收的应用程序将需要将其从结果中剥离出来或使用其他技巧隐藏它。

一个可能性是，如果您不是为一个每小时100万点击次数的网站编写文章，那么可以选择简单地进行两个调用——一个用于总体信息，如名称、总时间等，一个用于详细信息…从查询中可以看出您正在选择单个人的结果。

我们都支持节省开销和带宽，但有时候，简单就是更好的选择...

编辑：Pax在我之前按下了“保存”按钮...哈哈...

Answer 4

请使用MySQL扩展，正如Bill Karwin所描述的，这正是它的用途（我已经为此点赞了）。

如果这不可用，选项2将是另一个SQL语句：SELECT SUM（...）SQL真的是针对这种事情进行了极其高效的优化，与您可能编写的任何过程代码循环相比。

友情链接