我有一个表,其中有一堆订单行。我需要检查该表,看看是否有1行或多行匹配2个WHERE子句。如果有,我会将一个变量设置为"yes",以便在页面的其他地方使用。
目前,我正在用单独的php-mysql查询来检查这一点,但不知道是否可以将它们组合成一个选择查询,所以我只访问数据库一次,而不是10次或更多次。
以下是当前代码的示例:
$query9 = "SELECT COUNT(*) as num FROM mojave_requests where status = 'Pending' AND local = 'L-BKED'";
$requestEDI = mysql_fetch_array(mysql_query($query9));
$requestEDI = $requestEDI[num];
if ($requestEDI> 0) {
$requestsEDI = 'yes';
}
$query11 = "SELECT COUNT(*) as num FROM mojave_requests where status = 'Pending' AND local = 'LOQ53'";
$requestLOQ53 = mysql_fetch_array(mysql_query($query11));
$requestLOQ53 = $requestLOQ53[num];
if ($requestLOQ53 > 0) {
$requestsLOQ53 = 'yes';
}
$query13 = "SELECT COUNT(*) as num FROM mojave_requests where status = 'Pending' AND local = 'L-YRYR'";
$requestLYRYR = mysql_fetch_array(mysql_query($query13));
$requestLYRYR = $requestLYRYR[num];
if ($requestLYRYR > 0) {
$requestsLYRYR = 'yes';
}
$query15 = "SELECT COUNT(*) as num FROM mojave_requests where status = 'Pending' AND local = 'L-CAL0611'";
$requestLCAL0611 = mysql_fetch_array(mysql_query($query15));
$requestLCAL0611 = $requestLCAL0611[num];
if ($requestLCAL0611 > 0) {
$requestsLCAL0611 = 'yes';
}
每个查询之间只有两个不同之处,一个是我正在检查的"local"列字符串,另一个是如果至少有一个匹配项,我将变量名设置为"Yes"。
谢谢你的光临。
您可以使用条件和作为
SELECT
sum
(
case when status = 'Pending' AND local = 'L-BKED' then 1 else 0 end
) as `L-BKED_COUNT`,
sum
(
case when status = 'Pending' AND local = 'LOQ53' then 1 else 0 end
) as `LOQ53_COUNT`,
sum
(
case when status = 'Pending' AND local = 'L-YRYR' then 1 else 0 end
) as `L-YRYR_COUNT`,
sum
(
case when status = 'Pending' AND local = 'L-CAL0611' then 1 else 0 end
) as `L-CAL0611_COUNT`
FROM mojave_requests
此GROUP BY SELECT
将为local
提供与所需值匹配的count
:
SELECT COUNT(*) as num,
local
FROM mojave_requests
WHERE status = 'Pending'
GROUP BY local
HAVING local IN ('L-BKED','LOQ53','L-YRYR','L-CAL0611')
SQL Fiddle
您还应该更改您的PHP代码,不要使用那些$request___
变量,而只需要一段时间来检查:
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
switch ($row[1]) {
case 'L-BKED':
code to be executed if count for L-BKED > 0
break;
case 'LOQ53':
code to be executed if count for LOQ53 > 0
break;
case 'L-YRYR':
code to be executed if count for L-YRYR> 0
break;
...
}
}
您正在寻找条件聚合。在MySQL中,最简单的方法是利用布尔值在数字上下文中被视为数字的事实:
SELECT SUM(status = 'Pending' AND local = 'L-BKED') as num_bked,
SUM(status = 'Pending' AND local = 'LOQ53') as num_loc53,
SUM(status = 'Pending' AND local = 'L-YRYR') as num_yryr,
SUM(status = 'Pending' AND local = 'L-CAL0611') as num_cal0611
FROM mojave_requests;
这将返回一行中的值。您可以通过引用php中的列来获取值。
您可以通过考虑第一个条件来简化此查询:
SELECT SUM(local = 'L-BKED') as num_bked,
SUM(local = 'LOQ53') as num_loc53,
SUM(local = 'L-YRYR') as num_yryr,
SUM(local = 'L-CAL0611') as num_cal0611
FROM mojave_requests
WHERE status = 'Pending';
如果很少的状态值实际上是PENDING
,那么mojave_requests(status, local)
上的索引应该会显著提高性能。