postgresql - Postgresql 加入空值

Question

我有查询困难。我的表格和数据：

CREATE SEQUENCE a_files_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."a_files" (
    "id" integer DEFAULT nextval('a_files_id_seq') NOT NULL,
    "filename" character varying NOT NULL,
    "category_id" integer NOT NULL,
    "available_id" integer
) WITH (oids = false);

INSERT INTO "a_files" ("id", "filename", "category_id", "available_id") VALUES
(1, 'aa.jpg',   1,  NULL),
(2, 'bb.jpg',   1,  1);

CREATE SEQUENCE files_log_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."a_files_log" (
    "id" integer DEFAULT nextval('files_log_id_seq') NOT NULL,
    "user_id" integer NOT NULL,
    "file_id" integer NOT NULL,
    "created_at" timestamp NOT NULL
) WITH (oids = false);

INSERT INTO "a_files_log" ("id", "user_id", "file_id", "created_at") VALUES
(1, 100,    1,  '2021-03-14 17:04:34.068'),
(2, 100,    1,  '2021-03-14 17:04:46.176338'),
(3, 100,    2,  '2021-03-14 17:05:16.633936'),
(4, 100,    2,  '2021-03-14 17:05:30.279555');

CREATE SEQUENCE a_parameters_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1;

CREATE TABLE "public"."a_parameters" (
    "id" integer DEFAULT nextval('a_parameters_id_seq') NOT NULL,
    "door_id" integer NOT NULL,
    "category_id" integer NOT NULL,
    "available_id" integer NOT NULL
) WITH (oids = false);

INSERT INTO "a_parameters" ("id", "door_id", "category_id", "available_id") VALUES
(1, 1,  1,  1),
(2, 1,  1,  2),
(3, 1,  1,  3),
(4, 1,  2,  1),
(5, 1,  2,  2);

视觉表：

我的查询：

SELECT f.filename as filename, fl.user_id AS user_id, count(fl.*) as count_views, max(fl.created_at) as last_view
FROM a_files_log fl
JOIN a_files f ON f.id = fl.file_id
JOIN a_parameters p ON 
   CASE WHEN f.available_id IS NOT NULL 
   THEN   p.category_id = f.category_id AND p.available_id = f.available_id
     ELSE   p.category_id = f.category_id
   END
WHERE p.door_id = 1
GROUP BY filename, user_id
ORDER BY count_views DESC

我得到这样的记录：

预期结果：

当a_files 表中的available_id 为NULL 时，如何建立结果与我希望的一样的关系？

标签： postgresqljoinnull

Answer 1

你需要得到不同的计数：

SELECT
    f.filename as filename,
    fl.user_id AS user_id,
    count(DISTINCT fl.*) as count_views,
    max(fl.created_at) as last_view
FROM
    a_files_log fl
JOIN a_files f 
    ON f.id = fl.file_id
JOIN a_parameters p 
    ON CASE WHEN f.available_id IS NOT NULL THEN p.category_id = f.category_id
    AND p.available_id = f.available_id ELSE p.category_id = f.category_id END
WHERE
    p.door_id = 1
GROUP BY
    filename,
    user_id
ORDER BY
    count_views DESC