mysql - 在条件动态过滤的选择位置中嵌套“ifs”
问题描述
最近,我创建了一个基于 codeigniter 3-php 和 PHPMyADMIN-mysql 的新网站,并且我整合了一个分页,而不是使用 ajax 进行动态搜索。我的问题是,当我有超过 100 万、500000 个结果时,查询开始花费大量时间。
对于这个解决方案,我考虑使用触发器。所以我在使用触发器时有个小麻烦,就是当我尝试做一个动态过滤器时,我举个例子来更好地理解,我想知道一个过滤器对应存储在另一个动态表中的行数。
BEGIN
UPDATE max_tare
SET max_row = (SELECT COUNT(*)FROM history
IF(new.client != '') THEN
WHERE nom LIKE CONCAT('%', NEW.client, '%') END IF;
IF(new.commune != '') THEN
WHERE commune LIKE CONCAT('%', NEW.commune, '%') END IF;
IF(new.type != '') THEN
WHERE type LIKE CONCAT('%', NEW.type, '%') END IF;
IF(new.matricule != '') THEN
WHERE mat LIKE CONCAT('%', NEW.matricule, '%') END IF;
IF(new.tare != '') THEN
WHERE tare LIKE CONCAT('%', NEW.tare, '%') END IF;
WHERE cancled = 0),
max_tare =(SELECT SUM(tare) FROM history
WHERE cancled = 0) WHERE id = 1;
END
问题是如果我删除所有ifs statement
条件,我的过滤器将不起作用,如果我的过滤器包含一个空字段,它会将结果破坏为 0,如果它是一个空字段,我希望它不计为过滤的参数。我也尝试过案例陈述,这也无济于事。
当我运行代码时,这就是我得到的错误:
MySQL 回复:# 1064 - '
IF
' (new.client! = '') THEN WHERE name LIKE CONCAT ('%', NEW.client, '%')附近的语法错误END IF
;
不知何故,有很长的解决方案可行,但我不需要,因为我不能将其称为解决方案:
它是取出外部,并从获得的每个案例中进行选择,换句话说:
if (client! = 0 and common == 0 and type == 0 and number == 0 and tare == 0) then
{filter by client only}
otherwise-if (client! = 0 and common! = 0 and type == 0 and number == 0 and tare == 0) then
{filter by client and common only} .... ect
我将尝试举例说明:这是我的历史记录表:
| history |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| id | nom | commune | type | mat | tare | rfid | cancled |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 1 | EPIC paris | france | white | 01248-816-16 | 7600 | ABCF44C | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 2 | EPIC london | UK | white | 06854-315-16 | 5233 | A8CG27C | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 3 | NET barça | ESP | red | 03254-615-16 | 8900 | HBC54AC | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 4 | NET Dubai | arab | blue | 35251-117-16 | 11200 | HDK7BV5 | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 5 | EPIC roma | ita | red | 36524-618-16 | 7300 | NBL53DC | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 6 | SNC beta | alpha | green | 69358-117-16 | 5400 | JDLF8ND | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 7 | EPIC tokyo | japan | yellow | 46258-712-16 | 8700 | K5ND55D | 1 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
| 8 | SARL Fit | body | black | 69531-614-16 | 9600 | AIES5HJ | 0 |
+----+-------------+---------+--------+--------------+-------+---------+---------+
我想检索包含 EPIC 的行数,cancled = 0
并且在他们的名字中他们有 EPIC
所以我的文件管理器数组将是这样的:
| temp_fetch |
+----+--------+---------+------+--------------+------+
| id | client | commune | type | matricule | tare |
+----+--------+---------+------+--------------+------+
| 1 | EPIC | | | | |
+----+--------+---------+------+--------------+------+
结果我应该有这样的东西
| max_tare |
+----------------------------+
| id | max_row | max_tare |
+----+------------+----------+
| 1 | 2 | 14900 |
+----+------------+----------+
如果有帮助,我会将我的 php 代码放在那里,但加载结果需要太多时间
$this->db->from('history');
if ($query[1] != '') {
$this->db->like('nom', $query[1]);
}
if ($query[2] != '') {
$this->db->like('commune', $query[2]);
}
if ($query[3] != '') {
$this->db->like('type', $query[3]);
}
if ($query[4] != '') {
$this->db->like('mat', $query[4]);
}
if ($query[10] != '') {
$this->db->like('rfid', $query[10]);
}
if ($query[5] != '') {
$this->db->like('tare', $query[5]);
}
if ($query[6] != '') {
$this->db->where('date >', $query[6]);
}
if ($query[7] != '') {
$this->db->where('date <', $query[7]);
}
if ($query[11] != '') {
$this->db->where('time_plode >', $query[11]);
}
if ($query[12] != '') {
$this->db->where('time_plode <', $query[12]);
}
$this->db->where('cancled', 0);
return $this->db->count_all_results();
解决方案
您在寻找join
andgroup by
吗?
select f.id,
count(*) as num_rows,
sum(tare) as sum_tare
from history h join
temp_fetch f
on h.nom like concat('%', f.client, '%') or
h.commune like concat('%', f.commune, '%') or
h.type like concat('%', f.type, '%') or
h.mat like concat('%', f.matricule, '%') or
h.tare like concat('%', f.tare, '%')
where f.cancled = 0
group by f.id;
推荐阅读
- angular - 使用延迟加载以角度获取路由参数
- sql - 如何通过文本列将 JSONB 列与数组连接?
- angular - 在过渡时禁用 mat-stepper 的动画
- scala - 具有可变伙伴的不可变 POD 类
- c# - 在 FormattableString 和 (str, params object[]) 之间转换
- java - RxJava2:迭代项目并获取匹配条件的项目
- java - AerospikeException - 错误代码 1500:在 lmap.put() 的命名空间上未启用 LDT,即使 ldt-enabled 设置为 true
- sql - 加入显示空值
- openshift - tar:忽略未知的扩展标头关键字“LIBARCHIVE.xattr.security.selinux”
- activiti - Activiti - 多实例服务任务本身在循环中时出现异常