MySQL - 来自行的PHP唯一值

Question

I have this table: table 1

+----+-----------------------+----------+------+-------+
| ID | COUNTRY               | QUANTITY | EACH | PRICE |
+----+-----------------------+----------+------+-------+
| 1  | U.S.A                 |     1    |  12  | 1*12  |
| 2  | U.K.                  |     2    |   3  | 2* 3  |
| 3  | GERMANY               |   NULL   |   3  |       |
| 4  | FRANCE;GERMANY; U.S.A |     0    |   7  |       |
| 5  | U.S.A;GERMANY         |     3    |   8  | 3*8   |
| 6  | FRANCE;U.K.           |     1    |  10  | 1*10  |
| 7  | U.S.A;FRANCE          |     2    |   6  | 2*6   |
| 8  | FRANCE;FRANCE         |     9    |   3  | 9*3   |
+----+-----------------------+----------+------+-------+

and this code sql:

SELECT
  COUNTRY,
  SUM(COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY), 1) * EACH) AS PRICE
FROM table1
GROUP BY COUNTRY

How could I make unique values for the country column and return: USA = 48 (ID: 1+5+7); UK= 6; GERMANY=3; FRANCE = 44 (ID: 4+6+8). I want that the rows, those contain two, three, four countries to be eliminated and to remain only the first country from row. Thank you!

</div>

Answer 1

Use substring_index to get the first country in the ;-separated list.

SELECT
  SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY
  SUM(IF(QUANTITY IS NULL OR QUANTITY = 0,1,QUANTITY) * EACH) AS PRICE
FROM table1
GROUP BY COUNTRY

It would be much more complicated if you wanted to keep all the rows where each country appears (in that case I would recommend doing it in PHP, not MySQL, since MySQL doesn't have a builtin way to do explode()).