php - mysql比较produst的前两个单词并给出最低价格

Question

数据库:

id  name            price

1   apple iphone    500
2   apple iphone    300
3   apple iphone    250
4   apple iphone    400
5   nokia xl        300
6   nokia xl abc    200
7   nokia xl        250

1. 我想以最低的价格展示产品。
2. 如果产品由两个以上的单词组成，则必须按前两个单词进行分组，并给出价格最低的产品。
3. 输出必须显示 ID、名称和价格。

我的查询:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

SELECT mt.*,TRIM( CONCAT(SPLIT_STR(`name`, " ",1)," ",SPLIT_STR(`name`, " ",2))) as shrt   
FROM items mt INNER JOIN
    (
        SELECT ID, MIN(price) MinPrice,TRIM( CONCAT(SPLIT_STR(`name`, " ",1)," ",SPLIT_STR(`name`, " ",2))) as shrt
        FROM items
        GROUP BY shrt
    ) t ON shrt = t.shrt AND mt.price = t.MinPrice

输出:

id  name            price   shrt
3   apple iphone    250     apple iphone
6   nokia xl abc    200     nokia xl
7   nokia xl        250     nokia xl

期望输出:

id  name            price   shrt
3   apple iphone    250     apple iphone
6   nokia xl abc    200     nokia xl

Answer 1

下面是满足要求的查询。您在外部查询上有一个额外的联接和缺少的分组依据。

SELECT mt.* FROM items mt  JOIN(SELECT ID, MIN(price) MinPrice,TRIM( CONCAT(SPLIT_STR(`name`, " ",1)," ",SPLIT_STR(`name`, " ",2))) as shrt FROM items GROUP BY shrt) t ON  mt.price = t.MinPrice group by shrt;

添加了额外的记录来交叉验证输出:-项目:-

mysql> select * from items;
+----+--------------------+-------+
| id | name               | price |
+----+--------------------+-------+
|  1 | apple iphone       |   500 |
|  2 | apple iphone       |   300 |
|  3 | apple iphone       |   400 |
|  4 | apple iphone       |   250 |
|  5 | apple iphone       |   300 |
|  6 | nokia x1           |   300 |
|  7 | nokia x1 abc       |   200 |
|  8 | nokia x1           |   250 |
| 10 | motorolla Gx two   |   500 |
| 11 | motorolla Gx       |   500 |
| 12 | motorolla Gx three |   150 |
+----+--------------------+-------+
11 rows in set (0.00 sec)

输出:-

mysql>  SELECT mt.* FROM items mt  JOIN(SELECT ID, MIN(price) MinPrice,TRIM( CONCAT(SPLIT_STR(`name`, " ",1)," ",SPLIT_STR(`name`, " ",2))) as shrt FROM items GROUP BY shrt) t ON  mt.price = t.MinPrice group by shrt;
+----+--------------------+-------+
| id | name               | price |
+----+--------------------+-------+
|  4 | apple iphone       |   250 |
| 12 | motorolla Gx three |   150 |
|  7 | nokia x1 abc       |   200 |
+----+--------------------+-------+
3 rows in set (0.00 sec)