我有两个搜索查询-一个将显示过去7天的内容。另一个将显示2周前的内容。
两者都可以正常工作。然而,我想从第一个查询结果中获取与第二个查询结果的差异。然后以带有差异的方式显示第一个查询结果。
第二个查询将导致:
现在我想要像这样显示它。
$result_account = $db->query("
SELECT nid
, COUNT(cat) AS qty
, dte
, descript
, cat
, name
, user
FROM client_note AS cn
JOIN client_note_tag_items AS cnti
ON cnti.note_id = cn.nid
JOIN client_note_tags AS cnt
ON cnt.tag_id = cnti.tag_id
WHERE dte >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND name NOT LIKE 'Resolution%'
GROUP
BY cat
ORDER
BY qty DESC
LIMIT 5
");
if($count_account = $result_account->num_rows) {
while($row = $result_account->fetch_object()){
echo "<tr>";
echo "<td><h6>".$row->cat."</h6></td><td><h3 class='text-primary'>".$row->qty."</h3></td>";
echo "</tr>";
}
}
$result_previous = $db->query("SELECT nid, COUNT(cat) AS qty, dte, descript, cat, name, user FROM client_note AS cn JOIN client_note_tag_items AS cnti ON cnti.note_id = cn.nid JOIN client_note_tags AS cnt ON cnt.tag_id = cnti.tag_id WHERE (dte BETWEEN DATE_SUB(CURDATE(), INTERVAL 21 DAY) AND DATE_SUB(CURDATE(), INTERVAL 14 DAY)) AND name NOT LIKE 'Resolution%' GROUP BY cat ORDER BY qty DESC LIMIT 5");
if($count_previous = $result_previous->num_rows) {
while($row_p = $result_previous->fetch_object()){
echo "<tr>";
echo "<td><h6>".$row_p->cat."</h6></td><td><h3 class='text-primary'>".$row_p->qty."</h3></td>";
echo "</tr>";
}
}
第一个查询的结果如下:
Category - Qty
Baseball - 45
Football - 33
Soccer - 21
Hockey - 7
Basketball - 3
第二个查询将导致:
Category - Qty
Basketball - 38
Soccer - 28
Hockey - 16
Football - 12
Baseball - 12
现在我想要像这样显示它。
Category - Qty Difference
Baseball - 45 +33
Football - 33 +21
Soccer - 21 -7
Hockey - 7 -9
Basketball - 3 -35