這是個sybase的問題。發在oracle板塊只是因為sybase區好像一個月沒新帖了，問題又比較急......
資料庫版本：
select @@version;
SAP IQ/16.0.0.809/151113/P/sp08.39/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2015-11-13 12:24:54
sybase的語法實在不清楚如何構造測驗資料，甲方又不讓建表。所以測驗資料以oracle格式列出，請見諒。
要求，使用一條查詢sql完成。
我有一張存有日賬期的表，它們本應是連續的，但是有可能存在空擋。我需要把這些空缺的賬期明細查出來。oracle的話一般會構造笛卡爾積做外關聯來完成。
就像這樣：
with tab as (
select 'a' typ, 20180601 dt from dual union all
select 'a' typ, 20180602 dt from dual union all
select 'a' typ, 20180603 dt from dual union all
select 'b' typ, 20180601 dt from dual union all
select 'b' typ, 20180602 dt from dual union all
select 'b' typ, 20180603 dt from dual union all
select 'c' typ, 20180601 dt from dual union all
select 'c' typ, 20180603 dt from dual
),
tab2 as (
select to_char(to_date('20180601', 'yyyymmdd') + level - 1, 'yyyymmdd') full_dt from dual connect by level <=3
),
tab3 as (
select * from tab2, (select distinct typ from tab)
),
tab4 as (
select t1.full_dt, t1.typ, t2.dt from tab3 t1, tab t2
where t1.typ = t2.typ(+)
and t1.full_dt = t2.dt(+)
)
select t1.full_dt, typ from tab4 t1
where t1.dt is null
;
但是我唯一找到的sybase的動態構造連續日期的方法，用于子查詢會報錯。
select * from (
select top 6 NUMBER(*) num,
convert(char(10), dateadd(mm, -NUMBER(*), cast('2018/06/14' as datetime)) , 112) acct_month
from systable
);
with tab as (
select top 6 NUMBER(*) num,
convert(char(10), dateadd(mm, -NUMBER(*), cast('2018/06/14' as datetime)) , 112) acct_month
from systable
)
select * from tab;
我的問題是要怎么構造這個笛卡爾積呢？或者用別的方法也行。
真實的需求是：資料庫中某個用戶下存有
TABLE_A_20180601;
TABLE_A_20180602;
TABLE_B_20180601;
TABLE_A_201806;
TABLE_A_201805;
......
這樣的嚴格命名規范的表，我要查出TABLE_A_M，TABLE_A_D，TABLE_B_D等每個邏輯表所缺失的賬期的明細。（M是月表，D是日表）。統計賬期區間可以寫死。
寫了個這樣的sql，一直報錯......
--3缺失賬期
with tab as (
select t1.table_name,
reverse(substring(reverse(t1.table_name), 0, charindex('_', reverse(t1.table_name)) )) acct_month,
reverse(substring(reverse(t1.table_name), charindex('_', reverse(t1.table_name)) + 1, 999 )) || case length(substring(reverse(t1.table_name), 0, charindex('_', reverse(t1.table_name)) )) when 6 then '_M' when 8 then '_D' else '' end logic_table,
case length(substring(reverse(t1.table_name), 0, charindex('_', reverse(t1.table_name)) )) when 6 then 'M' when 8 then 'D' else '' end date_type
from (
select a.table_name from systable a,sysuserperm b
where a.creator=b.user_id
and b.user_name = 'A_USER'
) t1
),
tab2 as (
select * from tab t1
where 1 = 1
and t1.date_type is not null
and ((t1.date_type = 'M' and t1.acct_month > 201711 and t1.acct_month <= 201806) or (t1.date_type = 'D' and t1.acct_month > 20180401 and t1.acct_month <= 20180614))
),
table_day as (
select top 74 NUMBER(*) num,
convert(char,cast('2018/06/15' as datetime) - number(*),112) acct_day
from systable
),
table_month as (
select top 6 NUMBER(*) num,
convert(char(10), dateadd(mm, -NUMBER(*), cast('2018/06/14' as datetime)) , 112) acct_month
from systable
),
tab3 as (
select distinct t1.logic_table, t1.date_type from tab2 t1
)
,tab4 as (
select *
from tab3 t1, table_day t2
where t1.date_type = 'D'
)
, tab5 as (
select t1.logic_table, t2.table_name, t1.acct_day from tab4 t1 left join tab2 t2
on (t1.logic_table || '_' || t1.acct_day = t2.table_name)
)
select * from tab5 t1
where t1.table_name = '' or t1.table_name is null or t1.table_name = null
;
日，月缺失明細分成兩個sql寫也行。大家有什么好方法么？
uj5u.com熱心網友回復：
不懂 sybase ，頂一下貼子。PS：那邊的確有好長時間沒有新貼子，先在這里放著吧。等有人給你解答了，我再給你移過去。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/65074.html
標籤：開發
下一篇：oracle遞回查詢問題