时间戳

首页 > 解决方案 > 从左表中获取所有记录并从右表中匹配记录的查询是什么？

sql - 从左表中获取所有记录并从右表中匹配记录的查询是什么？

问题描述

从左表中获取所有记录并从右表中匹配记录的查询是什么？

表：

特性：

id cid property_name
1   1   Property1
2   1   Property2
3   1   Property3
4   1   Property4

属性媒体：

id cid property_id media_file_id
1   1  1           1
2   1  1           2
3   1  1           4
4   1  2           5
5   1  2           6

媒体文件

id cid media_alt
1   1  NULL
2   1  pqr
3   1  NULL
4   1  ttt
5   1  NULL
6   1  NULL

预期输出：

id  cid property_name media_alt
1   1   Property1      YES
2   1   Property2      NO
3   1   Property3       -
4   1   Property4       -

输出说明： Property1 至少有一条记录具有 media_alt IS NOT NULL，因此 media_alt 为 'YES' Property2 的所有记录为'-'。

我尝试使用此查询，但它给了我重复的属性记录。

SELECT distinct p.id, p.property_name, mf.media_alt
FROM
properties AS p
JOIN property_medias pm ON ( pm.property_id = p.id AND pm.cid = p.cid)
JOIN media_files mf ON ( mf.id = pm.media_file_id AND mf.cid = pm.cid )
WHERE
p.cid  = 1
ORDER BY p.property_name

请帮助我朝着正确的方向前进。

标签： sqlpostgresql

解决方案

您正在描述一个LEFT JOIN和GROUP BY：

SELECT p.id, p.property_name,
       (CASE WHEN COUNT(mf.media_alt) > 0 THEN 'YES'
             WHEN COUNT(mf.cid) > 0 THEN 'NO'
             ELSE '-'
        END) as media_alt
FROM properties p LEFT JOIN
     property_medias pm
     ON pm.property_id = p.id AND pm.cid = p.cid LEFT JOIN
     media_files mf
     ON mf.id = pm.media_file_id AND mf.cid = pm.cid
WHERE p.cid = 1
GROUP BY p.id, p.property_name;

第一个 COUNT()是确定是否有任何列值不是NULL。第二个是确定是否有任何匹配。

您也可以使用以下语句来表达EXISTS：

SELECT p.id, p.property_name,
       (CASE WHEN EXISTS (SELECT 1
                          FROM property_medias pm LEFT JOIN
                               media_files mf
                               ON mf.id = pm.media_file_id AND mf.cid = pm.cid
                          WHERE pm.property_id = p.id AND
                                pm.cid = p.cid AND
                                mf.media_alt IS NOT NULL
                         )
             THEN 'YES'
             WHEN EXISTS (SELECT 1
                          FROM property_medias pm 
                          WHERE pm.property_id = p.id AND
                                pm.cid = p.cid
                         )
             THEN 'NO'
             ELSE '-'
        END) as media_alt
FROM properties p        
WHERE p.cid = 1;

这节省了外部聚合，但在 Postgres 中可能没有任何性能优势。

推荐阅读