Mysql Select 分组产品的数量和每个产品的最便宜的价格

假设您有版本8或更高版本(现在应该是这样)，将行号分配给出价最低的联合产品，但没有出价

DROP TABLE IF EXISTS PRDUCTS,BIDS;

CREATE TABLE Prducts
( ProductID INT, Name  VARCHAR(20), Size VARCHAR(20), Weight VARCHAR(20), Color  VARCHAR(20));
INSERT INTO PRDUCTS VALUE
( 1              , 'Black Table' , '1m'   , '2kg'    , 'Black'  ), 
( 2              , 'Purple Table', '1m'   , '3kg'    , 'Purple' ), 
( 3              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ), 
( 4              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ), 
( 5              , 'Black Table' , '1m'   , '2kg'    , 'Black'  ), 
( 6              , 'Purple Table', '1m'   , '3kg'    , 'Purple' ),
( 7              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ), 
( 8              , 'Small Table' , '1m'   , '3kg'    , 'Black'  ),
( 9              , 'BOMBASTO'    , '1m'   , '3kg'    , 'Black'  ); 

CREATE TABLE Bids
( BidID    INT     , ProductID  INT  , Status VARCHAR(20) ,     Price DECIMAL(10,2));
INSERT INTO BIDS VALUES
( 1              ,  1          , 'Active'      , 123.5   ),
( 2              ,  1          , 'Active'      , 325.99  ),
( 3              ,  1          , 'Active'      , 85.99   ),
( 4              ,  3          , 'Cancelled'   , 315.99  ),
( 5              ,  4          , 'Active'      , 113.5   ),
( 6              ,  3          , 'Cancelled'   , 305.99  ),
( 7              ,  1          , 'Active'      , 82.99   ),
( 8              ,  2          , 'Active'      , 345.99  );

WITH CTE AS
(SELECT P.PRODUCTID PPID,NAME,SIZE,WEIGHT,COLOR
       ,B.BIDID,B.PRODUCTID AS BPID,STATUS,PRICE
         ,ROW_NUMBER() OVER (PARTITION BY NAME,SIZE,WEIGHT,COLOR ORDER BY PRICE) RN
FROM PRDUCTS P
JOIN BIDS B ON B.PRODUCTID = P.PRODUCTID
) 
SELECT PPID,NAME,SIZE,WEIGHT,COLOR,PRICE FROM CTE 
WHERE  RN = 1
UNION ALL
SELECT DISTINCT PRODUCTID,NAME,SIZE,WEIGHT,COLOR,NULL
FROM   PRDUCTS P
WHERE  NOT EXISTS
        (SELECT 1 FROM CTE WHERE CTE.NAME = P.NAME      AND
                                         CTE.WEIGHT = P.WEIGHT  AND
                                         CTE.COLOR = P.COLOR);

+------+--------------+------+--------+--------+--------+
| PPID | NAME         | SIZE | WEIGHT | COLOR  | PRICE  |
+------+--------------+------+--------+--------+--------+
|    1 | Black Table  | 1m   | 2kg    | Black  |  82.99 |
|    2 | Purple Table | 1m   | 3kg    | Purple | 345.99 |
|    4 | Small Table  | 1m   | 3kg    | Black  | 113.50 |
|    9 | BOMBASTO     | 1m   | 3kg    | Black  |   NULL |
+------+--------------+------+--------+--------+--------+
4 rows in set (0.003 sec)