时间戳

首页 > 解决方案 > postgresql - 使用大小写检查表之间是否存在关系并存在

sql - postgresql - 使用大小写检查表之间是否存在关系并存在

问题描述

这是我的package桌子

+----+--------------+---------------+
| id | order_number | purchase_date |
+----+--------------+---------------+
|  1 | P1           | 11-22-2019    |
|  2 | P2           | 11-21-2019    |
|  3 | P3           | 11-20-2019    |
|  4 | P4           | 11-17-2019    |
|  5 | P5           | 11-21-2019    |
+----+--------------+---------------+

这是通过表连接到tags表的object_tagsobject_idpackage.id

        object_tags                 tags

+----+-----------+--------+    +----+---------+
| id | object_id | tag_id |    | id |  name   |
+----+-----------+--------+    +----+---------+
|  1 |         2 |      1 |    |  1 | special |
|  2 |         3 |      1 |    |  2 | normal  |
+----+-----------+--------+    +----+---------+

special如果包裹有标签special或者purchase_date从今天起超过 3 天或更长时间，则可以将包裹视为包裹。这就是输出应该是什么：

+------------+--------------+--------+
| package_id | order_number | is_vip |
+------------+--------------+--------+
|          1 | P1           |      0 |
|          2 | P2           |     -1 |
|          3 | P3           |     -1 |
|          4 | P4           |     -1 |
|          5 | P5           |      0 |
+------------+--------------+--------+

现在这是我尝试过的：

  SELECT packages.id as package_id,
    packages.order_number as order_number,
    (CASE
      WHEN EXISTS(SELECT p.id as id
                  FROM packages p
                  INNER JOIN object_tags ot on ot.object_id = p.id
                  INNER JOIN tags t on t.id = ot.tag_id
                  WHERE tag.name = 'special'
                  )vip on packages.id = vip.id THEN -1 /*(PG::SyntaxError: ERROR:  syntax error at or near "vip"*/
      WHEN p.purchased_at NOT BETWEEN NOW() AND NOW() - interval '3 days' then -1
      ELSE 0 END) as is_vip
  FROM packages p

我不太确定 CASE - EXISTS 部分。任何帮助，将不胜感激。

标签： sqlpostgresql

解决方案

  WITH special_packages
    AS (
         SELECT package.id
           FROM package, object_tags, tags
          WHERE package.id = object_tags.object_id
            AND tags.id = object_tags.tag_id
            AND tags.name = 'special'
       )
SELECT package.id AS package_id,
       package.order_number,
       CASE
         WHEN package.purchase_date < NOW() - interval '3 days' THEN -1
         WHEN special_packages.id IS NOT NULL THEN -1
         ELSE 0
       END AS is_vip
  FROM package
  LEFT JOIN special_packages USING (id)

这是一个SQL Fiddle。如果您的数据库不支持 CTE，那么您可以special_packages进入子查询：

SELECT ...
  FROM package
  LEFT JOIN ( ... ) AS special_packages USING (id)

推荐阅读