一个多表查询的SQL语句向大家请教一下.
我写的SQL查出的invoice_id重复,结果是想得到不重复的invoice_id.
先贴出我的SQL
----------------------------------------------------------------------------------
SELECT a.invoice_id, a.invoice_num, a.invoice_date, a.org_num, a.bizunit_key,
a.costcentre_key, a.invoice_amt, a.settled_amt, a.status, b.ABINVOICEITEM_ID, b.item_desc, c.reg_name
FROM ABINVOICE a,
ABINVOICEITEM b,
TMORG c,
TMORGPROFILEBRANCH d,
TMORGBRANCH e
WHERE a.invoice_id = b.invoice_id
AND a.org_num = d.pb_id AND d.branch_id = e.branch_id AND e.org_id = c.org_id order by a.invoice_id
-------------------------------------------------------------------------------------
这样做就会有重复,因为a表中一个invoice_id在b表中对应多条item_desc 要求只取第一条item_desc就行.从而让invoice_id不重复.
有什么好方法修改上边的语句,实现这样的呢? 请大侠们帮忙!!!! 多谢了先! 分比较少,还请帮忙.
我写的SQL查出的invoice_id重复,结果是想得到不重复的invoice_id.
先贴出我的SQL
----------------------------------------------------------------------------------
SELECT a.invoice_id, a.invoice_num, a.invoice_date, a.org_num, a.bizunit_key,
a.costcentre_key, a.invoice_amt, a.settled_amt, a.status, b.ABINVOICEITEM_ID, b.item_desc, c.reg_name
FROM ABINVOICE a,
ABINVOICEITEM b,
TMORG c,
TMORGPROFILEBRANCH d,
TMORGBRANCH e
WHERE a.invoice_id = b.invoice_id
AND a.org_num = d.pb_id AND d.branch_id = e.branch_id AND e.org_id = c.org_id order by a.invoice_id
-------------------------------------------------------------------------------------
这样做就会有重复,因为a表中一个invoice_id在b表中对应多条item_desc 要求只取第一条item_desc就行.从而让invoice_id不重复.
有什么好方法修改上边的语句,实现这样的呢? 请大侠们帮忙!!!! 多谢了先! 分比较少,还请帮忙.
(
SELECT a.invoice_id, a.invoice_num, a.invoice_date, a.org_num, a.bizunit_key,
a.costcentre_key, a.invoice_amt, a.settled_amt, a.status, b.ABINVOICEITEM_ID, b.item_desc, c.reg_name
FROM ABINVOICE a,
ABINVOICEITEM b,
TMORG c,
TMORGPROFILEBRANCH d,
TMORGBRANCH e ,
row_number() over (patition py a.invoice_id order by a.invoice_id) row_numberWHERE a.invoice_id = b.invoice_id
AND a.org_num = d.pb_id AND d.branch_id = e.branch_id AND e.org_id = c.org_id order by a.invoice_id
)a
where a.row_number = 1
(
SELECT a.invoice_id, a.invoice_num, a.invoice_date, a.org_num, a.bizunit_key,
a.costcentre_key, a.invoice_amt, a.settled_amt, a.status, b.ABINVOICEITEM_ID, b.item_desc, c.reg_name,
row_number() over (patition py a.invoice_id order by a.invoice_id) row_number
FROM ABINVOICE a,
ABINVOICEITEM b,
TMORG c,
TMORGPROFILEBRANCH d,
TMORGBRANCH e
WHERE a.invoice_id = b.invoice_id
AND a.org_num = d.pb_id AND d.branch_id = e.branch_id AND e.org_id = c.org_id order by a.invoice_id
)a
where a.row_number = 11楼的那个row_number()位置写错了.....
呵呵
(
SELECT a.invoice_id, a.invoice_num, a.invoice_date, a.org_num, a.bizunit_key,
a.costcentre_key, a.invoice_amt, a.settled_amt, a.status, b.ABINVOICEITEM_ID, b.item_desc, c.reg_name,
row_number() over (partition by a.invoice_id order by a.invoice_id) row_number
FROM ABINVOICE a,
ABINVOICEITEM b,
TMORG c,
TMORGPROFILEBRANCH d,
TMORGBRANCH e
WHERE a.invoice_id = b.invoice_id
AND a.org_num = d.pb_id AND d.branch_id = e.branch_id AND e.org_id = c.org_id order by a.invoice_id
)a
where a.row_number = 1 这样OK了多谢两位!
如果是取每个item_desc的第一条记录,需要使用RANK() OVER(PARTITION BY "invoice_id"order by by invoice_id asc) ranknum
这样的分组语句,然后跟a表关联加上条件ranknum=1就可以了
a.costcentre_key, a.invoice_amt, a.settled_amt, a.status, b.ABINVOICEITEM_ID, b.item_desc, c.reg_name
FROM ABINVOICE a inner join
(select invoice_id,ABINVOICEITEM_ID,item_desc,
RANK() OVER(PARTITION BY invoice_id order by invoice_id asc) ranknum
from ABINVOICEITEM) b on a.invoice_id = b.invoice_id
inner join TMORGPROFILEBRANCH d on a.org_num = d.pb_id
inner join TMORGBRANCH e on d.branch_id = e.branch_id
inner join TMORG c on e.org_id = c.org_id
WHERE b.ranknum=1
order by a.invoice_id