按部分字符串分组

Question

下面是我的表格：

----------+-----------+--------------
45678-sm-w|   18      | Mens clothing
----------+-----------+--------------
45678-sm-b|   5       | Mens clothing
----------+-----------+--------------
2189-L-Wh |   4       | Juniors Clothing
----------+-----------+--------------
2189-L-Bl |   3       | Juniors Clothing
----------+---------- +--------------

这就是预期的结果：

----------+-----------+--------------
45678     |   23      | Mens clothing
----------+-----------+--------------
2189      |   7       | Juniors Clothing
----------+-----------+--------------

Answer 1

你能改一下桌子吗？最有意义的做法是将col1拆分为三列(例如id、size和color)，这样您就可以只按新的id列进行分组：

-----+----+----+-----------+--------------
45678| sm | w  |   18      | Mens clothing
-----+----+----+-----------+--------------
45678| sm | b  |   5       | Mens clothing
-----+----+----+-----------+--------------
2189 |  L | Wh |   4       | Juniors Clothing
-----+----+----+-----------+--------------
2189 |  L | Bl |   3       | Juniors Clothing
-----+----+----+---------- +--------------

为此，您可以使用ALTER TABLE添加所需的三列并更改主键，使用SUBSTRING_INDEX和UPDATE提取和存储拆分值，使用另一个ALTER TABLE进行清理。如下所示：

ALTER TABLE t
DROP PRIMARY KEY;

ALTER TABLE T
ADD id INT UNSIGNED NOT NULL,
ADD size VARCHAR(255),
ADD color VARCHAR(255),
ADD PRIMARY KEY (col1);

UPDATE TABLE t
SET id = CONVERT(SUBSTRING_INDEX(col1, '-', 1), UNSIGNED INTEGER),
SET size = SUBSTRING_INDEX(col1, '-', 2),
SET color = SUBSTRING_INDEX(col1, '-', 3);

ALTER TABLE t
DROP COLUMN col1;

如果无法更改该表，则可以使用SUBSTRING_INDEX提取所需的id号

SELECT SUBSTRING_INDEX(col1, '-', 1), col2, col3 FROM table;

提供：

-----+-----------+--------------
45678|   18      | Mens clothing
-----+-----------+--------------
45678|   5       | Mens clothing
-----+-----------+--------------
2189 |   4       | Juniors Clothing
-----+-----------+--------------
2189 |   3       | Juniors Clothing
-----+---------- +--------------

对于最后的查询，我将使用一个SELECT提取所需的id，并使用一个外部SELECT进行分组：

SELECT id, SUM(col2) AS total, col3
FROM 
    (SELECT SUBSTRING_INDEX(col1, '-', 1) AS id, col2, col3
    FROM table) AS t
GROUP BY id

Answer 2

如果您选择的是数据，则可以使用类似以下内容：

select SUBSTRING_INDEX(col1, '-', 1) col1, 
  sum(col2) Total, 
  col3
from table1
group by SUBSTRING_INDEX(col1, '-', 1), SUBSTRING_INDEX(col3, ' ', 1)
order by col1 desc

请参阅SQL Fiddle With Demo

Answer 3

将第一列转换为数字，向新创建的列添加索引，并在该列上运行group by sum。

update table set `left`=CAST(col1 AS SIGNED );

select `left`,sum(*),`col3` from table;