本帖最后由 钱程远 于 2022-9-12 14:42 编辑
plsql里面可以执行，在永洪里面报错，是不是有语法不兼容？with all_data as (
SELECT
distinct
trunc(t.tj_date,'yyyy') tj_date
,t.clientfullname
,t.nw_type
FROM dm_szh.v_dm_user_own_nw_num t
where to_char(t.tj_date,'yyyy') = '2022'
and to_char(t.tj_date,'mm') >= '01'
and to_char(t.tj_date,'mm') <= '03'
)
,nm_and_wm as (
SELECT tj_date,clientfullname,count(1) FROM all_data
group by tj_date,clientfullname
having count(1)>1
)
,wm as (
SELECT tj_date,clientfullname FROM all_data
where nw_type='外贸'
and NVL(clientfullname,' ') NOT IN (SELECT NVL(clientfullname,' ') FROM nm_and_wm)
)
,nm as (
SELECT tj_date,clientfullname FROM all_data
where nw_type='内贸'
and NVL(clientfullname,' ') NOT IN (SELECT NVL(clientfullname,' ') FROM nm_and_wm)
)
SELECT
'内外贸' AS nw_type --贸易类型
,COUNT(1) AS CLIENT_NUM --客户数
FROM nm_and_wm
union
SELECT
'外贸' AS nw_type --贸易类型
,COUNT(1) AS CLIENT_NUM --客户数
FROM wm
union
SELECT
'内贸' AS nw_type --贸易类型
,COUNT(1) AS CLIENT_NUM --客户数
FROM nm
|
