17,086
社区成员
发帖
与我相关
我的任务
分享
select decode(rn,1,id,null) id ,decode(rn,1,name,null) name ,sid,qty from
(select a.id,name,sid,qty , row_number()over(partition by a.id order by a.id,sid) rn
from wuliao a,cangku b where a.id=b.id order by a.id,sid)
select decode(rn,1,id,null) id ,decode(name,1,name,null) name ,sid,qty from
(select a.id,name,sid,qty , row_number()over(partition by a.id order by a.id,sid) rn from wuliao a,cangku b where a.id=b.id order by a.id,sid)
WITH CTE
AS
(
SELECT P.ID , P.NAME,B.SID,B.QTY ,ROWNUM RM FROM PRODUCTS P ,BASE B
WHERE P.ID =B.ID
)
SELECT ID,NAME,SID,QTY FROM
(
SELECT C.ID,C.NAME,C.SID,C.QTY ,c.rm
FROM CTE C WHERE C.RM =1
UNION
SELECT CASE T.ID WHEN C.ID THEN '' ELSE C.ID END ID, CASE T.NAME WHEN C.NAME THEN '' ELSE C.NAME END NAME,C.SID,C.QTY ,c.rm
FROM CTE C , CTE T WHERE C.RM = T.RM +1
)
ORDER BY rm
CREATE TABLE products
(
ID VARCHAR2(10),
NAME VARCHAR2(20)
);
CREATE TABLE base
(
SID VARCHAR2(10),
ID VARCHAR2(10),
QTY NUMBER
);
WITH CTE
AS
(
SELECT P.ID , P.NAME,B.SID,B.QTY ,ROWNUM RM FROM PRODUCTS P ,BASE B
WHERE P.ID =B.ID
)
SELECT ID,NAME,SID,QTY FROM
(
SELECT C.ID,C.NAME,C.SID,C.QTY ,c.rm
FROM CTE C WHERE C.RM =1
UNION
SELECT CASE T.ID WHEN C.ID THEN '' ELSE C.ID END ID,C.NAME,C.SID,C.QTY ,c.rm
FROM CTE C , CTE T WHERE C.RM = T.RM +1
)
ORDER BY rm;
1 001 物料1 01 2
2 物料1 02 3
3 物料1 03 5
4 002 物料2 "01
" 1
5 物料2 02 2
6 物料2 03 4
select t1.id ,t1.name,t2.sid,t2.qty
from (select * from t_wuliao order by id asc)t1
full join
(select * from t_cangku order by id,sid asc)t2
on t1.id=t2.id and t2.sid ='01'
t1 物料表:t_wuliao
t2 仓库表:t_cangku
select t1.id ,t1.name,t2.sid,t2.qty
from t_wuliao t1 full join t_cangku t2
on t1.id=t2.id and t2.sid ='01'
with t1 as(
select '001' id, '物料1' name from dual union all
select '002', '物料2' from dual)
,t2 as(
select '01' sid, '001' id, 2 qty from dual union all
select '02', '001', 3 from dual union all
select '03', '001', 5 from dual union all
select '01', '002', 1 from dual union all
select '02', '002', 2 from dual union all
select '03', '002', 4 from dual)
select t1.id ,t1.name,t2.sid,t2.qty from t1 full join t2 on t1.id=t2.id and sid ='01'