mysql比较了produst的前两个单词并给出了最低价格 - mysql compare first two words of produst and give lowest price

mysql compare first two words of produst and give lowest price

数据库：

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

我想展示价格最低的产品
若产品由两个以上的单词组成，则必须按前两个单词分组，并给出价格最低的产品
输出必须显示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

下面是满足要求的查询。在外部查询中，您有一个额外的联接和一个丢失的组。

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)