多个子查询错误"标识符无效"

Question

我正在处理这段代码，但我一直得到t2.nabp_num 的无效标识符

`with t1 as (query1),
t2 as (query2)
Select t1.*, t2.device_count, 
d.* from t1 
inner join t2 on
t1.nabp_num = t2.nabp_num and
t1.dt = t2.dt and
t1.d_member = t2.d_member
inner join drug_product d on
t1.d_product_id = d.product_id
order by claim_count desc;`

我得到无效的标识符

Answer 1

如前所述，您不能引用不存在的内容。

如果你发布了实际的查询而不是无效的，这肯定会有所帮助

with t1 as (query1          --> what is "query1"?
Order by ...

---

无论如何：此处引用NABP_NUM：

inner join t2 on t1.nabp_num = t2.nabp_num

这意味着它必须是CCD_ 2和CCD_。然而，由于t2的CTE结果是从t1导出的，也许您根本不需要t1。。。

若添加select列列表或group by子句中当前缺少的所有列，则查询将如下所示(请参阅代码中的注释(：

WITH
t1
AS
(SELECT d_member_id,
dt,
device_type,
claim_id,
nabp_num,          --> add NABP_NUM
d_member_hq_id     --> add D_MEMBER_HQ_ID
d_drug_product_id  --> add D_DRUG_PRODUCT_ID
FROM some_table         --> which table?
),       --> remove ORDER BY, it is useless here
t2
AS
(  SELECT d_member_id,
dt,
nabp_num,        --> add NABP_NUM
d_member_hq_id,  --> add D_MEMBER_HQ_ID
COUNT (DISTINCT device_type) AS device_count,
COUNT (DISTINCT claim_ID) AS claim_count
FROM t1
GROUP BY d_member_id, dt, nabp_num, d_member_hq_id)   --> add NABP_NUM and D_MEMBER_HQ_ID
SELECT t1.*, t2.device_count, d.*
FROM t1
INNER JOIN t2
ON     t1.nabp_num = t2.nabp_num
AND t1.dt = t2.dt
AND t1.d_member_hq_id = t2.d_member_hq_id
INNER JOIN vmd_drug_product d
ON t1.d_drug_product_id = d.d_drug_product_id
ORDER BY t2.claim_count DESC;

尽管这不应该再返回任何语法错误(假设这里使用的列确实存在于some_table中(，但我无法判断这是否会返回所需的结果。