将一对多关系显示为2列-1个唯一行(ID和逗号分隔的列表) [英] Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

问题描述

我需要一些与这2个SO问题类似的东西，但要使用Informix SQL语法.

I need something similar to these 2 SO questions, but using Informix SQL syntax.

• 使用SQL将多个字段合并为一个

SQL帮助:Select语句Concatenate一对多的关系

我输入的数据如下:

id     codes

63592  PELL
58640  SUBL
58640  USBL
73571  PELL
73571  USBL
73571  SUBL

我想看到它像这样回来:

I want to see it come back like this:

id     codes 

63592  PELL
58640  SUBL, USBL
73571  PELL, USBL, SUBL

另请参阅Informix中的 group_concat().

See also group_concat() in Informix.

推荐答案

我相信您需要的答案是用户定义的汇总，类似于以下内容:

I believe that the answer you need is a user-defined aggregate, similar to this one:

CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || ',' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    IF partial1 IS NULL OR partial1 = '' THEN
        RETURN partial2;
    ELIF partial2 IS NULL OR partial2 = '' THEN
        RETURN partial1;
    ELSE
        RETURN partial1 || ',' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_comb, FINAL = gc_fini);

给出一个元素(称为元素)表，其中一个名为name的列包含(很有趣)元素名称，另一个名为atomic_number的列，此查询将产生以下结果:

Given a table of elements (called elements) with a column called name containing (funnily enough) the element name, and another column called atomic_number, this query produces this result:

SELECT group_concat(name) FROM elements WHERE atomic_number < 10;

Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine

应用于该问题，您应该从以下位置获得所需的答案:

Applied to the question, you should obtain the answer you need from:

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id;

---

CREATE TEMP TABLE anonymous_table
(
    id      INTEGER NOT NULL,
    codes   CHAR(4) NOT NULL,
    PRIMARY KEY (id, codes)
);

INSERT INTO anonymous_table VALUES(63592, 'PELL');
INSERT INTO anonymous_table VALUES(58640, 'SUBL');
INSERT INTO anonymous_table VALUES(58640, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'PELL');
INSERT INTO anonymous_table VALUES(73571, 'USBL');
INSERT INTO anonymous_table VALUES(73571, 'SUBL');
INSERT INTO anonymous_table VALUES(73572, 'USBL');
INSERT INTO anonymous_table VALUES(73572, 'PELL');
INSERT INTO anonymous_table VALUES(73572, 'SUBL');

SELECT id, group_concat(codes)
    FROM anonymous_table
    GROUP BY id
    ORDER BY id;

输出是:

58640 SUBL,USBL
63592 PELL
73571 PELL,SUBL,USBL
73572 PELL,SUBL,USBL

添加了额外的数据集以测试插入序列是否影响结果；似乎没有这样做(代码是按排序的顺序；我不确定是否有办法更改-反转-该顺序).

The extra set of data was added to test whether insert sequence affected the result; it appears not to do so (the codes are in sorted order; I'm not sure whether there's a way to alter - reverse - that order).

注意:

1. 此聚合应可用于可转换为VARCHAR(255)的任何类型，这意味着任何数字或时间类型.不处理长CHAR列和Blob类型(BYTE，TEXT，BLOB，CLOB).
2. 普通LVARCHAR将聚合大小限制为2048个字节.如果您认为需要更长的长度，例如，指定LVARCHAR(10240)(对于10 KiB).
3. 从Informix 12.10.FC5开始，可以使用的最大长度似乎是16380；似乎再有什么事情会触发SQL -528: Maximum output rowsize (32767) exceeded，这使我感到惊讶.

4. 如果需要删除聚合，可以使用:

1. This aggregate should be usable for any type that can be converted to VARCHAR(255), which means any numeric or temporal type. Long CHAR columns and blob types (BYTE, TEXT, BLOB, CLOB) are not handled.
2. The plain LVARCHAR limits the aggregate size to 2048 bytes. If you think you need longer lengths, specify LVARCHAR(10240) (for 10 KiB), for example.
3. As of Informix 12.10.FC5, the maximum length that works seems to be 16380; anything longer seems to trigger SQL -528: Maximum output rowsize (32767) exceeded, which surprises me.

4. If you need to remove the aggregate, you can use:

DROP AGGREGATE IF EXISTS group_concat;
DROP FUNCTION IF EXISTS gc_fini;
DROP FUNCTION IF EXISTS gc_init;
DROP FUNCTION IF EXISTS gc_iter;
DROP FUNCTION IF EXISTS gc_comb;

这篇关于将一对多关系显示为2列-1个唯一行(ID和逗号分隔的列表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！