PHP MySQL两个cols之间的和不工作

Question

我在用PHP制作动态图形时有点卡住了。下面是代码:

$arrlength = count($ages);for($x = 0; $x < $arrlength; $x++) {

$query = $con->query("SELECT SUM(Amount) AS AmountTotal FROM Col WHERE Type!=1 AND client_id = $client_id AND (StartAge<$ages[$x] OR Start=$ages[$x]) AND (EndAge>$ages[$x] OR EndAge=$ages[$x])");
if(mysqli_num_rows($query)>0) {
while($row = $query->fetch_assoc()){ 

$Total = $row['AmountTotal'];

}     
}
if(!empty($Total)){echo" $Total,";}else { echo" 0,";}

}

图形x轴为Age, Y为Sum of Amount。它们在数据中的多个条目。

数据为:

你会运行更少的查询，并有一个更有效的过程，如果你考虑总结你的查询结合你的逻辑表达式(即使用<=代替

1. 步骤1 -使用php生成所有年龄的子查询

$agesTable="( ";
foreach($ages as $age){
$agesTable.=" SELECT $age as age UNION ALL ";
}
$agesTable.=" SELECT NULL as age) ages";

2. 创建完整的SQL查询以运行

我假设$client_id不是从面向客户端的应用程序(例如web/移动应用程序)收到的值，在这种情况下，您应该考虑SQL注入并在运行查询时使用准备好的语句。有很多文章和S/O问题都在讨论这个问题。

$combinedQuery = "SELECT ages.age as Age, SUM(COALESCE(Col.Amount,0)) as AmountTotal FROM $agesTable LEFT JOIN  Col ON ages.age BETWEEN Col.StartAge AND Col.EndAge AND Type!=1 AND client_id = $client_id WHERE ages.age IS NOT NULL GROUP BY 1";

3. 步骤3 -运行查询

$results = []; //store all your graph data as
if(mysqli_num_rows($query)>0) {
while($row = $query->fetch_assoc()){ 
if(empty($row['Age']))continue; //optional - to ignore null groups
//append data to your array of results
array_push($results,[
//Below I use 'Age' and 'Total' as my array keys but you may also use X and Y instead
'Age'=>$row['Age'], //extract the age or your 'X' value
'Total'=>$row['AmountTotal'] //extract the total or your 'Y' value
]);   
//instead of appending the data for your chart to an array to be used later, you could continue with your current approach and output it here.
}     
}

基于年龄的值，您的结果数组看起来像这样。

[
[
'Age'=>1,
'Total'=>10
],
[
'Age'=>2,
'Total'=>10
],
...etc
]

我已经复制了这个解决方案，并使用php和db-fiddle生成查询。您将在下面看到完整的结果

测试PHP代码

<?php
$ages = [];
for($i=1;$i<=10;$i++)$ages[]=$i;
$agesTable="( ";
foreach($ages as $age){
$agesTable.=" SELECT $age as age UNION ALL ";
}
$agesTable.=" SELECT NULL as age) ages";
$client_id=1;
$combinedQuery = "SELECT ages.age as Age, SUM(COALESCE(Col.Amount,0)) as AmountTotal FROM $agesTable LEFT JOIN  Col ON ages.age BETWEEN Col.StartAge AND Col.EndAge AND Type!=1 AND client_id = $client_id WHERE ages.age IS NOT NULL GROUP BY 1";
echo $combinedQuery;

使用$combinedQuery输出的DB Fiddle输出

Schema (MySQL v8.0)

CREATE TABLE Col (
`StartAge` INTEGER,
`EndAge` INTEGER,
`Amount` INTEGER,
`client_id` INTEGER DEFAULT 1,
`Type` INTEGER DEFAULT 2
);
INSERT INTO Col
(`StartAge`, `EndAge`, `Amount`)
VALUES
('1', '9', '10'),
('5', '7', '10'),
('7', '9', '10');

---

查询# 1

SELECT 
ages.age as Age, 
SUM(COALESCE(Col.Amount,0)) as AmountTotal 
FROM ( 
SELECT 1 as age UNION ALL 
SELECT 2 as age UNION ALL 
SELECT 3 as age UNION ALL 
SELECT 4 as age UNION ALL 
SELECT 5 as age UNION ALL 
SELECT 6 as age UNION ALL 
SELECT 7 as age UNION ALL 
SELECT 8 as age UNION ALL 
SELECT 9 as age UNION ALL 
SELECT 10 as age UNION ALL 
SELECT NULL as age) ages 
LEFT JOIN 
Col ON ages.age BETWEEN Col.StartAge AND Col.EndAge AND 
Type!=1 AND client_id = 1 
WHERE ages.age IS NOT NULL 
GROUP BY 1;

tbody> <<tr>24567810

Age	AmountTotal
1	10
	10
3	10
	10
	20
	20
	30
	20
9	20
	0