主页 / 问题 / 745074
Accepted
ilyaplot
Asked:2020-11-15 21:05:31 +0000 UTC2020-11-15 21:05:31 +0000 UTC

如何在一个查询中按日期从另一个表中选择记录数？

  • 772

我正在尝试从一个表中选择分组数据并从另一个表中添加记录数。

表结构是这样的：

t1:
  id
  created_at (timestamp)
  price (double)

t2:
  id
  created_at (timestamp)
  type (enum(1,2))

我正在尝试提出这样的请求：

select
    date(t1.created_at) as date,
    t1.price,
    (select count(*) from t2 t21 where date(t1.created_at) = date(t21.created_at) and type = 1) as cnt1,
    (select count(*) from t2 t22 where date(t1.created_at) = date(t22.created_at) and type = 2) as cnt2
from t1
group by date, t1.price

我收到一个错误subquery uses ungrouped column "t1.created_at" from outer query

如何按日期从另一个表中选择计数？

postgresql

1 个回答

  1. Best Answer
    SELECT date(t1.created_at) as date,
       t1.price,
       SUM(CASE WHEN type = 1 THEN 1 END) count1,
       SUM(CASE WHEN type = 2 THEN 1 END) count2
FROM t1
LEFT /* или INNER */ JOIN t2 ON date(t1.created_at) = date(t2.created_at)
group by date(t1.created_at), t1.price

    更新：之前的请求不正确。

    SELECT t11.date, 
       t11.price,
       COALESCE(SUM(CASE WHEN t2.type = 1 THEN 1 END)) count1,
       COALESCE(SUM(CASE WHEN t2.type = 2 THEN 1 END)) count2
FROM ( SELECT DISTINCT date(t1.created_at) as date,
                       t1.price as price
       FROM t1
     ) t11
LEFT /* или INNER */ JOIN t2 ON t11.date = date(t2.created_at)
group by t11.date, t11.price
    • 2

相关问题