有一个采购表，想抓取同一供应商，同一物料在某月的最新单价
请问以下代码要如何修改？谢谢！SELECT A2.acc_code, A2.stk_c, A2.sales_price
FROM SP_MAS_ITEM A2
where PK_NO =
(SELECT C2.PK_NO
from (SELECT rownum no,
mas_no,
ACC_CODE,
STK_C,
sales_price
FROM SP_MAS_ITEM C1
WHERE ORG_NO = 10
AND MAS_CODE = 'PO'
AND STATUS_FLG IN ('N', 'P')
and to_char(mas_date, 'YYYY') = '2010'
AND to_char(MAS_DATE, 'MM') = '01'
AND C1.STK_C = A2.STK_C--提示引用无效代码
AND C1.ACC_CODE = A2.acc_code--提示引用无效代码
order by mas_no desc) C2
WHERE no = 1
AND C2.STK_C = A2.STK_C
AND C2.ACC_CODE = A2.ACC_CODE)
请问以下代码要如何修改？谢谢！SELECT A2.acc_code, A2.stk_c, A2.sales_price
FROM SP_MAS_ITEM A2
where PK_NO =
(SELECT C2.PK_NO
from (SELECT rownum no,
mas_no,
ACC_CODE,
STK_C,
sales_price
FROM SP_MAS_ITEM C1
WHERE ORG_NO = 10
AND MAS_CODE = 'PO'
AND STATUS_FLG IN ('N', 'P')
and to_char(mas_date, 'YYYY') = '2010'
AND to_char(MAS_DATE, 'MM') = '01'
AND C1.STK_C = A2.STK_C--提示引用无效代码
AND C1.ACC_CODE = A2.acc_code--提示引用无效代码
order by mas_no desc) C2
WHERE no = 1
AND C2.STK_C = A2.STK_C
AND C2.ACC_CODE = A2.ACC_CODE)
FROM SP_MAS_ITEM A2
where PK_NO =
(SELECT C2.PK_NO
from (SELECT rownum no,
mas_no,
ACC_CODE,
STK_C,
sales_price
FROM SP_MAS_ITEM C1
WHERE ORG_NO = 10
AND MAS_CODE = 'PO'
AND STATUS_FLG IN ('N', 'P')
and to_char(mas_date, 'YYYY') = '2010'
AND to_char(MAS_DATE, 'MM') = '01'
AND C1.STK_C = A2.STK_C--提示引用无效代码
AND C1.ACC_CODE = A2.acc_code--提示引用无效代码
order by mas_no desc) C2
WHERE no = 1
AND C2.STK_C = A2.STK_C
AND C2.ACC_CODE = A2.ACC_CODE)
FROM SP_MAS_ITEM C1改为
FROM SP_MAS_ITEM C1,SP_MAS_ITEM A2
试试,感觉你的SQL非常的乱啊,
SELECT C2.PK_NO
from (SELECT rownum no,
mas_no,
ACC_CODE,
STK_C,
sales_price
FROM SP_MAS_ITEM C1
WHERE ORG_NO = 10
AND MAS_CODE = 'PO'
AND STATUS_FLG IN ('N', 'P')
and to_char(mas_date, 'YYYY') = '2010'
AND to_char(MAS_DATE, 'MM') = '01'
AND C1.STK_C = A2.STK_C--提示引用无效代码
AND C1.ACC_CODE = A2.acc_code--提示引用无效代码
order by mas_no descC2.PK_NO 在哪里???而且外层的WHERE no = 1 很少让人疑惑不解,,,,,,
--运行下看看
SELECT A2.acc_code, A2.stk_c, A2.sales_price
FROM SP_MAS_ITEM A2
where exist (SELECT 1
from (SELECT rownum no, mas_no, ACC_CODE, STK_C, sales_price,PK_NO
FROM SP_MAS_ITEM C1, SP_MAS_ITEM A3
WHERE ORG_NO = 10
AND MAS_CODE = 'PO'
AND STATUS_FLG IN ('N', 'P')
and to_char(mas_date, 'YYYY') = '2010'
AND to_char(MAS_DATE, 'MM') = '01'
AND C1.STK_C = A3.STK_C --提示引用无效代码
AND C1.ACC_CODE = A3.acc_code --提示引用无效代码
order by mas_no desc) C2
WHERE no = 1
AND A2.pk_no = c2.PK_NO
AND C2.STK_C = A2.STK_C
AND C2.ACC_CODE = A2.ACC_CODE)
由于我没有给出表的结构，及测试数据，相对来说会让热心的解答者有些模糊。
背景：这个问题是来源于一个ERP系统的报表查询
1.表名：sp_mas_item（视图表，其实是表头sp_mas和表身sp_item的数据组合）
字段名：ORG_NO(公司别)
PK_NO(表头的主键)
mas_no(采购单号)
acc_code(供应商代码)
stk_c(料号)
QTY(采购数量)
sales_price(采购单价)
mas_date(采购日期)
status_flg(采购单状态)
2.表数据：
org_no pk_no mas_no acc_code stk_c qty sales_price mas_date status_flg
------- ------- -------- --------- ------- ---- ------------ --------- -----------
10 490887 10010237 S0175 A0001 100 30 2010/01/07 P
10 490887 10010237 S0175 A0002 500 25 2010/01/07 P
10 635539 10010464 S0173 B0001 190 10 2010/01/15 P
10 635658 10010458 S0175 A0001 100 30 2010/01/10 P
10 671040 10010488 S0174 B0005 2000 0.35 2010/01/14 P
10 671040 10010488 S0174 B0008 1500 0.33 2010/01/14 P
10 682005 10010530 S0175 A0001 500 29 2010/01/20 P
10 680059 10010506 S0174 B0008 100 0.4 2010/01/18 P
3.希望的结果：
问题：用户希望从采购明细表里查出同一供应商，同一物料在某月的单价和最后一次下采购单的单价,
所以要取得某物料在某月的最后一次单价,需要按采购单号mas_no倒排,然后取第一笔数据.
acc_code stk_c qty sales_price last_price
--------- ----- ---- ------------ ---------
S0175 A0001 200 30 29
S0175 A0001 500 29 29
S0175 A0002 500 25 25
S0173 B0001 190 10 10
S0174 B0005 2000 0.35 0.35
S0174 B0008 1500 0.33 0.4
S0174 B0008 100 0.4 0.4
FROM sp_mas_item a2
WHERE pk_no =
(SELECT pk_no
FROM (SELECT c1.pk_no
FROM sp_mas_item c1
WHERE org_no = 10
AND mas_code = 'PO'
AND status_flg IN ('N', 'P')
AND TO_CHAR (mas_date, 'YYYY') = '2010'
AND TO_CHAR (mas_date, 'MM') = '01'
ORDER BY mas_no DESC)
WHERE ROWNUM = 1)试试
from
(
SELECT A2.acc_code, A2.stk_c, A2.sales_price,
row_number() over(partition by A2.acc_code,A2.stk_c order by PK_NO desc) r1
FROM SP_MAS_ITEM A2
) t
where r1 = 1