select C.num_buy,C.cod_fact,f.nam_fact,C.cod_paym,D.content
from buym c,CODD D,fact f
where c.cod_paym<>
(select cod_paym from
(select A.NUM_BUY,A.COD_FACT,A.COD_PAYM,A.DAT_BUY FROM BUYM A LEFT JOIN (SELECT COD_FACT,MAX(DAT_BUY) DAT_BUY,max(num_buy) num_buy FROM BUYM where dat_buy <=(select to_char(last_day(add_months(sysdate, -1)),'yyyyMMdd') from dual)GROUP BY COD_FACT) B ON B.DAT_BUY=A.DAT_BUY AND B.COD_FACT=A.COD_FACT WHERE B.DAT_BUY=A.DAT_BUY AND B.COD_FACT=A.COD_FACT and b.num_buy=a.num_buy) t where c.cod_fact=t.cod_fact) and c.dat_buy >(select to_char(last_day(add_months(sysdate, -1)),'yyyyMMdd') from dual)
and C.cod_paym=d.code
and c.cod_fact=f.cod_fact这个sql的写成收到了csdn上朋友们的热情帮助,现在有新的问题想再请大家帮忙了
在第3行和第4行有作比较,现在查询结果是显示的本月的付款条件(cod_paym)我想把与之比较的原付款条件也显示出来
还有总觉得这个sql蛮复杂的,不知道能不能改改优化一下呵呵,请朋友们帮忙了,谢谢!
解决方案 »
- 求救:access连接oracle,不能显示中文!
- 那位有oracle8和oracle9的安装包
- 这语句该怎么(最迟明早结贴)
- 大家帮我看看这个存储过程是哪里的错误吧,我找了一个晚上了,我快昏了,谢谢各位了!!
- 6位数字转换成4位
- 如何建立只读用户
- 关于select语句中写提示加快查询速度的疑惑
- 文本文件添加到oracle中除了sqlldr和utl_file,就没有其他的方法了吗?
- 各位如何分发Oracle客户端连接,有没有MS SQL Server里那样的 *.MSM
- ORACLE 一个比较复杂的查询语句,一直处于执行, 我要怎么优化才能让它正常执行出结果呢?
- 急 !!!!!!!!!!
- 大数据表备份方案求教
--上月日期部分不用from dual,直接可用select C.num_buy, C.cod_fact, f.nam_fact, C.cod_paym, D.content
from buym c, CODD D, fact f
where c.cod_paym <>
(select cod_paym
from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
FROM BUYM A
LEFT JOIN (SELECT COD_FACT,
MAX(DAT_BUY) DAT_BUY,
max(num_buy) num_buy
FROM BUYM
where dat_buy <=
to_char(last_day(add_months(sysdate, -1)),
'yyyyMMdd')
GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
WHERE B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
and b.num_buy = a.num_buy) t
where c.cod_fact = t.cod_fact)
and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
and C.cod_paym = d.code
and c.cod_fact = f.cod_fact;
你还不如把表结构发给我们,重新给你写!
CLS_GET NOT NULL VARCHAR2(1)
NUM_BUY NOT NULL VARCHAR2(12)
SER_BUY VARCHAR2(2)
DAT_BUY VARCHAR2(8)
COD_FACT VARCHAR2(8)
NUM_FACT VARCHAR2(20)
COD_PAYM VARCHAR2(3)
CLS_DOLLA VARCHAR2(3)
COD_EMPBUY VARCHAR2(8)
COD_XFER VARCHAR2(2)
COD_ADDR VARCHAR2(4)
ADR_DEL VARCHAR2(200)
DAT_REQ VARCHAR2(8)
NAM_ATTN VARCHAR2(30)
NUM_TEL VARCHAR2(25)
NUM_FAX VARCHAR2(25)
AMT_TOT NUMBER(16,4)
MNY_DSC NUMBER(16,2)
MNY_TRN NUMBER(16,2)
MNY_INSU NUMBER(16,2)
MNY_PROC NUMBER(16,2)
MNY_OTHS NUMBER(16,2)
AMT_BUY NUMBER(16,4)
COD_CONF VARCHAR2(8)
REMARK VARCHAR2(2000)
COD_EMPIN VARCHAR2(8)
COD_EMPCLS VARCHAR2(8)
DAT_CLOSE VARCHAR2(8)
COD_BUYC VARCHAR2(2)
STS_BUY VARCHAR2(2)
COD_NEED VARCHAR2(8)
NUM_ORD VARCHAR2(30)
ROT_ORD VARCHAR2(3)
SOR_BUY VARCHAR2(1)
INS_ALL VARCHAR2(1)
COD_QUT VARCHAR2(3)
DSC_DELV VARCHAR2(2000)
NUM_EDSM VARCHAR2(12)
NUM_EDIR VARCHAR2(10)
DAT_POR VARCHAR2(8)
COD_POR VARCHAR2(2)
DSP_POR VARCHAR2(50)
DSC_REJ VARCHAR2(100)
NUM_PS VARCHAR2(12)
NUM_CNTL VARCHAR2(16)
TAX_TYPE VARCHAR2(1)
DAT_CONF VARCHAR2(8)
SER_PCS VARCHAR2(12)
SND_FROM VARCHAR2(5)
SND_TO VARCHAR2(5)
COD_REQ VARCHAR2(3)
DAT_KEYIN VARCHAR2(8)
TIM_KEYIN VARCHAR2(8)
EMP_LAST VARCHAR2(8)
DAT_LAST VARCHAR2(8)
TYP_BUYM VARCHAR2(3)
REMARK2 VARCHAR2(2000)
SED_CUST VARCHAR2(8)
COD_PAYMT VARCHAR2(4)
DPT_CTL VARCHAR2(8)
OX_CODBUY VARCHAR2(1)
OX_AREA VARCHAR2(8)
OX_PAYDAYS VARCHAR2(10)
OX_UPDATED_DATE DATE
OX_OPT_DB_LOC VARCHAR2(30)
DSC_CLOSE VARCHAR2(100)
TYP_CLOSE VARCHAR2(2)
OX_FLOW_FLAG VARCHAR2(1)
COD_EMP_BUYER VARCHAR2(8)
F1 VARCHAR2(30)
F2 VARCHAR2(30)
F3 VARCHAR2(30)
COD_EMPSOURCER VARCHAR2(8)
OX_COD_MULTI VARCHAR2(8)
LIN_MULTI VARCHAR2(3)
OX_NUM_MULTI VARCHAR2(50)
NUM_PO VARCHAR2(16)
OX_EXEC_MULTI VARCHAR2(1)
OX_CROSSFAB VARCHAR2(1)
OX_NUM_CONT VARCHAR2(20)
OX_NUM_VDBK VARCHAR2(16)
OX_NUM_CROSS VARCHAR2(20)
OX_DAT_CROSS VARCHAR2(8)
OX_SDAT_CROSS VARCHAR2(8)
OX_NUM_APPLY VARCHAR2(20)
Name Null? Type
CODE_ID NOT NULL VARCHAR2(10)
CODE NOT NULL VARCHAR2(20)
CONTENT VARCHAR2(2000)
CVALUE VARCHAR2(100)
EDIT_USER VARCHAR2(1)
CVALUE2 VARCHAR2(100)
CVALUE3 VARCHAR2(20)
OX_OPT_DB_LOC VARCHAR2(8)
OX_UPDATED_TIME VARCHAR2(6)
OX_AREA VARCHAR2(8)
NUM_CNTL VARCHAR2(16)
DPT_CTL VARCHAR2(8)
OX_CODEGRP VARCHAR2(20)
OX_UPDATED_DATE DATE
OX_CODECLS VARCHAR2(10)
OX_DATEND VARCHAR2(8)
CVALUE4 VARCHAR2(20)
CVALUE5 VARCHAR2(20)
CVALUE6 VARCHAR2(20)
CVALUE7 VARCHAR2(20)
CVALUE8 VARCHAR2(20)
select t.num_buy,
t.cod_fact,
f.nam_fact,
t.acod_paym,
t.bcod_paym,
D.content
from (select a.cod_paym acod_paym,
a.cod_fact,
a.num_buy,
b.cod_paym bcod_paym,
row_number() over(order by(a.dat_buy - b.dat_buy)) rn
from buym a, buym b
where a.cod_paym <> b.cod_paym
and substr(a.dat_buy, 1, 6) = to_char(sysdate, 'yyyymm')
and substr(b.dat_buy, 1, 6) =
to_char(add_months(sysdate, -1), 'yyyymm')) t,
CODD D,
fact f
where t.cod_paym = d.code
and t.cod_fact = f.cod_fact
and t.rn = 1;
運行后有如下提示
ORA-00904: "T"."COD_PAYM": invalid identifier
(select t.cod_paym oldpaym
from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
FROM BUYM A
LEFT JOIN (SELECT COD_FACT,
MAX(DAT_BUY) DAT_BUY,
max(num_buy) num_buy
FROM BUYM
where dat_buy <=
to_char(last_day(add_months(sysdate, -1)),
'yyyyMMdd')
GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
WHERE B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
and b.num_buy = a.num_buy) t ,buym c ,codd e
where c.cod_fact = t.cod_fact
and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
and c.cod_paym<>t.cod_paym
and t.cod_paym = e.code)oldpaym , (select e.content
from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
FROM BUYM A
LEFT JOIN (SELECT COD_FACT,
MAX(DAT_BUY) DAT_BUY,
max(num_buy) num_buy
FROM BUYM
where dat_buy <=
to_char(last_day(add_months(sysdate, -1)),
'yyyyMMdd')
GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
WHERE B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
and b.num_buy = a.num_buy) t ,buym c ,codd e
where c.cod_fact = t.cod_fact
and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
and c.cod_paym<>t.cod_paym
and t.cod_paym = e.code) oldcontent from buym c, CODD D, fact f
where c.cod_paym <>
(select cod_paym
from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
FROM BUYM A
LEFT JOIN (SELECT COD_FACT,
MAX(DAT_BUY) DAT_BUY,
max(num_buy) num_buy
FROM BUYM
where dat_buy <=
to_char(last_day(add_months(sysdate, -1)),
'yyyyMMdd')
GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
WHERE B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
and b.num_buy = a.num_buy) t
where c.cod_fact = t.cod_fact)
and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
and C.cod_paym = d.code
and c.cod_fact = f.cod_fact;
难道
(select t.cod_paym oldpaym
from (select A.NUM_BUY, A.COD_FACT, A.COD_PAYM, A.DAT_BUY
FROM BUYM A
LEFT JOIN (SELECT COD_FACT,
MAX(DAT_BUY) DAT_BUY,
max(num_buy) num_buy
FROM BUYM
where dat_buy <=
to_char(last_day(add_months(sysdate, -1)),
'yyyyMMdd')
GROUP BY COD_FACT) B ON B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
WHERE B.DAT_BUY = A.DAT_BUY
AND B.COD_FACT = A.COD_FACT
and b.num_buy = a.num_buy) t ,buym c ,codd e
where c.cod_fact = t.cod_fact
and c.dat_buy > to_char(last_day(add_months(sysdate, -1)), 'yyyyMMdd')
and c.cod_paym<>t.cod_paym
and t.cod_paym = e.code)oldpaym ,这样的子查询返回单行值?
你那个拼接起来的太复杂了。
我给修改了下,有问题再说。关键这没测试数据
select t.num_buy,
t.cod_fact,
f.nam_fact,
t.cod_paym,
t.bcod_paym,
D.content
from (select a.cod_paym cod_paym,
a.cod_fact,
a.num_buy,
b.cod_paym bcod_paym,
row_number() over(order by(a.dat_buy - b.dat_buy)) rn
from buym a, buym b
where a.cod_paym <> b.cod_paym
and substr(a.dat_buy, 1, 6) = to_char(sysdate, 'yyyymm')
and substr(b.dat_buy, 1, 6) =
to_char(add_months(sysdate, -1), 'yyyymm')) t,
CODD D,
fact f
where t.cod_paym = d.code
and t.cod_fact = f.cod_fact
and t.rn = 1;
SELECT
A.COD_FACT,
A.DAT_BUY,
A.NUM_BUY,
A.COD_PAYM,E.CONTENT,
B.COD_PAYM,F.CONTENT,
FROM BUYM A
JOIN CODD E ON a.COD_PAYM=E.CODE
LEFT JOIN
(SELECT COD_FACT,COD_PAYM,
row_number() over(PARTITION BY COD_FACT ORDER BY DAT_BUY DESC,NUM_BUY DESC) AS recent_paym
FROM BUYM
WHERE DAT_BUY<=TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'yyyyMMdd')
) B ON a.COD_FACT=B.COD_FACT AND b.recent_paym=1
LEFT JOIN CODD F ON B.COD_PAYM=F.CODE
WHERE a.DAT_BUY>TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'yyyyMMdd')
AND (a.COD_PAYM<>b.COD_PAYM OR b.COD_PAYM IS NULL)
您好,謝謝你的熱情回覆。
你最後給我的sql可以執行了,但查到的數據有些問題,bcod_paym查到的數據不正確,這個我需要同廠商cod_fact這個月之前最近日期的采購單num_buy中的cod_paym,另外我也需要它對應的content付款條件名稱 codd是個代碼表,code就是代碼,content就是代碼對應的名稱
再麻煩大家了,不勝感激!
我的思路就是两个日期相减,求日期相减最小值的那行,其实主要修改以下SQL就可以。楼主要么搞些测试数据要么自己好好研究下我的思路。
[code=SQL]select a.cod_paym cod_paym,
a.cod_fact,
a.num_buy,
b.cod_paym bcod_paym,
row_number() over(order by(a.dat_buy - b.dat_buy)) rn
from buym a, buym b
where a.cod_paym <> b.cod_paym
and substr(a.dat_buy, 1, 6) = to_char(sysdate, 'yyyymm')
and substr(b.dat_buy, 1, 6) =
to_char(add_months(sysdate, -1), 'yyyymm'))code]
非常感謝二位的幫助。sql我再改了一點,我驗證了一下前幾個月的資料應該是可以了。SELECT
A.COD_FACT,
G.nam_fact,
A.DAT_BUY,
A.NUM_BUY,
A.COD_PAYM,E.CONTENT,
B.COD_PAYM,F.CONTENT
FROM BUYM A
JOIN CODD E ON a.COD_PAYM=E.CODE
join fact G on a.cod_fact = G.cod_fact --這邊我加了一句應該沒有問題吧?LEFT JOIN
(SELECT COD_FACT,COD_PAYM,
row_number() over(PARTITION BY COD_FACT ORDER BY DAT_BUY DESC,NUM_BUY DESC) AS recent_paym
FROM BUYM
WHERE DAT_BUY <=TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)),'yyyyMMdd')
) B ON a.COD_FACT=B.COD_FACT AND b.recent_paym=1
LEFT JOIN CODD F ON B.COD_PAYM=F.CODE
WHERE a.DAT_BUY>TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)),'yyyyMMdd')
AND (a.COD_PAYM <>b.COD_PAYM OR b.COD_PAYM IS NULL)
and b.cod_paym is not null
AND E.CODE_ID='OX_TL'
AND F.CODE_ID='OX_TL'
A.COD_FACT,
A.DAT_BUY,
A.NUM_BUY,
A.COD_PAYM,E.CONTENT,
B.COD_PAYM,F.CONTENT,
FROM BUYM A
JOIN CODD E ON a.COD_PAYM=E.CODE
LEFT JOIN
(SELECT COD_FACT,COD_PAYM, /*选择每一个供应商及付款方式,后面是该供应商按对其付款的日期倒排名(如果同一天有两笔定单,那么定购量最大的那一笔排在最前)=>*/ row_number() over(PARTITION BY COD_FACT ORDER BY DAT_BUY DESC,NUM_BUY DESC) AS recent_paym
FROM BUYM
WHERE DAT_BUY <=TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'yyyyMMdd') /*参与排名的是指定月份之前的供应商及付款*/) B ON a.COD_FACT=B.COD_FACT AND b.recent_paym=1 /*同一供应商取最后一笔定单*/LEFT JOIN CODD F ON B.COD_PAYM=F.CODE
WHERE a.DAT_BUY>TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'yyyyMMdd')
/*指定月份之后的供应商及付款*/
AND (a.COD_PAYM <>b.COD_PAYM OR b.COD_PAYM IS NULL) /*指定月份后的定单和该月份前的最后一笔定单付款方式不一样,或者之前没有发生过定单的*/