我有两个包含类似数据的数据库表 - 一个是进程完成的工作量,另一个是该进程所犯的错误量。
我想显示第一次工作的百分比。
我按进程计算项目数的查询是:
SELECT Counter.Process, count( Counter.Process ) AS count
FROM Counter WHERE (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')
GROUP BY Counter.Process
ORDER BY count DESC
上面给了我一个类似于以下内容的列表:
Process | count
-------------------
Process A | 40
Process B | 32
Process C | 102
Process D | 23
我有第二个表,其中包含用于记录错误的相同字段,称为"错误"。
通过使用相同的查询(但明显更改表名称),我可以获得进程和错误的列表。
我想做的是创建一个PHP网页,显示每个进程的错误百分比。
我的问题是,如果我分别创建两个查询,当我使用 PHP Where 循环遍历它们时,我不知道如何进行计算以显示百分比。
我尝试在两个SQL查询之间使用UNION,但我无法让它显示针对进程的两个不同的计数读数。
理想情况下,我想要一种显示数据的方法,如下所示:
Process | Counter.count | Errors.count
Process A 40 2
Process B 32 0
Process C 102 18
Process D 23 8
关于如何完成此操作的任何建议/建议,我将不胜感激 - 如果您需要有关我的数据库表的更多信息,请告诉我
感谢您的阅读
S
我没有
运行它,所以可能会有错误。 您可以进行两个选择,然后左边连接结果:
select s.process, s.success_count, e.error_count
from
(SELECT Counter.Process as process, count( Counter.Process ) AS success_count
FROM Counter
WHERE (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')
GROUP BY Counter.Process
) s left join
(SELECT Errors.Process, count( Errors.Process ) AS error_count
FROM Errors
WHERE (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')
GROUP BY Errors.Process
) e
on (s.Process = e.process)
我个人更喜欢通过 PHP 运行这样的情况,因为它让我不那么困惑,所以我将解释我认为如何在 PHP 中做到这一点(我说"思考"是因为我可能不知道你的数据库结构或系统规范会让我变得不舒服):
$rows = array();
$res = mysql_query('SELECT Counter.Process, count( Counter.Process ) AS count
FROM Counter WHERE (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')
GROUP BY Counter.Process
ORDER BY count DESC');
while ($arr = mysql_fetch_assoc($res))
{
$rows[] = $arr;
}
$res2 = mysql_query('SELECT Errors.Process, count( Errors.Process ) AS count
FROM Counter WHERE (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')
GROUP BY Errors.Process
ORDER BY count DESC');
while ($arr2 = mysql_fetch_assoc($res2)
{
for ($i=0; $i<count($rows); $i++)
{
if ($arr2["Process"] == $data[$i]["process"])
{
break;
}
//Do your functions with $arr and $data[$i] at this point...
}
}
在没有子查询的情况下执行此操作应该很简单:
SELECT
Counter.Process
, COUNT(Counter.Process) AS ProcessCount
, COUNT(Errors.Process) AS ProcessErrors
FROM
Counter
LEFT JOIN
Errors
ON Counter.Process = Errors.Process
WHERE
Counter.TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59'
GROUP BY
Counter.Process
您可以对这两个查询进行说明,以查看哪个查询最适合您的数据。
-编辑:我已将其更改为左联接,因此如果进程没有错误,您就不会错过行。
您可以使用子查询:
SELECT Counter.Process, count( Counter.Process ) AS Count,
(SELECT count(Errors.Process)
FROM Errors
WHERE Process=Counter.Process
AND (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')) AS Errors
FROM Counter WHERE (TIME BETWEEN '2012-07-01 00:00:00' AND '2012-07-06 23:59:59')
GROUP BY Counter.Process
ORDER BY count DESC
问候尼尔。