有一个采购表，想抓取同一供应商，同一物料在某月的最新单价

[dandanyiwang]

有一个采购表，想抓取同一供应商，同一物料在某月的最新单价
请问以下代码要如何修改？谢谢！

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)

[ws_hgo]

看不到请提供表结构
测试信息及其结果

[iqlife]

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 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 desc

C2.PK_NO 在哪里???而且外层的WHERE no = 1 很少让人疑惑不解,,,,,,

[YY_MM_DD]

--运行下看看
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)

[47522341]

select acc_code,stk_c,sales_price
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

[47522341]

这种问题用分析函数解决较为高效，楼主可以看一下over开窗函数的用法

[dandanyiwang]

感谢各位的热心解答。
由于我没有给出表的结构，及测试数据，相对来说会让热心的解答者有些模糊。
背景：这个问题是来源于一个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

[iqlife]

查出同一供应商，同一物料在某月的单价和最后一次下采购单的单价
里的某月单价30如何取出来的?

[iqlife]

SELECT a2.acc_code, a2.stk_c, a2.sales_price
  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)

试试