在具有多个 INNER JOINS 的 SELECT 查询中使用 Count()。找不到正确的输出

Question

这是我的查询：

select distinct 
    p.*,
    d.tech_id, 
    d.id, 
    prio.priority 
from
    data.curated_data s
inner join
    data.curated_details p on p.curated_scheme = s.curated_scheme 
                           and p.curated_category = s.curated_category
inner join
    data1.data_defn d on d.service_id = p.service_id
inner join 
    data1.data_priority prio on prio.ingest_data = d.ingest_data
                             and p.curated_scheme = %s
                             and s.manually_curated_flag = 'Y'
                             and s.autosuggest_kind = %s
limit %s
offset %s

这就是我试图做的：

select count(*) 
from ( select distinct p.*,
              d.tech_id, 
              d.id, 
              prio.priority 
       from data.curated_data s
       inner join data.curated_details p on p.curated_scheme = s.curated_scheme and p.curated_category = s.curated_category
       inner join data1.data_defn d on d.service_id = p.service_id
       inner join data1.data_priority prio on prio.ingest_data = d.ingest_data
       and p.curated_scheme = %s
       and s.manually_curated_flag = 'Y'
       and s.autosuggest_kind = %s
       limit %s
       offset %s
      )

但是，这会导致错误

错误代码：1248。每个派生表必须有自己的别名

为了处理这个问题，我向 count select 查询添加了别名，但随后它返回了错误的输出 1，而表中有大约 500 行输出表。

Answer 1

尝试在派生表|子查询的末尾使用别名

select count(*) from(select distinct p.*,d.tech_id, d.id, prio.priority from data.curated_data s
                INNER JOIN data.curated_details p on p.curated_scheme = s.curated_scheme and p.curated_category = s.curated_category
                INNER JOIN data1.data_defn d on d.service_id = p.service_id
                inner join data1.data_priority prio
                on prio.ingest_data = d.ingest_data
                and  p.curated_scheme = %s
                and s.manually_curated_flag = 'Y'
                and s.autosuggest_kind = %s
                limit %s
                offset %s) as temp  ---- here