Informix一对多格式问题 [英] Informix one to many format issue

问题描述

尝试从一对多关系中修复我的Informix查询结果格式.我当前的查询使用的是JOIN，但每次与JOIN ON条件匹配时都会创建一个新行.我应该在下面添加一个示例，实际数据是成千上万个条目，其中约有100个唯一的类别"条目，因此我无法对WHERE语句进行硬编码，它需要读取每个条目并在匹配时添加.我尝试了GROUP_CONCAT，但是只是返回了一个错误，猜想它不是一个notifyix函数，我也尝试读取此线程，但仍无法正常工作. 显示一个一对多关系作为2列-1个唯一行(ID和逗号分隔列表)

Trying to fix my Informix query results format from a one to many relationship. My current query is using a JOIN but is creating a new line for every time there is a match to the JOIN ON condition. I should add the below is only an example, the real data is thousands of entries with about a 100 unique "category" entries so I cant hard code WHERE statements, it needs to read each entry and add if a match. I tried a GROUP_CONCAT however is just returned an error, guess its not a informix function, I also tried reading this thread but have yet been unable to get working. Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

任何帮助将不胜感激.

• IBM/Informix-Connect版本3.70.UC4
• IBM/Informix LIBGLS库5.00.UC5版
• IBM Informix Dynamic Server版本11.70.FC8W1

表格

电影

name    rating  movie_id
rio g   1   
horton  g   2
blade   r   3
lotr_1  pg13    4
lotr_2  pg13    5
paul_blart  pg  6

类别

cat_name    id
kids    1
comedy  2
action  3
fantasy 4

category_member

category_member

movie_name  cat_name    catmem_id
lotr_1 action 1
lotr_1 fantasy 2
rio kids 3
rio comedy  4

当我使用

#!/bin/bash

echo "SET isolation dirty read;
UNLOAD to /export/home/movie/movieDetail.unl DELIMITER ','
    SELECT a.name, a.rating, b.cat_name
      FROM movie a
      LEFT JOIN category b ON b.movie_name = a.name
;" | dbaccess thedb;

我得到的是

rio,g,kids
rio,g,comedy
lotr_1,pg13,action
lotr_1,pg13,fantasy

我想要的是

rio,g,kids,comedy
lotr_1,pg13,action,fantasy

推荐答案

安装GROUP_CONCAT用户定义的聚合

您必须将来自 SO 715350 (问题中引用)的GROUP_CONCAT用户定义的聚合安装到数据库中. GROUP_CONCAT聚合不是由Informix定义的，但是如果您使用该问题中的SQL，则可以添加该聚合.此功能与常规内置功能之间的区别是，您需要在需要使用聚合的服务器中的每个数据库中安装聚合.可能有一种方法(针对给定服务器中的所有数据库)进行全局安装"，但我已经忘记了(或更准确地说，是从没学过)如何做.

Install the GROUP_CONCAT user-defined aggregate

You must install the GROUP_CONCAT user-defined aggregate from SO 715350 (referenced in your question) into your database. The GROUP_CONCAT aggregate is not defined by Informix, but can be added if you use the SQL from that question. One difference between that and a normal built-in function is that you need to install the aggregate in each database in the server where you need to use it. There might be a way to do a 'global install' (for all databases in a given server), but I've forgotten (or, more accurately, never learned) how to do it.

示例数据库在底部列出:

With the sample database listed at the bottom:

1. 问题中的查询无法运行:

1. The query in the question does not run:

SELECT a.name, a.rating, b.cat_name
  FROM movie a
  LEFT JOIN category b ON b.movie_name = a.name;
SQL -217: Column (movie_name) not found in any table in the query (or SLV is undefined).

这可以通过将category更改为category_member来解决.这将产生:

This can be fixed by changing category to category_member. This produces:

SELECT a.name, a.rating, b.cat_name
  FROM movie a
  LEFT JOIN category_member b ON b.movie_name = a.name;
rio           g       kids
rio           g       comedy
horton        g
blade         r
lotr_1        pg13    action
lotr_1        pg13    fantasy
lotr_2        pg13
paul_blart    pg

