我試圖獲得一個復雜的（至少對我而言）MySQL 查詢，但我無法得到我想要的結果。
我有一個帶有 2 個表的資料庫：
商店
身份證，姓名
產品
id、store_id、硬體、名稱、價格、url、通知
我想為每個 store_id 獲得 3 個產品，其中 notify = 1、hardware = 'gpu' 并按價格 asc 訂購。
所以如果我啟動這個 SQL 陳述句：
SELECT s.name as store, p.id as id, p.name as name, p.hardware as hw, p.price as price, p.url as url, p.notify as notify
FROM products p JOIN stores s
ON p.store_id = s.id
AND p.hardware='gpu'
AND p.notify = 1
GROUP BY store
ORDER BY p.price asc;
我每個 store_id 只獲得一個產品，我想獲得每個 store_id 的（最大）樹元素。像下一個例子：
| 店鋪 | ID | 名稱 | 硬體 | 價格 | 網址 | 通知 |
|---|---|---|---|---|---|---|
| 商店1 | 1 | 名稱1 | 顯卡 | 價格 | 網址 | 1 |
| 商店1 | 2 | 名稱2 | 顯卡 | 價格 | 網址 | 1 |
| 商店1 | 3 | 名稱3 | 顯卡 | 價格 | 網址 | 1 |
| 商店2 | 8 | 名稱8 | 顯卡 | 價格 | 網址 | 1 |
| 商店2 | 12 | 名稱12 | 顯卡 | 價格 | 網址 | 1 |
| 商店3 | 22 | 名稱22 | 顯卡 | 價格 | 網址 | 1 |
等等...
我怎樣才能做到這一點？
示例資料
商店
CREATE TABLE `stores` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(50) NOT NULL,
`afiliate` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
產品
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`store_id` int(11) NOT NULL,
`hardware` char(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`price` decimal(10,2) NOT NULL,
`notify` tinyint(4) NOT NULL
PRIMARY KEY (`id`),
KEY `Store_id_to_products` (`store_id`),
CONSTRAINT `Store_id_to_products` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1961 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
select id, name from stores;
結果
| ID | 名稱 |
|---|---|
| 1 | 亞馬遜 |
| 2 | 易趣 |
| 3 | 百思買 |
| 4 | 目標 |
select * from products;
結果
| ID | store_id | 硬體 | 名稱 | 價格 | 通知 |
|---|---|---|---|---|---|
| 1 | 3 | 顯卡 | EVGA GeForce RTX 3070 FTW3 超游戲 8GB GDDR6 | 949.00 | 1 |
| 2 | 3 | 顯卡 | 索泰游戲 GeForce RTX 3090 Trinity OC 24GB GDDR6X | 2259.00 | 1 |
| 3 | 3 | 顯卡 | EVGA GeForce RTX 3090 FTW3 超游戲 24GB GDDR6X | 2749.00 | 1 |
| 4 | 3 | 顯卡 | Asus TUF Gaming GeForce RTX 3090 OC Edition 24GB GDDR6X | 2549.00 | 1 |
| 5 | 1 | gpu | Gigabyte GTX 1660 Super OC 6G | 489.00 | 1 |
| 6 | 1 | gpu | Asus TUF GTX 1660 Super OC 6GB | 428.99 | 1 |
| 8 | 1 | gpu | GIGABYTE RTX 3060 GAMING OC 12GB | 689.00 | 1 |
| 9 | 1 | gpu | Gigabyte RTX 3080 Aorus Master 10GB | 1398.99 | 1 |
| 10 | 2 | gpu | Asus ROG Strix RTX 3080 OC 10GB Blanca | 1312.00 | 1 |
| 11 | 2 | gpu | Gigabyte RTX 3080 Gaming OC 10GB | 1258.99 | 1 |
| 12 | 2 | gpu | Asus ROG Strix RTX 3090 OC Gaming 24GB | 2599.00 | 1 |
| 13 | 4 | gpu | Asus TUF Gaming Radeon RX 6800 OC 16GB | 1078.99 | 1 |
| 14 | 4 | gpu | GIGABYTE RX6800XT GAMING OC 16GB | 450.00 | 1 |
| 15 | 4 | gpu | Gigabyte GeForce GTX 1660 Ti OC 6G | 499.95 | 1 |
| 16 | 4 | gpu | ZOTAC GeForce GTX 1660 SUPER Twin Fan | 529.95 | 1 |
I'm trying to do a SQL sentence that get 3 cheapest products per store. This is the result that I want:
| id | store_id | hardware | name | price | notify |
|---|---|---|---|---|---|
| 1 | 3 | gpu | EVGA GeForce RTX 3070 FTW3 Ultra Gaming 8GB GDDR6 | 949.00 | 1 |
| 2 | 3 | gpu | Zotac Gaming GeForce RTX 3090 Trinity OC 24GB GDDR6X | 2259.00 | 1 |
| 4 | 3 | gpu | Asus TUF Gaming GeForce RTX 3090 OC Edition 24GB GDDR6X | 2549.00 | 1 |
| 5 | 1 | gpu | Gigabyte GTX 1660 Super OC 6G | 489.00 | 1 |
| 6 | 1 | gpu | Asus TUF GTX 1660 Super OC 6GB | 428.99 | 1 |
| 8 | 1 | gpu | GIGABYTE RTX 3060 GAMING OC 12GB | 689.00 | 1 |
| 10 | 2 | gpu | Asus ROG Strix RTX 3080 OC 10GB Blanca | 1312.00 | 1 |
| 11 | 2 | gpu | Gigabyte RTX 3080 Gaming OC 10GB | 1258.99 | 1 |
| 12 | 2 | gpu | Asus ROG Strix RTX 3090 OC Gaming 24GB | 2599.00 | 1 |
| 14 | 4 | gpu | GIGABYTE RX6800XT GAMING OC 16GB | 450.00 | 1 |
| 15 | 4 | gpu | Gigabyte GeForce GTX 1660 Ti OC 6G | 499.95 | 1 |
| 16 | 4 | gpu | ZOTAC GeForce GTX 1660 SUPER Twin Fan | 529.95 | 1 |
Thanks in advance!!
uj5u.com熱心網友回復：
如果您的 MySQL 版本低于 8 ，您可以使用：
SELECT
t1.id,
name,
hardware,
price,
notify
FROM (
SELECT
id,
store_id,
hardware,
notify,
price,
@rn := IF(@prev = store_id, @rn 1, 1) AS rn,
@prev := store_id
FROM products
JOIN ( SELECT @prev := NULL, @rn := 0) AS vars
where hardware='gpu' and notify=1
ORDER BY price ASC
) AS t1
INNER JOIN stores st on t1.store_id=st.id
WHERE rn <= 5
ORDER BY id asc ;
演示
在 MySQL 8
WITH my_table AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY price ASC ) AS rownum
FROM products
)
SELECT my_table.id,st.name,hardware,my_table.name,price,notify
FROM my_table
INNER JOIN stores st on my_table.store_id=st.id
WHERE rownum <= 5
order by my_table.id asc;
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/417204.html
標籤：