使用min，max和condition从SQL获取结果

I am trying to learn some (advance or more complex) SQL.
Let's say I have a table of cars, with information of every car.
Then I have another table with cars being sold, some are new, and some are used.

我正在尝试学习一些(先进的或更复杂的)SQL。假设我有一张汽车的桌子,上面有每辆车的信息。然后我有另一张桌子,有车出售,有些是新的,有些是用的。

I want the user to be able to look up a car, a Honda civic 2016 for instance, and see the car info.
But also want the user to see all Honda civic 2016 cars being sold, including the highest and lowest price for that particular year/model, organized by new and used.

我希望用户能够查看汽车,例如本田思域2016,并查看汽车信息。但也希望用户看到所有本田思域2016款车型的销售情况,包括特定年份/车型的最高价和最低价,由新车和二手车组织。

What would be the most efficient way to retrieve all the information - the car info and the ones being sold to display on a page!

什么是最有效的方式来检索所有信息 - 汽车信息和出售在页面上显示的信息!

These are my tables.

这些是我的表。

CREATE TABLE Users(
    id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16) NOT NULL,
    last VARCHAR(16) NOT NULL,
    email VARCHAR(128) NOT NULL,
    phone CHAR(10) NOT NULL,
    joined DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Cars(
    id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    make VARCHAR(32) NOT NULL,
    model VARCHAR(32) NOT NULL,
    year INT(4) NOT NULL,
    trim VARCHAR(16) NOT NULL
);

