如何改进联合查询?

xdnvmnnf  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(343)

我有一个查询,这是'工作',但速度慢,感觉非常差,我不想更新每次我们添加/删除供应商!
基本上我有一个‘价格’表,对于每个供应商,我需要根据他们最长的代码匹配(这是有效的)得到他们的代码价格,我对每个供应商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        |
| ...        | ...            |
+------------+----------------+

我希望能说得更清楚,谢谢

yqyhoc1h

yqyhoc1h1#

首先定义一个视图,以获取与每个供应商的每个代码前缀匹配的所有行(如果您有较新的mariadb版本,则可以使用cte)。

CREATE VIEW supplier_codes AS
SELECT DISTINCT c.code, p.code_prefix AS code_prefix, p.supplier_id, p.price
FROM codes AS c
JOIN prices AS p ON c.code LIKE CONCAT(p.code_prefix, '%');

然后,您可以使用一个常见的习惯用法来获取每个供应商id的代码前缀最大长度的行。

SELECT t1.*
  FROM supplier_codes AS t1
  JOIN (
    SELECT supplier_id, MAX(LENGTH(code_prefix)) AS maxlength
    FROM supplier_codes
    GROUP BY supplier_id
  ) AS t2 ON t1.supplier_id = t2.supplier_id AND LENGTH(t1.code_prefix) = t2.maxlength

然后你可以加入 codes 表以获取每个代码的分组结果。

SELECT c.code, GROUP_CONCAT(DISTINCT sc.supplier_id ORDER BY sc.price) AS supplier_order
FROM codes AS c
JOIN (
  SELECT t1.*
  FROM supplier_codes AS t1
  JOIN (
    SELECT supplier_id, MAX(LENGTH(code_prefix)) AS maxlength
    FROM supplier_codes
    GROUP BY supplier_id, code
  ) AS t2 ON t1.supplier_id = t2.supplier_id AND LENGTH(t1.code_prefix) = t2.maxlength
) AS sc ON c.code = sc.code
GROUP BY c.code;

演示

相关问题