企业表(table_qy)
qyid qymc
1 a企业
2 b企业产品表(table_cp)
cpid cpmc qyid
1 a产品 1
2 b产品 1
3 c产品 2通过sql
select a.qymc,b.cpmc from table_qy left join table_cp on a.qyid=b.qyid
查询结果:
qymc cpmc
a企业 a产品
a企业 b产品
b企业 c产品如何得到这样的结果:
qymc cpmc
a企业 a产品
b产品
b企业 c产品
即相同的qymc就null显示
qyid qymc
1 a企业
2 b企业产品表(table_cp)
cpid cpmc qyid
1 a产品 1
2 b产品 1
3 c产品 2通过sql
select a.qymc,b.cpmc from table_qy left join table_cp on a.qyid=b.qyid
查询结果:
qymc cpmc
a企业 a产品
a企业 b产品
b企业 c产品如何得到这样的结果:
qymc cpmc
a企业 a产品
b产品
b企业 c产品
即相同的qymc就null显示
with tmp as
(
select 'a' a, 1 b from dual union all
select 'a' a, 2 b from dual union all
select 'a' a, 3 b from dual union all
select 'b' a, 4 b from dual union all
select 'b' a, 5 b from dual)
select decode(a, a2, null, a), b
from (
select lag(a,1) over (order by a) a2, a, b
from tmp
);DECODE(A,A2,NULL,A) B
------------------- ----------
a 1
2
3
b 4
5
with table_qy as
(
select 1 qyid, 'a企业' qymc from dual union all
select 2 qyid, 'b企业' qymc from dual
),
table_cp as
(
select 1 cpid, 'a产品' cpmc, 1 qyid from dual union all
select 2 cpid, 'b产品' cpmc, 1 qyid from dual union all
select 3 cpid, 'c产品' cpmc, 2 qyid from dual
)
select decode(qymc,qymc2, null, qymc) qymc, cpmc
from (
select lag(a.qymc,1) over (order by a.qyid) qymc2, a.qymc, b.cpmc
from table_qy a left join table_cp b on a.qyid=b.qyid
) ;
QYMC CPMC
---------- ----------
a企业 a产品
b产品
b企业 c产品
SQL> select deptno,empno,ename,sal
2 from emp order by deptno; DEPTNO EMPNO ENAME SAL
---------- ---------- ---------- ----------
10 7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
20 7566 JONES 2975
7902 FORD 3000
7876 ADAMS 1100
7369 SMITH 800
7788 SCOTT 3000
30 7521 WARD 1250
7844 TURNER 1500
7499 ALLEN 1600
7900 JAMES 950
7698 BLAKE 2850
7654 MARTIN 1250
(
select '1' qyid , 'a企业' qymc from dual union all
select '2' , 'b企业' from dual
)
,
table_cp AS(
select '1' cpid , 'a产品' cpmc,'1' qyid from dual union all
select '2' , 'b产品','1' from dual union all
select '3' , 'c产品','2' from dual
)
SELECT Decode(qymc,qqq,null,qymc),cpmc FROM(
select a.qymc,b.cpmc,lag(a.qymc) over(ORDER BY a.qyid) qqq
from table_qy a left outer join table_cp b on a.qyid=b.qyid
)