我可以在带有GROUP BY的WHERE子句中使用IF语句编写mysqli查询吗？或我可以在函数中执行多个查询吗？
旅社
目前，我有一个mysql db表，其结构如下所示（$ sql执行的表。）。我正在运行php函数（当前代码：），该函数会生成一个数组，并输出到html中。
当前结果：每个模型代码基于$ location，$ age1和$ age2的输入一行。
+----+-----+-----------+----------+----+-------------+-----------+
| Yr | Mdl | Mdl Code | Mdl Line | Ct | Total Gross | Avg Gross |
+----+-----+-----------+----------+----+-------------+-----------+
|2017| MDX | YD7H7HKNW | AWDADV | 3 | $ 5,324.54 |$ 1,774.85 |
|2017| RDX | ZD7H8HKNW | ADV | 1 | $ 2,324.54 |$ 2,324.54 |
+----+-----+-----------+----------+----+-------------+-----------+
我需要为每个模型代码生成多个行，并使用年龄段为1-30、31-60、61-90和> = 91的输出。
+-------+----+-----+-----------+----------+----+-------------+-----------+
| Age B | Yr | Mdl | Mdl Code | Mdl Line | Ct | Total Gross | Avg Gross |
+-------+----+-----+-----------+----------+----+-------------+-----------+
|1 - 30 |2017| MDX | YD7H7HKNW | AWDADV | 3 | $ 6,324.54 |$ 2,108.18 |
|31 -60 |2017| MDX | YD7H7HKNW | AWDADV | 2 | $ 3,324.54 |$ 1,662.27 |
+-------+----+-----+-----------+----------+----+-------------+-----------+
我不确定如何执行此操作，我们将不胜感激。
当前代码：
function ageBucket($location, $age1, $age2){
global $con;
$ageBucket = array();
foreach (modelCodeList($location) as $mc) {
$sql = "SELECT `Model Code`, `Year`, `Model`, `Model Line`, COUNT(`Model Code`) AS 'Count' , SUM(`GROSS`) AS 'Total Gross', AVG(`GROSS`) AS 'Avg Gross' FROM `inv_current` WHERE `Location` = '$location' AND `Model Code`= '$mc' AND `Age` BETWEEN '$age1' AND '$age2' GROUP BY `Age` BETWEEN '$age1' AND '$age2'";
$result = mysqli_query($con, $sql);
while ($row = mysqli_fetch_assoc($result)) {
$ageBucket[] = $row;
}
}
usort($ageBucket, function ($a, $b) {
if($b['Avg Gross'] == $a['Avg Gross']) return 0;
return $b['Avg Gross'] < $a['Avg Gross'] ? -1 : 1;
});
return $ageBucket;
}
$ sql执行的表。
+----------+------------+-----+------+---------+------------+-------+
| Location | Model Code | Age | Year | Model | Model Line | GROSS |
+----------+------------+-----+------+---------+------------+-------+
| 3 | TB3H3HJNW | 241 | 2017 | RDX | FWD | 328 |
| 3 | KC1F9HKNW | 225 | 2017 | RLX | ADV | -1181 |
| 3 | DE2F7HKNW | 225 | 2017 | ILX | TECH | 896 |
| 3 | NC1B0HEW | 205 | 2017 | NSX | | 734 |
| 8 | YD3H8HKNW | 178 | 2017 | MDX | ADV | 24 |
| 8 | DE2F7HKNW | 178 | 2017 | ILX | TECH | -724 |
| 8 | DE2F8HJW | 171 | 2017 | ILX | PREMAS | -381 |
| 8 | YD3H5HKNW | 171 | 2017 | MDX | TECH | -213 |
| 3 | YD3H5HKNW | 169 | 2017 | MDX | TECH | -776 |
| 3 | YD3H5HKNW | 169 | 2017 | MDX | TECH | 244 |
| 3 | YD4H5HKNW | 163 | 2017 | MDX | 4TECH | -914 |
| 3 | YD3H5HKNW | 163 | 2017 | MDX | TECH | 70 |
| 8 | YD4H5HKNW | 163 | 2017 | MDX | 4TECH | -1165 |
| 8 | DE2F7HKNW | 157 | 2017 | ILX | | -1107 |
| 8 | NC1B0HEW | 156 | 2017 | NSX | | -863 |
| 8 | DE2F8HJW | 156 | 2017 | ILX | PREMAS | -561 |
| 3 | TB3H5HKNW | 156 | 2017 | RDX | TECH | 139 |
| 3 | YD3H5HKNW | 146 | 2017 | MDX | TECH | 8 |
| 3 | YD3H8HKNW | 143 | 2017 | MDX | ADV | 5 |
| 3 | DE2F7HKNW | 140 | 2017 | ILX | TECH | 491 |
| 8 | YD3H5HKNW | 140 | 2017 | MDX | TECH | -230 |
| 8 | YD3H5HKNW | 140 | 2017 | MDX | TECH | -764 |
| 8 | YD4H5HKNW | 134 | 2017 | MDX | 4TECH | 113 |
| 8 | DE2F7HJNW | 134 | 2017 | ILX | PREM | 755 |
| 3 | TB3H7HKNW | 129 | 2017 | RDX | ADV | -21 |
| 3 | TB3H3HJNW | 127 | 2017 | RDX | | 905 |
| 3 | TB3H3HJNW | 127 | 2017 | RDX | | -664 |
| 3 | TB3H3HJNW | 127 | 2017 | RDX | | -112 |
| 8 | DE2F3HJW | 121 | 2017 | ILX | | 170 |
| 8 | DE2F7HKNW | 120 | 2017 | ILX | TECH | -412 |
| 8 | YD3H5HKNW | 120 | 2017 | MDX | TECH | 341 |
| 8 | YD3H5HKNW | 120 | 2017 | MDX | TECH | -1026 |
| 3 | YD3H5HKNW | 120 | 2017 | MDX | TECH | 653 |
| 3 | YD3H5HKNW | 119 | 2017 | MDX | TECH | 279 |
| 3 | DE2F8HJW | 119 | 2017 | ILX | PREMAS | -207 |
| 3 | YD3H3HJNW | 118 | 2017 | MDX | | -662 |
| 8 | YD3H3HJNW | 113 | 2017 | MDX | | 178 |
| 8 | DE2F7HJNW | 111 | 2017 | ILX | PREM | 701 |
| 8 | YD3H5HKNW | 111 | 2017 | MDX | TECH | -1034 |
| 8 | YD4H5HKNW | 111 | 2017 | MDX | 4TECH | -502 |
| 3 | YD3H3HJNW | 111 | 2017 | MDX | | 16 |
| 3 | YD3H5HKNW | 111 | 2017 | MDX | TECH | 853 |
| 3 | YD3H8HKNW | 108 | 2017 | MDX | | 362 |
| 3 | YD4H8HKNW | 105 | 2017 | MDX | 4ADV | 525 |
| 8 | DE2F7HKNW | 100 | 2017 | ILX | TECH | 149 |
| 8 | DE2F7HKNW | 100 | 2017 | ILX | TECH | 167 |
| 8 | YD3H5HKNW | 97 | 2017 | MDX | TECH | 326 |
| 8 | YD3H5HKNW | 97 | 2017 | MDX | TECH | 586 |
| 3 | YD3H5HKNW | 97 | 2017 | MDX | TECH | -118 |
| 3 | DE2F7HJNW | 94 | 2017 | ILX | PREM | -305 |
| 3 | DE2F7HJNW | 93 | 2017 | ILX | PREM | -609 |
| 3 | YD3H8HKNW | 87 | 2017 | MDX | | -263 |
| 8 | TB3H3HJNW | 86 | 2017 | RDX | | 20 |
| 8 | YD4H8HKNW | 79 | 2017 | MDX | 4ADV | 4 |
| 8 | DE2F3HJW | 79 | 2017 | ILX | | 265 |
| 8 | TB3H5HKNW | 79 | 2017 | RDX | TECH | -470 |
| 3 | UB1F3JJW | 78 | 2018 | TLX 2.4 | TLX2.4 | -1161 |
| 3 | UB1F5JKNW | 78 | 2018 | TLX | TECH 2.4 | -1073 |
| 3 | UB2F5JKNW | 78 | 2018 | TLX | TECH | -141 |
| 3 | UB1F5JKNW | 78 | 2018 | TLX | TECH 2.4 | 789 |
| 8 | UB2F5JKNW | 78 | 2018 | TLX | TECH | 296 |
| 8 | UB1F3JJW | 78 | 2018 | TLX 2.4 | TLX 2.4 | -10 |
| 8 | UB2F5JKNW | 78 | 2018 | TLX | TECH | 871 |
| 8 | YD3H5HKNW | 78 | 2017 | MDX | | -1170 |
| 3 | YD3H5HKNW | 76 | 2017 | MDX | TECH | -627 |
| 3 | UB1F5JKNW | 76 | 2018 | TLX | TECH 2.4 | -711 |
| 3 | UB2F6JKNW | 76 | 2018 | TLX | A-SPEC | -1100 |
| 3 | YD3H5HKNW | 72 | 2017 | MDX | TECH | -529 |
| 8 | YD4H5HKNW | 72 | 2017 | MDX | 4TECH | -254 |
| 8 | UB2F3JJW | 72 | 2018 | TLX | | 632 |
| 8 | UB2F6JKNW | 72 | 2018 | TLX | A-SPEC | -801 |
| 8 | UB1F3JJW | 72 | 2018 | TLX 2.4 | TLX 2.4 | -1165 |
| 3 | UB2F5JKNW | 72 | 2018 | TLX | TECH | -482 |
| 3 | DE2F7HKNW | 70 | 2017 | ILX | TECH | 421 |
| 3 | UB2F5JKNW | 70 | 2018 | TLX | TECH | 288 |
| 3 | UB1F5JKNW | 70 | 2018 | TLX | TECH 2.4 | -144 |
| 8 | YD7H7HKNW | 68 | 2017 | MDX | | -678 |
| 8 | DE2F7HKNW | 66 | 2017 | ILX | | -993 |
| 8 | YD4H9HKNW | 64 | 2017 | MDX | 4ADVENT | -934 |
| 8 | UB2F5JKNW | 64 | 2018 | TLX | TECH V6 | 298 |
| 3 | UB1F3JJW | 64 | 2018 | TLX 2.4 | TLX 2.4 | -609 |
| 3 | UB1F5JKNW | 64 | 2018 | TLX | TECH 2.4 | 484 |
| 3 | UB1F5JKNW | 64 | 2018 | TLX | TECH 2.4 | 570 |
| 3 | TB3H5JKNW | 64 | 2018 | RDX | TECH | 675 |
| 8 | TB3H7JKNW | 64 | 2018 | RDX | ADV | 25 |
| 8 | UB2F5JKNW | 64 | 2018 | TLX | TECH | -853 |
| 8 | YD3H8HKNW | 64 | 2017 | MDX | | -593 |
| 8 | DE2F3HJW | 62 | 2017 | ILX | | -16 |
| 3 | YD3H5HKNW | 58 | 2017 | MDX | TECH | 252 |
| 3 | YD4H8HKNW | 58 | 2017 | MDX | 4ADV | 977 |
| 3 | YD3H3HJNW | 58 | 2017 | MDX | | -50 |
| 3 | UB1F3JJW | 58 | 2018 | TLX 2.4 | TLX 2.4 | 848 |
| 8 | UB1F5JKNW | 58 | 2018 | TLX | TECH 2.4 | 79 |
| 8 | TB3H7JKNW | 58 | 2018 | RDX | ADV | -299 |
| 8 | UB2F5JKNW | 58 | 2018 | TLX | TECH | 708 |
| 8 | UB2F5JKNW | 58 | 2018 | TLX | TECH | -813 |
| 3 | TB4H3JJNW | 58 | 2018 | RDX | 4RDX | -370 |
| 3 | YD3H5HKNW | 56 | 2017 | MDX | TECH | -824 |
| 3 | YD3H3HJNW | 56 | 2017 | MDX | | 462 |
| 3 | UB2F5JKNW | 56 | 2018 | TLX | TECH | -676 |
| 8 | TB3H7JKNW | 56 | 2018 | RDX | ADV | 610 |
| 8 | TB3H7JKNW | 56 | 2018 | RDX | ADV | -556 |
| 8 | TB3H5JKNW | 56 | 2018 | RDX | TECH | -1058 |
| 8 | TB3H3JJNW | 56 | 2018 | RDX | | -192 |
| 3 | TB3H7JKNW | 56 | 2018 | RDX | ADV | -96 |
| 3 | YD4H5HKNW | 55 | 2017 | MDX | 4TECH | -842 |
| 3 | UB1F3JJW | 55 | 2018 | TLX 2.4 | TLX 2.4 | 948 |
| 3 | TB3H7JKNW | 55 | 2018 | RDX | ADV | 484 |
| 8 | YD3H5HKNW | 51 | 2017 | MDX | TECH | 31 |
| 8 | YD3H5HKNW | 51 | 2017 | MDX | TECH | 694 |
| 8 | UB2F5JKNW | 51 | 2018 | TLX | TECH | 352 |
| 8 | UB2F5JKNW | 51 | 2018 | TLX | TECH | -260 |
| 3 | UB1F3JJW | 51 | 2018 | TLX 2.4 | TLX 2.4 | 454 |
| 3 | UB1F5JKNW | 51 | 2018 | TLX | TECH 2.4 | -245 |
| 8 | UB2F5JKNW | 1 | 2018 | TLX | TECH | 722 |
+----------+------------+-----+------+---------+------------+-------+
旅社
Jamaima姨妈感谢您上面的sql语句。当我对我的数据库运行它时，group返回的null的用户定义变量如此处所示。
因此，Age在SELECT中，我将上限（/ 30）从GROUP BY移到了UDV。
SELECT (@c:=ceiling(`Age`/30)) as `group`, `Model Code`, `Year`, `Model`, `Model Line`, COUNT(`Model Code`) AS 'Count' , SUM(`GROSS`) AS 'Total Gross', AVG(`GROSS`) AS 'Avg Gross' FROM `inv_current` WHERE `Location` = 30 AND `Model Code`= 'CC15706' group by `group`
这是此sql语句的结果。
有了这个成功的sql结果，我发现我仍然需要通过将第一次尝试中使用的mysqli_fetch_assoc（）更改为mysqli_multi_query（）来修改PHP函数，如下所示。
关于multi_query的重要说明，我发现的许多在线文档都错误地使mysqli_free_result（）在mysqli_free_result（$ connection）而不是mysqli_free_result（$ result）上工作。此错误产生以下参数错误。
Warning: mysqli_free_result() expects parameter 1 to be mysqli_result
直到我回到PHP手册，我才意识到该错误。该手册指出：“ mysqli_free_result-释放与结果关联的内存”。
无论如何，这是几个小时反复试验的结果。大部分是由于我需要学习更多有关编程的知识。没有Jamamia姨妈和其他花时间在这里和其他地方发布的人的帮助，我无法获得想要的输出。我希望其他人会发现此片段有用。
function ageBuckets($location, $model_code){
global $con;
$ageBuckets = array();
// foreach (modelCodeList($location) as $mc) {
$sql = "SELECT (@c:=ceiling(`Age`/30)) as `Age Bucket`, `Model Code`, `Year`, `Model`, `Model Line`, COUNT(`Model Code`) AS 'Count' , SUM(`GROSS`) AS 'Total Gross', AVG(`GROSS`) AS 'Avg Gross' FROM `inv_current` WHERE `Location` = '$location' AND `Model Code`= '$model_code' group by `Year`, `Age Bucket` ";
if (mysqli_multi_query($con, $sql)) {
do {
if($result = mysqli_store_result($con)) {
while ($row = mysqli_fetch_assoc($result)) {
$ageBuckets[] = $row;
}
mysqli_free_result($result);
}
} while(mysqli_more_results($con) && mysqli_next_result($con));
}
// }
return $ageBuckets;
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
编辑于
相关文章
TOP 榜单
- 1
材质UI垂直滑块。如何改变在垂直材料UI滑块导轨的厚度（反应）
- 2
隐藏发件人没有短信PHP
- 3
在Windows 7中无法删除文件（2）
- 4
HttpClient中的角度变化检测
- 5
Java Eclipse中的错误13，如何解决？
- 6
Hashchange事件侦听器在将事件处理程序附加到事件之前进行侦听
- 7
在浏览器中请求URL时会发生什么？
- 8
flask-admin 如何自定义删除按钮
- 9
java io ioexception无法解析服务器地址解析器的响应
- 10
jOOQ：在特定表中查找约束
- 11
Flexbox CSS 对齐属性环境惰性？
- 12
共享图像将路径放入地址
- 13
加载Microsoft Visual菜单时出现问题
- 14
Powerpoint-条形长度错误的堆积条形图
- 15
应用发明者仅从列表中选择一个随机项一次
- 16
在Angular2中的输入值之前添加加号“ +”
- 17
检查errno！= EINTR：这是什么意思？
- 18
ClickHouse 创建临时表
- 19
ggplot：对齐多个分面图-所有大小不同的分面
- 20
Azure VM启动/停止日志
- 21
是否可以通过编程方式对很多动画进行重新着色？
我来说两句