MySQL - 来自行的 PHP 唯一值 [英] MySQL - PHP unique values from rows

问题描述

我有这张桌子:表一

+----+-----------------------+----------+------+-------+
| 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   |
+----+-----------------------+----------+------+-------+

和这个代码sql:

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

如何为国家/地区列设置唯一值并返回:USA = 48(ID:1+5+7)；英国= 6;德国=3；法国 = 44(ID:4+6+8).我希望那些包含两个、三个、四个国家的行被淘汰，并且只保留行中的第一个国家.谢谢！

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!

推荐答案

使用 substring_index 获取;分隔列表中的第一个国家.

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

如果您想保留每个国家/地区出现的所有行，情况会复杂得多(在这种情况下，我建议使用 PHP 而不是 MySQL，因为 MySQL 没有内置的方法来执行 爆炸()).

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()).

这篇关于MySQL - 来自行的 PHP 唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！