mysql - 将逗号分隔的重复值分组

Question

我有一个包含这些值的表格

SITE_NAME | CATEGORY |
----------------------
SITE1 | CAR, TRAVEL
SITE2 | TRAVEL
SITE3 | SPORT, GAME
SITE4 | GAME
SITE5 | CAR
SITE6 | TRAVEL
SITE7 | GAME

我希望它聚合重复的值，所以我使用这个:

SELECT category, COUNT (*) FROM table_db group by category having count (*)> = 1

这适用于对相等的“类别”值进行分组，但将“CAR，TRAVEL”视为“CAR”以外的值，我希望它也被识别为重复值。

此代码显示:

CAR, TRAVEL
TRAVEL
SPORT, GAME
CAR
GAME

我希望它看起来像这样:

CAR
TRAVEL
SPORT
GAME

Answer 1

虽然我完全同意有关数据库设计的其他评论，但如果出于某种原因，您坚持现有的设计，那么您需要为自己创建一个拆分函数。像这样的事情:

CREATE FUNCTION public.fnsplit(
    IN stringlist character varying,
    IN delimit character varying)
  RETURNS TABLE(items character varying) AS
$BODY$
declare remainderlist character varying;
declare front character varying;
declare delimitpos integer;
begin
    drop table if exists tmptbl;
    create temp table tmptbl(items character varying);
    remainderlist := $1;
    delimitpos := strpos(remainderlist, $2);
    while delimitpos > 0 loop
        front := trim(both from(left(remainderlist, delimitpos -1)));
        remainderlist := substr(remainderlist, delimitpos + 1);
        if length(front) > 0 then
            insert into tmptbl values (front);
        end if;
        delimitpos := strpos(remainderlist, $2);
    end loop;
    --insert last value
    remainderlist := trim(both from remainderlist);
    if length(remainderlist) > 0 then
        insert into tmptbl values (remainderlist);
    end if;
    return query
        select * from tmptbl;
        return;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

然后您就可以在您的选择中使用它，如下所示:

SELECT category, COUNT (*) FROM
(SELECT fnsplit(category, ', ') as category FROM table_db) d
group by category having count(*) >= 1;

不过，我忍不住强调，这应该是最后的手段!

编辑

有人指出OP想要MySQL。这有点棘手，因为 MySQL 不允许函数返回表。所以你必须使用临时表来代替。所以现在这个函数看起来像这样:

DELIMITER $$
CREATE PROCEDURE fnsplit(
    stringlist varchar(2000),
    delimit varchar(20)
) 
BEGIN

declare remainderlist varchar(2000);
declare front varchar(2000);
declare delimitpos integer;

    SET remainderlist = stringlist;
    SET delimitpos = position(delimit in remainderlist);
    while delimitpos > 0 do
        SET front = trim(both from(left(remainderlist, delimitpos -1)));
        SET remainderlist = substr(remainderlist, delimitpos + 1);
        if length(front) > 0 then
            insert into tblTmpSplit values (front);
        end if;
        SET delimitpos = position(delimit in remainderlist);
    end while;
    SET remainderlist = trim(both from remainderlist);
    if length(remainderlist) > 0 then
        insert into tblTmpSplit values (remainderlist);
    end if;

END$$
DELIMITER ;

您现在可以这样调用它:

SET @allcategories = (SELECT GROUP_CONCAT(category separator ', ') FROM table_db);

drop table if exists tbltmpsplit;
create temporary table tbltmpsplit(items varchar(2000));

call fnsplit(@allcategories, ', ');

SELECT *, Count(*) FROM tbltmpsplit GROUP BY items having count(*) >= 1;

drop table if exists tbltmpsplit;

这将返回:

CAR 2
GAME    3
SPORT   1
TRAVEL  3