前端之家

  1. 首页
  2. 问答

在长多表查询中无法直接计数时，有什么方法可以在has子句中使用COUNT变量

• 问答

我一直遇到这个逻辑错误，我需要获取计数数据大于0的行。我已经尝试过

  

“拥有c> 0”

但返回错误

  

'ORA-00904：“ C”：无效的标识符”

以下是我一直在尝试为我提供结果的查询。

SELECT accOUNTS.accOUNT_NO,(Select count(*) as cc from SALE where sale.account_no=accounts.account_no and DATED >= add_months(sysdate,-6) ) as c,(select LISTAGG(v.TYPE_NAME,',') WITHIN GROUP (ORDER BY v.TYPE_NAME) AS deals  from  DEALSIN v  where account_no=accounts.account_no ) as deals FROM accOUNTS,DetaIL_accOUNTS,CITY WHERE accOUNTS.accOUNT_NO = DetaIL_accOUNTS.accOUNT_NO AND DetaIL_accOUNTS.CITY = REGISTRATION_NO AND PARENT_CODE = 111 AND STATUS = 'A' AND CITY =:CITY_Name having c>0 order by c desc

我想要输出为

accOUNT_NO  C   DEALS
1           10  deal1,deal2
2           9   deal1,deal3
3           3   deal4

但是我得到的是

accOUNT_NO  C   DEALS
1           10  deal1,deal3
3           3   deal4
4           0   deal  ----> DON'T WANT THIS ROW
5           0   deal0 ----> DON'T WANT THIS ROW

我不希望这一行的count = 0。

meng0827 回答：在长多表查询中无法直接计数时，有什么方法可以在has子句中使用COUNT变量

我建议改写您的查询，以消除select子句中的那些内联相关子查询。而是左联接以SALE和DEALSIN表上的单独子查询。

SELECT
    a.ACCOUNT_NO,COALESCE(s.cc,0) AS c,COALESCE(v.deals,'NA') AS deals,FROM ACCOUNTS a
LEFT JOIN
(
    SELECT account_no,COUNT(*) AS cc
    FROM SALE
    WHERE DATED >= ADD_MONTHS(SYSDATE,-6)
    GROUP BY account_no
) s
    ON s.account_no = a.account_no
LEFT JOIN
(
    SELECT account_no,LISTAGG(v.TYPE_NAME,',') WITHIN GROUP (ORDER BY v.TYPE_NAME) AS deals
    FROM DEALSIN
    GROUP BY account_no
) v
    ON v.account_no = a.account_no
INNER JOIN DETAIL_ACCOUNTS da
    ON a.ACCOUNT_NO = da.ACCOUNT_NO
INNER JOIN CITY c
    ON da.CITY = c.REGISTRATION_NO
WHERE
    PARENT_CODE = 111 AND
    STATUS = 'A' AND
    CITY =:CITY_Name AND
    COALESCE(s.cc,0) > 0   -- your previous HAVING clause now appears in WHERE
ORDER BY
    c DESC;
c#oracle-sqldeveloper
本文链接：https://www.f2er.com/3167957.html

大家都在问