左联接似乎是多余的.并使用GROUP_CONCAT产生大致所需的答案:

The LEFT JOIN appears to be unwanted. And using GROUP_CONCAT produces approximately the desired answer:

SELECT a.name, a.rating, GROUP_CONCAT(b.cat_name)
  FROM movie a
  JOIN category_member b ON b.movie_name = a.name
 GROUP BY a.name, a.rating;
rio          g       kids,comedy
lotr_1       pg13    action,fantasy

如果将分隔符指定为<​​c6>，则将转义来自GROUP_CONCAT运算符的数据中的逗号，以避免产生歧义:

If you specify the delimiter as ,, the commas in the data from the GROUP_CONCAT operator will be escaped to avoid ambiguity:

SELECT a.NAME, a.rating, GROUP_CONCAT(b.cat_name)
  FROM movie a
  JOIN category_member b ON b.movie_name = a.NAME
 GROUP BY a.NAME, a.rating;
rio,g,kids\,comedy
lotr_1,pg13,action\,fantasy

在标准的Informix实用程序中，没有办法避免这种情况.它们不会以模糊的格式保留所选/卸载的数据.

Within standard Informix utilities, there isn't a way to avoid that; they don't leave the selected/unloaded data in an ambiguous format.

我不认为数据库架构的组织性很好.电影表正常；类别表正常；但是，如果使用以下架构，则Category_Member表将更加传统:

I'm not convinced that the database schema is very well organized. The Movie table is OK; the Category table is OK; but the Category_Member table would be more orthodox if it used the schema:

DROP TABLE IF EXISTS category_member;
CREATE TABLE category_member
(
    movie_id INTEGER NOT NULL REFERENCES Movie(Movie_id),
    category_id INTEGER NOT NULL REFERENCES Category(Id),
    PRIMARY KEY(movie_id, category_id)
);
INSERT INTO category_member VALUES(4, 3);
INSERT INTO category_member VALUES(4, 4);
INSERT INTO category_member VALUES(1, 1);
INSERT INTO category_member VALUES(1, 2);

-- Use GROUP_CONCAT
SELECT a.NAME, a.rating, GROUP_CONCAT(c.cat_name)
  FROM movie a
  JOIN category_member b ON b.movie_id = a.movie_id
  JOIN category c ON b.category_id = c.id
 GROUP BY a.NAME, a.rating;

此查询的输出与上一个查询的输出相同，但是连接的查询更为正统.

The output from this query is the same as from the previous one, but the joining is more orthodox.

样本数据库

DROP TABLE IF EXISTS movie;
CREATE TABLE movie
(
    name VARCHAR(20) NOT NULL UNIQUE,
    rating CHAR(4) NOT NULL,
    movie_id SERIAL NOT NULL PRIMARY KEY
);
INSERT INTO movie VALUES("rio", "g", 1);
INSERT INTO movie VALUES("horton", "g", 2);
INSERT INTO movie VALUES("blade", "r", 3);
INSERT INTO movie VALUES("lotr_1", "pg13", 4);
INSERT INTO movie VALUES("lotr_2", "pg13", 5);
INSERT INTO movie VALUES("paul_blart", "pg", 6);

DROP TABLE IF EXISTS category;
CREATE TABLE category
(
    cat_name VARCHAR(10) NOT NULL UNIQUE,
    id SERIAL NOT NULL PRIMARY KEY
);
INSERT INTO category VALUES("kids", 1);
INSERT INTO category VALUES("comedy", 2);
INSERT INTO category VALUES("action", 3);
INSERT INTO category VALUES("fantasy", 4);

DROP TABLE IF EXISTS category_member;
CREATE TABLE category_member
(
    movie_name VARCHAR(20) NOT NULL,
    cat_name VARCHAR(10) NOT NULL,
    catmem_id SERIAL NOT NULL PRIMARY KEY
);
INSERT INTO category_member VALUES("lotr_1", "action", 1);
INSERT INTO category_member VALUES("lotr_1", "fantasy", 2);
INSERT INTO category_member VALUES("rio", "kids", 3);
INSERT INTO category_member VALUES("rio", "comedy", 4);

这篇关于Informix一对多格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！