我正在尝试学习一些(高级或更复杂的)SQL。
假设我有一张汽车表，其中包含每辆车的信息。
然后我有另一张 table ，上面有正在出售的汽车，有些是新的，有些是二手的。

我希望用户能够查找汽车，例如本田思域 2016，并查看汽车信息。
但也希望用户看到正在销售的 所有 Honda civic 2016 汽车，包括该特定年份/型号的最高和最低价格，按新车和二手车分类。

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

这些是我的表。

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
);

它对我来说看起来是多余的，而且随着 table 变大，它注定会变慢。因此，如果有人向我展示更好的方法，我将不胜感激。

/* 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';

我最终希望使用 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: "email@domain.com",
                },
                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: "email@domain.com",
                },
                car: {
                    price: 15000,
                    color: "white",
                    condition: "new",
                    notes: "Some notes about the car"
                }
            }]
        }
    }
}

一旦我检索到信息，我就可以输入该格式。此外，我还必须对数据库进行分页。

基本上，我很想知道做亚马逊所做的事情的最佳方式。一件商品从不同的供应商处以不同的价格出售。亚马逊提供有关所售商品的信息；它的状况、价格、卖家等。此外，亚马逊还会为您提供最低、最高价格以及有关该商品的一般信息。什么是最好的方法？

最佳答案

四个查询应该没问题:

• 一次查询以获取有关汽车类型的一般信息
• 根据条件获取最低/最高价格的另一个查询
• 然后查询每一个以获取可用的新车和二手车的市场列表。您可以懒惰地执行这两个查询 - 首先向您的购物者显示基本汽车信息和最低/最高新/旧价格，然后当您的购物者点击使用过的号码(如亚马逊)时，仅获取二手车的市场报价.

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

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`; 

您的其他查询看起来不错。随着表的增长并且您希望保持快速查询，索引 会有所帮助。基本规则是属于您的 WHERE 谓词的字段最好有索引。此外，表之间的任何 JOIN 键通常都适合建立索引。尝试在 Cars(make, model, year, trim) 上建立索引。

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

关于mysql - 使用最小值、最大值和条件从 SQL 获取结果，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44938107/