返回确切行数的查询

Question

我有一个表，其中存储了两个外键，实现了n:m关系。

其中一个指向一个人(subject)，另一个指向特定的项目。

现在，一个人可能拥有的物品数量是在一个不同的表中指定的，我需要一个查询，它将返回与一个人可能拥有的物品数量相同的行数。

其余的记录可以用NULL值或其他值填充。

事实证明，从应用程序端解决这个问题很痛苦，所以我决定尝试一种不同的方法。

编辑:示例

CREATE TABLE subject_items
(
  sub_item integer NOT NULL,
  sal_subject integer NOT NULL,
  CONSTRAINT pkey PRIMARY KEY (sub_item, sal_subject),
  CONSTRAINT fk1 FOREIGN KEY (sal_subject)
      REFERENCES subject (sub_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk2 FOREIGN KEY (sub_item)
      REFERENCES item (item_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

我需要一个查询/函数，它将返回所有主题项目(主题可能有5个项目)，但只有3个项目分配给主题。

返回值可能类似于：

sub_item   |  sal_subject
2          |   1
3          |   1
4          |   1
NULL       |   1
NULL       |   1

我使用的是postgresql-8.3

Answer 1

考虑一下您的plpgsql函数的主要简化的版本。应在PostgreSQL 8.3中工作

CREATE OR REPLACE FUNCTION x.fnk_abonemento_nariai(_prm_item integer)
  RETURNS SETOF subject_items AS
$BODY$
DECLARE
    _kiek    integer :=  num_records    -- get number at declaration time
                         FROM subjekto_abonementai WHERE num_id = _prm_item;
    _counter integer;
BEGIN

RETURN QUERY                            -- get the records that actualy exist
SELECT sub_item, sal_subject
FROM   sal_subject 
WHERE  sub_item = prm_item;

GET DIAGNOSTICS _counter = ROW_COUNT;   -- save number of returned rows.

RETURN QUERY
SELECT NULL, NULL                       -- fill the rest with null values
FROM   generate_series(_counter + 1, _kiek);

END;
$BODY$ LANGUAGE plpgsql VOLATILE STRICT;

有关plpgsql in the manual的详细信息(指向8.3版的链接)。

Answer 2

可以这样工作( SQL )：

SELECT a.sal_subject
     , b.sub_item
FROM  (
    SELECT generate_series(1, max_items) AS rn
         , sal_subject
    FROM   subject
    ) a
LEFT   JOIN (
    SELECT row_number() OVER (PARTITION BY sal_subject ORDER BY sub_item) AS rn
         , sal_subject
         , sub_item
    FROM   subject_items
    ) b USING (sal_subject, rn)
ORDER  BY sal_subject, rn

1. 生成每个主题的最大行数，我们称它们为理论项目。

请参阅手册，了解针对每个主题的现有项目的行号( generate_series().

• Apply a row-number )。

手册关于window functions.

• LEFT JOIN从现有项目到每个主题的理论项目。缺少的项目将用NULL填充。

除了您在问题中公开的表之外，我还假设有一列包含subject表中的最大项数：

CREATE temp TABLE subject
( sal_subject integer,     -- primary key of subject
  max_items int);          -- max. number of items

查询PostgreSQL 8.3，替换缺少的窗口函数row_number()

SELECT a.sal_subject
     , b.sub_item
FROM  (
    SELECT generate_series(1, max_items) AS rn
         , sal_subject
    FROM   subject
    ) a
LEFT   JOIN (
    SELECT rn, sal_subject, arr[rn] AS sub_item
    FROM  (
        SELECT generate_series(1, ct) rn, sal_subject, arr
        FROM  (
            SELECT s.sal_subject
                 , s.ct
                 , ARRAY(
                        SELECT sub_item
                        FROM   subject_items s0
                        WHERE  s0.sal_subject = s.sal_subject
                        ORDER  BY sub_item
                    ) AS arr
            FROM  (
                SELECT sal_subject
                     , count(*) AS ct
                FROM   subject_items
                GROUP  BY 1
                ) s
            ) x
        ) y
    ) b USING (sal_subject, rn)
ORDER  BY sal_subject, rn

有关在此article by Quassnoi中替换row_number()的更多信息。

Answer 3

我提出了这个简单的解决方案:首先返回我可能选择的所有值，然后循环返回空值，而我们有合适的值。如果有人遇到同样的问题，就把它贴在这里。仍然在寻找更容易/更快的解决方案，如果存在的话。

CREATE OR REPLACE FUNCTION fnk_abonemento_nariai(prm_item integer)
  RETURNS SETOF subject_items AS
$BODY$DECLARE _kiek integer;
DECLARE _rec subject_items;
DECLARE _counter integer;
BEGIN
  /*get the number of records we need*/
  SELECT INTO _kiek num_records
  FROM subjekto_abonementai
    WHERE num_id = prm_item;

  /*get the records that actualy exist */

  FOR _rec IN SELECT sub_item, sal_subject
      FROM sal_subject 
      WHERE sub_item = prm_item LOOP
    return 
      next _rec;
    _counter := COALESCE(_counter, 0) + 1;
  END LOOP;

  /*fill the rest with null values*/

  While _kiek > _counter loop
    _rec.sub_item := NULL;
    _rec.sal_subject := NULL;
    Return next _rec;
    _counter := COALESCE(_counter, 0) + 1;
  end loop;

END;$BODY$
  LANGUAGE plpgsql VOLATILE;