多维数组查询mysql [英] multidimensional array query mysql
问题描述
我正在使用酒店预订系统，价格为每晚。
我的数据库有两个表，一个表叫stock，另一个表促销。第一行包含一天（365天）的一行，最后一行仅包含折扣百分比的天。有时，有些日子会有两个或更多不同的折扣百分比。在这种情况下，1月30日和31日的0.10和0.15。
I am working in a hotel reservation system, and the rates are per night. My database has two tables, one called stock and the other promotions. The first contain one row for day (365 days), and the last one only the days that have a percent of discount. Sometimes, some days have two or more different percent of discount. In this case 30 and 31 of january have 0.10 and 0.15.
餐桌股票
date | rate
--------------------------
2017-01-29 500
2017-01-30 500
2017-01-31 500
餐桌促销
date | percent
--------------------------
2017-01-30 0.10
2017-01-31 0.10
2017-01-30 0.15
2017-01-31 0.15
而mysql查询（SELECT s.date，s.rate，IFNULL（p.percent，0）
从股票s LEFT JOIN促销中p ON s.date = p.date
WHERE s.date as '2017-01-29'和'2017-01-31'之间的日期）我得到以下结果：
while a mysql query (SELECT s.date, s.rate, IFNULL(p.percent, 0) FROM stock s LEFT JOIN promotions p ON s.date = p.date WHERE s.date as date BETWEEN '2017-01-29' AND '2017-01-31') i obtain this result:
date | rate | percent
-----------------------------------
2017-01-29 500 0
2017-01-30 500 0.10
2017-01-31 500 0.10
2017-01-30 500 0.15
2017-01-31 500 0.15
我需要创建一个多维数组，每个折扣百分比包含一个数组，这些数组包含三天的时间，第29天的百分比为0.00，另两天的百分比为0.10。新的一天29的百分比为0.00，另两天的百分比为0.15。我正在练习，但是我不能在两个数组中分配第29天。
i need to create a multidimensional array, that contain one array for each percent of discount, and these arrays contain the three days, the day 29 with a percent 0.00 and the other two days 0.10. a new one the day 29 with a percent 0.00 and the other two days 0.15. i am tring but i cant assign the day 29 in both arrays.
Array
(
[1] => Array
(
[0] => Array
(
[date] => 2017-01-29
[rate]=>500
[percent] => 0.00
)
[1] => Array
(
[date] => 2017-01-30
[rate]=>500
[percent] => 0.10
)
[2] => Array
(
[date] => 2017-01-31
[rate]=>500
[percent] => 0.10
)
)
[2] => Array
(
[0] => Array
(
[date] => 2017-01-29
[rate]=>500
[percent] => 0.00
)
[1] => Array
(
[date] => 2017-01-30
[rate]=>500
[percent] => 0.15
)
[2] => Array
(
[date] => 2017-01-31
[rate]=>500
[percent] => 0.15
)
)
)
推荐答案
从本质上讲，这就像是数据库问题。
At its core, this feels like a database problem.
将百分比列设置为NOT NULL并将0设置为默认值。
Set the percent column as NOT NULL and set 0 as the DEFAULT.
ALTER TABLE promotions CHANGE COLUMN percent [maintain-its-type] NOT NULL DEFAULT '0';
然后是拔除MAX（）和MIN（ ）每个日期的百分比值。
Then it is a matter of (if I understand the problem) plucking the MAX() and MIN() percent values for each date.
SELECT s.date, s.rate, MAX(p.percent) AS maxperc, MIN(p.percent) AS minperc
FROM stock s
LEFT JOIN promotions p ON s.date = p.date
WHERE s.date BETWEEN '2017-01-29' AND '2017-01-31'
GROUP BY s.date,s.rate;
...我尚未测试，因此可能需要摆弄。
...I haven't test that, so it may need some fiddling with.
然后，当您遍历结果集时，可以声明两个单独的子数组并构建完整的数组。
Then as you loop through your result set, you can declare the two separate subarrays and build your complete array.
$array=[];
$i=0;
while($row=mysqli_fetch_assoc($result)){
$array[0][$i]["date"]=$row["date"];
$array[0][$i]["rate"]=$row["rate"];
$array[0][$i]["perc"]=$row["minperc"];
$array[1][$i]["date"]=$row["date"];
$array[1][$i]["rate"]=$row["rate"];
$array[1][$i]["perc"]=$row["maxperc"];
++$i;
}
至此，我已经对您的用途/用途做出了太多假设。
基本上，将零设置为默认百分比，查询每个日期率对的最高和最低百分比（如果为零，则零将显示为最高和最低值）。对结果集做任何您想做的事情。
By this point, I've made too many assumptions about your purpose/usage. Basically, set zero as the percent default, query for highest and lowest percent for each date-rate pair (if zero, then zero will appear as highest and lowest value). Do whatever you wish with the result set.
这篇关于多维数组查询mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