CREATE TABLE Market(
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    user_id BIGINT(20) NOT NULL,
    car_id BIGINT(20) NOT NULL,
    condition VARCHAR(5) NOT NULL,
    notes VARCHAR(1024) NOT NULL,
    PRIMARY KEY(id),

    CONSTRAINT cfk FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT ufk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

It looks redundant to me and doomed to get slow as the tables get bigger. So I would appreciate if somebody shows me a better way.

它对我来说看起来多余,随着桌子越来越大,注定要变慢。如果有人向我展示更好的方式,我将不胜感激。

/* Get car information*/
SELECT *
FROM Cars 
WHERE make = 'Honda' 
  AND model = 'Civic' 
  AND year = '2017'   
  AND trim = 'EX'; 

/* I also would like to get the min and max price for this particular car*/
/* ?? How ?? */

/* Get (new) cars being sold and sellers */
SELECT M.*, U.* 
FROM Market M 
INNER JOIN Users ON M.user_id = U.id 
WHERE make = 'Honda' 
  AND model = 'Civic' 
  AND year = '2017' 
  AND color = 'white' 
  AND trim = 'EX' 
  AND condition = 'NEW';

/* Get (used) cars being sold and sellers */
SELECT M.*, U.* 
FROM Market M 
INNER JOIN Users ON M.user_id = U.id 
WHERE make = 'Honda' 
  AND model = 'Civic' 
  AND year = '2017' 
  AND color = 'white' 
  AND trim = 'EX' 
  AND condition = 'USED';

I ultimately would like to get something like the following using PHP:

我最终希望使用PHP获得类似下面的内容:

{
    car: {
        make: "Honda",
        model: "Civic",
        year: 2017,
        trim: "EX"
    },
    market: {
        new: {
            min: 'overall min',
            max: 'overall max',
            data: [{
                seller:{
                    name: "John",
                    last: "Smith",
                    phone: "xxx-xxx-xxxx",
                    email: "[e-mail]",
                },
                car: {
                    price: 15000,
                    color: "white",
                    condition: "used",
                    notes: "Some notes about the car"
                }
            }]
        },
        used: { 
            min: 'overall min',
            max: 'overall max',
            data: [{
                seller:{
                    name: "John",
                    last: "Smith",
                    phone: "xxx-xxx-xxxx",
                    email: "[e-mail]",
                },
                car: {
                    price: 15000,
                    color: "white",
                    condition: "new",
                    notes: "Some notes about the car"
                }
            }]
        }
    }
}

I am able to put into that format once I retrieve the information. Also I would have to paginate through the database.

一旦我检索到信息,我就可以使用这种格式。此外,我将不得不通过数据库分页。

Basically I am interested on knowing the best way to do what Amazon does. An item is for sale from different vendors, and at different prices. Amazon provides information about the item being sold; its condition, price, seller, etc. Also, Amazon gives you the lowest, highest price and the information about the item in general. What is the best approach?

基本上我有兴趣了解做亚马逊的最佳方式。物品可以从不同的供应商处以不同的价格出售。亚马逊提供有关待售物品的信息;它的条件,价格,卖方等。此外,亚马逊为您提供最低,最高价格和一般项目的信息。什么是最好的方法?

3 个解决方案

#1

Four queries should be fine:

四个查询应该没问题:

• One query to get the general info about the type of Car

一个查询,以获取有关汽车类型的一般信息

• Another query to get the min/max prices by condition

另一个通过条件获取最小/最大价格的查询

• Then queries each to get the Market list of available cars for new and used. You could do these two queries lazily - first display to your shoppers the basic car info and min/max new/used prices, and then when your shopper clicks on the used number (like Amazon), fetch the Market offers just for the used cars.

然后查询每个以获取新的和使用的可用汽车的市场列表。您可以懒洋洋地进行这两个查询 - 首先向您的购物者显示基本的汽车信息和最小/最大新/使用价格,然后当您的购物者点击使用过的号码(如亚马逊)时,获取二手车的市场报价。

For the min/max sales, you are needing to do an aggregation, so GROUP BY is your friend. Try this:

对于最小/最大销售额,您需要进行聚合,因此GROUP BY是您的朋友。尝试这个:

SELECT `condition`, MIN(price) min_price, MAX(price) max_price
FROM Cars
JOIN Market ON (Cars.id = Market.car_id)
WHERE make = 'Honda'
  AND model = 'Civic'
  AND year = '2017'
  AND trim = 'EX'
GROUP BY `condition`; 

Your other queries look good. As the tables grow and you want to keep the queries quick, indexing will help. Basic rule is that the fields that are part of your WHERE predicate are good to have indexed. Also any JOIN keys between tables are usually good to have indexed. Try an index on Cars(make, model, year, trim).

您的其他查询看起来不错。随着表的增长以及您希望快速保持查询,索引将有所帮助。基本规则是作为WHERE谓词一部分的字段很适合索引。表之间的任何JOIN键通常都可以编入索引。尝试使用汽车指数(品牌,型号,年份,装饰)。

Also, condition is a reserved word in MySQL 5.7, which is why I escaped using backticks. Consider using cond instead, and if you only have a few conditions {"new", "used"}, consider using an ENUM data type. Beware MIN and MAX are also reserved db words.

此外,条件是MySQL 5.7中的保留字,这就是我使用反引号转义的原因。请考虑使用cond,如果您只有几个条件{“new”,“used”},请考虑使用ENUM数据类型。当心MIN和MAX也是保留的db字。

#2

You can retrieve the information you need with two queries. For example - You can get the lowest price for new and used cars along with the car info in one query using correlated subqueries in the SELECT clause:

您可以使用两个查询检索所需的信息。例如 - 您可以使用SELECT子句中的相关子查询在一个查询中获得新车和二手车的最低价格以及汽车信息:

SELECT c.*, 
    (SELECT MIN(m.price) FROM Market m WHERE m.car_id = c.id and m.condition = 'NEW')  as new_min_price,
    (SELECT MIN(m.price) FROM Market m WHERE m.car_id = c.id and m.condition = 'USED') as used_min_price
FROM Cars c
WHERE c.make = 'Honda' 
  AND c.model = 'Civic' 
  AND c.year = '2017'   
  AND c.trim = 'EX'

For best performance I would create composite indexes Cars(make, model, year, trim) and Market(car_id, condition, price). The order of the columns in the first index isn't important and you can change it. The optimizer can adjust the execution plan to the order you define. However the order for the second index must be this way to get the lowest price most efficiently. If you have an index on Market(car_id) (which is probably created by the FOREIGN KEY definition), you can drop it. The new composite index can be used instead.

为了获得最佳性能,我将创建复合索引Cars(品牌,型号,年份,装饰)和市场(car_id,条件,价格)。第一个索引中列的顺序并不重要,您可以更改它。优化程序可以根据您定义的顺序调整执行计划。但是,第二个索引的顺序必须通过这种方式才能最有效地获得最低价格。如果您在Market(car_id)上有一个索引(可能是由FOREIGN KEY定义创建的),您可以删除它。可以使用新的复合索引。

Note that I didn't include the highest price, since I don't think anybody cares. But you can get it the same way as you get the lowest price by useing MAX() instead of MIN().

请注意,我没有包含最高价格,因为我认为没有人关心。但是你可以通过使用MAX()而不是MIN()获得最低价格。

There is another way to get the same data using "conditional aggregation":

还有另一种方法可以使用“条件聚合”获取相同的数据:

SELECT c.*,
    MIN(CASE m.condition = 'NEW' THEN m.price END)  as new_min_price,
    MIN(CASE m.condition = 'USED' THEN m.price END) as used_min_price
FROM Cars c
JOIN Market m ON m.car_id = c.id
WHERE c.make = 'Honda' 
  AND c.model = 'Civic' 
  AND c.year = '2017'   
  AND c.trim = 'EX'
GROUP BY c.id

But this query can't use the indexes in the best way, since the engine will need to loop through all offers in order to find the lowest price.

但是这个查询不能以最好的方式使用索引,因为引擎需要循环遍历所有商品才能找到最低价格。

Now to get all the offers from the Market table, you don't need to execute two queries (one for each condition). You can fetch them all with one query and group the result by the condition field in your application language. Also since you already know the car_id from the first query, you can use it as the search criteria, so you don't need to touch the cars table again. (Note that your last two queries wouldn't work without a JOIN with the cars table, since the market table doesn't have the columns make, model, year and trim.)

现在要从Market表中获取所有商品,您不需要执行两个查询(每个条件一个)。您可以使用一个查询获取所有查询,并按应用程序语言中的条件字段对结果进行分组。此外,由于您已经知道第一个查询中的car_id,因此您可以将其用作搜索条件,因此您无需再次触摸汽车表。 (请注意,如果没有与cars表的JOIN,您的最后两个查询将无法工作,因为市场表没有列make,model,year和trim。)

SELECT
    m.condition
    m.id as market_id,
    m.user_id,
    u.name,
    u.last
FROM Market m
INNER JOIN Users u ON m.user_id = u.id
WHERE m.car_id = ?
ORDER BY price

Note that if you use SELECT m*, u*, the id field will be ambiguous since it is defined in both tables. So you should list all fields you want to fetch and use an alias for ambiguous columns like m.id as market_id.

请注意,如果使用SELECT m *,u *,则id字段将不明确,因为它在两个表中都已定义。因此,您应该列出要获取的所有字段,并使用m.id等不明确的列的别名作为market_id。

You didn't mention the language you use - So I can't tell you exactly how to group the result by the condition field. For example with PHP PDO you could use the PDO::FETCH_GROUP mode. However if your language doesn't support such functionality you can group/split the result in a simple loop.

您没有提到您使用的语言 - 所以我无法确切地告诉您如何通过条件字段对结果进行分组。例如,使用PHP PDO,您可以使用PDO :: FETCH_GROUP模式。但是,如果您的语言不支持此类功能,则可以在简单的循环中对结果进行分组/拆分。

#3

A lot of people may not like this but I have solved some of the most complex SQL statements using MS Access. First simply use MS access to design tables and relationships. Then use the query builder to design queries. Then change query builder to SQL view to view the SQL Statements. To use this SQL code on other DB engines require minor changes.

很多人可能不喜欢这个,但我已经使用MS Access解决了一些最复杂的SQL语句。首先,只需使用MS访问来设计表和关系。然后使用查询构建器来设计查询。然后将查询构建器更改为SQL视图以查看SQL语句。要在其他数据库引擎上使用此SQL代码,需要进行细微更改。

SQL Coding Kings hate MS Access but in reality, it is the best development tool when starting to build any database. It allows easy and fast changes, and in single user mode is the fastest DB around.

SQL Coding Kings讨厌MS Access,但实际上,它是开始构建任何数据库时最好的开发工具。它允许轻松快速的更改,而在单用户模式下是最快的数据库。

If you plan to build lots of databases and want to save time by not becoming a SQL coding king then us MS Access as a development tool!

如果您计划构建大量数据库并希望通过不成为SQL编码王来节省时间,那么我们将MS Access作为开发工具!