我有一个查询,这是'工作',但速度慢,感觉非常差,我不想更新每次我们添加/删除供应商!
基本上我有一个‘价格’表,对于每个供应商,我需要根据他们最长的代码匹配(这是有效的)得到他们的代码价格,我对每个供应商id重复这个,然后将他们联合在一起,最后将供应商名称加入结果
项目代码123456
select t1.*, s.name from
(
(Select * from prices where ABC123456 like CONCAT(code_prefix , '%') AND supplier_id = 1
ORDER BY LENGTH(code_prefix) Desc Limit 1)
UNION
(Select * from prices where ABC123456 like CONCAT(code_prefix , '%') AND supplier_id = 2
ORDER BY LENGTH(code_prefix) Desc Limit 1)
UNION
(Select * from prices where ABC123456 like CONCAT(code_prefix , '%') AND supplier_id = 3
ORDER BY LENGTH(code_prefix) Desc Limit 1)
... for each supplier
) t1
join supplier s on t1.supplier_id = s.id
order by buy_price asc
1) 我怎么能为任意数量的供应商自动运行这个程序(即为供应商表中的每个供应商运行,然后加入每个供应商的最佳结果)-我无法理解它
2) 性能不是很好,每个查询300毫秒,我有400000个代码(在代码表中)也在运行。我应该作为一个仓库管理员来做这件事吗?这会有很大的区别,虽然它应该只运行时,我们得到一个供应商每月1或2次价格更新!
3) 是否可以填充一个新的表price\u order(code,sequence),其中code来自上面,sequence是供应商id的价格顺序(从低到高),我可以这样做这是一个应用程序,但是有没有更聪明的方法在db中实现更好的性能?
目前正在运行10.0.27-mariadb,但如果真的需要可以更改!
提前谢谢
更新要求
价格表(其他列也存在!)
+---------------+--------------+-----------+
| code_prefix | suppler_id | price. |
+---------------+--------------+-----------+
| ABC123 | 1 | 100 |
| ABC1 | 1 | 123 |
| ABC177 | 1 | 723 |
| ABC12 | 2 | 111 |
| ABC | 2 | 222 |
| ABC111 | 3 | 001 |
| AB | 3 | 234 |
| A | 4 | 010 |
| B | 4 | 710 |
+---------------+--------------+-----------+
我们有另一个代码表,我们需要对照价格表中的前缀进行查找
+---------------+
| code |
+---------------+
| ABC123456 |
| ABC155555 |
| ABC12 |
| ABC7777777 |
+---------------+
因此,对于代码表中的每一行,我需要每个供应商的最佳/最长匹配
so code ABC123456 will return
+---------------+--------------+-----------+
| code_prefix | suppler_id | price |
+---------------+--------------+-----------+
| ABC123 | 1 | 100 |
| ABC12 | 2 | 111 |
| AB | 3 | 234 |
| A | 4 | 010 |
+---------------+--------------+-----------+
code ABC155555 will return
+---------------+--------------+-----------+
| code_prefix | suppler_id | price |
+---------------+--------------+-----------+
| ABC1 | 1 | 123 |
| ABC | 2 | 222 |
| AB | 3 | 234 |
| A | 4 | 010 |
+---------------+--------------+-----------+
然后,我们需要按价格升序对结果进行排序,并连接供应商id以给出供应商订单
+------------+----------------+
| code | suppler_order |
+------------+----------------+
| ABC123456 | 4,1,2,3 |
| ABC155555 | 4,1,2,3 |
| ... | ... |
+------------+----------------+
我希望能说得更清楚,谢谢
1条答案
按热度按时间yqyhoc1h1#
首先定义一个视图,以获取与每个供应商的每个代码前缀匹配的所有行(如果您有较新的mariadb版本,则可以使用cte)。
然后,您可以使用一个常见的习惯用法来获取每个供应商id的代码前缀最大长度的行。
然后你可以加入
codes
表以获取每个代码的分组结果。演示