我正在尝试计算不同价格范围的广告，但是当计数为零时，我的查询不输出，因此我无法将其与范围数组关联。
:
这是一个带有换行符的数组:
$arr_pri = array(1, 30000, 50000, 75000, 100000, 125000, 150000, 175000, 200000, 300000, 400000);
如果下面是查询:
$ctpri = count ($arr_pri);
$ctmod_pri = array();
$arr_i=$arr_k='';
$sql = "SELECT `range`, COUNT(`ad_id`) as ctads FROM (
SELECT CASE ";
for ($i=0; $i < $ctpri-1; $i++){
$k=$i+1;
$arr_i=$arr_pri[$i]+1;
$arr_k=$arr_pri[$k];
$sql .= "WHEN price BETWEEN {$arr_i} AND {$arr_k} THEN CAST('{$i}' AS UNSIGNED) ";}
$sql .= " END AS `range`, ad_id FROM ads
WHERE published = 'Y'
AND deleted = 'N' ) AS t GROUP BY `range`";
$stmt = $ulink->prepare($sql);
$stmt->execute();
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
ctmod_pri[] = $r['ctads'];
}
$ctmod_pri的典型输出如下:
array(5) { [0]=> string(1) "1" [1]=> string(1) "2" [2]=> string(1) "1" [3]=> string(1) "1" [4]=> string(1) "1" }
我期望它生成一个包含10个元素的数组，每个元素对应一个价格范围(0-30000,30001-50000 ....)，也输出零结果。
我错过了什么?
正如Michael-sqlbot指出的那样，CASE不会做您想做的事情。我不完全确定想要的输出，但是试试这个查询。
SELECT '2-30000' AS `range`, SUM(CASE WHEN price BETWEEN 2 AND 30000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '30001-50000' AS `range`, SUM(CASE WHEN price BETWEEN 30001 AND 50000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '50001-75000' AS `range`, SUM(CASE WHEN price BETWEEN 50001 AND 75000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '75001-100000' AS `range`, SUM(CASE WHEN price BETWEEN 75001 AND 100000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '100001-125000' AS `range`, SUM(CASE WHEN price BETWEEN 100001 AND 125000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '125001-150000' AS `range`, SUM(CASE WHEN price BETWEEN 125001 AND 150000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '150001-175000' AS `range`, SUM(CASE WHEN price BETWEEN 150001 AND 175000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '175001-200000' AS `range`, SUM(CASE WHEN price BETWEEN 175001 AND 200000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '200001-300000' AS `range`, SUM(CASE WHEN price BETWEEN 200001 AND 300000 THEN 1 ELSE 0 END) AS `ctads` FROM ads
union
SELECT '300001-400000' AS `range`, SUM(CASE WHEN price BETWEEN 300001 AND 400000 THEN 1 ELSE 0 END) AS `ctads` FROM ads;
它将计算给定价格范围内的广告数量。如果需要输出，可以将2-30000更改为0等等。
THEN 1 else 0基本上表示如果行满足WHEN部分，则加1，否则加0。