mysql查询价格比较 [英] Mysql query for price comparison

问题描述

我正在开发一个价格比较程序。我有三个网站。在我的数据库中，我有三个网上商店的表。每个表都有一个price和一个product_id列。我想列出所有的product_id-s，并看到在每个网上商店的价格。产品ID是唯一的，但在所有表中不一样，因为并不是所有的网上商店都有相同的产品。我正在使用mysql，但我是新数据库。我应该尝试什么查询？

I am working on a price comparison program. I have three websites. In my database I have three tables for the webshops. Each tables has a price and a product_id column. I want to list all the product_id-s and see the prices in every webshop. The product id is unique, but not the same in all of the tables, as not all webshop has the same products. I am working with mysql, but I'm very new to databases. What query should I try?

推荐答案

我建议你有一个主产品表，列出所有产品，它们在所有网站上销售，或只有一个。然后从那里加入每个网站定价表。尝试匹配产品名称。在最简单的形式中，查询wold看起来像：

I suggest you have a "master" product table, that lists all products, regardless of whether they are sold on all sites, or just one. Then join from that to each of the website pricing tables. Try matching on product name. In its simplest form, the query wold look like:

select
  p.*,
  t1.price as site1_price,
  t2.price as site2_price,
  t3.price as site3_price
from product p
left join website1 t1 on t1.name = p.name
left join website2 t2 on t2.name = p.name
left join website2 t3 on t3.name = p.name;

您可能需要尝试加入品牌和型号，品牌= p.brand和t1.model = p.model ，或其他标准，如果名称不是唯一的。

You might have to try joining on brand and model, ie on t1.brand = p.brand and t1.model = p.model, or some other criteria if name is not unique.

要快速填充产品，您可以执行以下操作：

To quickly populate product, you could run this:

insert into product (name, brand, model, ...)
select name, brand, model, ... from website1
union 
select name, brand, model, ... from website2
union 
select name, brand, model, ... from website3;

FYI，使用 UNION UNION ALL ）使联合的输出只产生唯一的行

FYI, the use of UNION (rather than UNION ALL) makes the output of the union produce only unique rows

这篇关于mysql查询价格比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！