mysql比较产品的前两个单词并给出最低价格

Question

database:

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. i want to display product with lowest price.
2. if product is consist of more than two words it must group by first two words and give product with lowest price.
3. output must shows id,name and price.

my queries:

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

output :

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

Desire output :

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

Answer 1

Below is the query that fulfill the requirement. You were having an extra join and a missing group by on outer query.

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;

Added extra records to cross verify the output:- Items:-

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)

Output:-

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)