时间戳

首页 > 解决方案 > SQLite GROUP BY 最低价格缺失结果

sql - SQLite GROUP BY 最低价格缺失结果

问题描述

我有一个 SQLite 数据库，它基本上包含产品和价格列表。

CREATE TABLE brand_list (
        id INTEGER NOT NULL,
        brand_name TEXT NOT NULL,
        PRIMARY KEY (id)
);
CREATE TABLE product_list (
        id INTEGER NOT NULL,
        prod_generic VARCHAR NOT NULL,
        prod_pretty VARCHAR NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (prod_generic)
);
CREATE TABLE qty_list (
        id INTEGER NOT NULL,
        qty_initial VARCHAR NOT NULL,
        PRIMARY KEY (id)
);
CREATE TABLE vendor_list (
        id INTEGER NOT NULL,
        vendor_name TEXT NOT NULL,
        vendor_url VARCHAR NOT NULL, url_pretty varchar not null default 1, datacollect varchar not null default 'Y',
        PRIMARY KEY (id)
);
CREATE TABLE listing (
        id INTEGER NOT NULL,
        brand_id INTEGER,
        qty_id INTEGER,
        vendor_id INTEGER,
        prod_id INTEGER,
        qty_actual INTEGER,
        price INTEGER,
        unit_price FLOAT,
        timestamp VARCHAR(255),
        sale INTEGER DEFAULT 0 NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY(brand_id) REFERENCES brand_list (id),
        FOREIGN KEY(prod_id) REFERENCES product_list (id),
        FOREIGN KEY(qty_id) REFERENCES qty_list (id),
        FOREIGN KEY(vendor_id) REFERENCES vendor_list (id)

我拉了一个查询，查看列表，目的是获取最低价格并列出最终用户的所有相关信息（即品牌、产品、数量、价格、何时拉出、从何处拉出）。

select qty_initial, qty_actual, price, MIN(unit_price), brand_name, prod_pretty, sale, timestamp, vendor_name from listing left join brand_list on listing.brand_id = brand_list.id left join product_list on listing.prod_id = product_list.id left join vendor_list on listing.vendor_id = vendor_list.id left join qty_list on listing.qty_id = qty_list.id group by prod_pretty order by brand_name, prod_pretty

我看到的是大部分数据都显示了，但并非所有数据都显示了。它似乎并不一致——但一个品牌可能会展示八分之七的参赛作品，另一个品牌可能拥有一切。

有趣的是，如果我在 group by 之前添加一个 WHERE 子句并指定一个我知道原始输出中缺少条目的品牌，我将获得该品牌的所有预期输出。

这不是我的世界——我只是为了自己的乐趣而拼凑一些东西。我敢肯定我在这里遗漏了一些基本的东西，它很可能围绕着 GROUP BY。任何方向都会很棒——甚至是一种重新思考我如何以更简单的方式到达我想去的地方的方法。

标签： sqlsqlite

解决方案

您的查询格式错误——尽管 SQLite 确实接受它。列GROUP BY与SELECT列表不一致。

相反，使用窗口函数：

select qty_initial, qty_actual, price, unit_price, brand_name, prod_pretty, sale, timestamp, vendor_name
from (select qty_initial, qty_actual, price, unit_price, brand_name, prod_pretty, sale, timestamp, vendor_name,
             row_number() over (partition by prod_pretty order by unit_price) as seqnum
      from listing left join
           brand_list
           on listing.brand_id = brand_list.id left join
           product_list
           on listing.prod_id = product_list.id left join 
           vendor_list
           on listing.vendor_id = vendor_list.id left join
           qty_list
          on listing.qty_id = qty_list.id
     group by prod_pretty
    ) p
where seqnum = 1
order by brand_name, prod_pretty

推荐阅读