查询返回准确的行数

Question

我有一个表存储两个外键，实现：m 关系。

其中一个指向一个人（主题），另一个指向一个特定项目。
现在，一个人可能拥有的物品数量在另一个表中指定，我需要一个查询，该查询将返回与一个人可能拥有的物品数量相同的行数。

其余记录可能会填充 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 的详细信息（链接到版本 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()。
2. 将行号应用于每个主题的现有项目。
   关于窗口函数的手册。
3. 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

有关在此 row_number() 的更多信息href="http://explainextended.com/2009/05/11/postgresql-emulated-row_number/" rel="nofollow">Quassnoi 的文章。

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;