问题描述
嗨，康纳/克里斯，
您能否看一下下面的场景并帮助构建SQL:
我需要根据SELECT中的列值生成重复行
要执行的SQL是:
基于SAL & COMM列值，我需要重复同一行:
例如，如果SAL = “1” 且COMM = null，则empno 7839行应在最终SQL输出标志中生成一次，该标志应为APPLY_SAL。
如果SAL = “1” 和COMM = “1”，则empno 7934行应在最终SQL输出标志中生成两次，该标志应为APPLY_SAL & APPLY_COMM。
如果SAL = null且COMM = “1”，则empno 1234行应在最终SQL输出标志中生成一次，该标志应为APPLY_COMM。
empno 7782行应被忽略，因为noth SAL & COMM为空
您能否看一下下面的场景并帮助构建SQL:
我需要根据SELECT中的列值生成重复行
要执行的SQL是:
select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm
from emp
, dept
where dept.deptno = 10
and dept.deptno = emp.deptno;
And its output is:
EMPNO ENAME HIREDATE SAL COMM
----- ------ --------- ---------- ----------
7782 CLARK 09-JUN-81 (null) (null)
7839 KING 17-NOV-81 1 (null)
7934 MILLER 23-JAN-82 1 1
1234 STYCK 09-JUN-81 (null) 1
基于SAL & COMM列值，我需要重复同一行:
例如，如果SAL = “1” 且COMM = null，则empno 7839行应在最终SQL输出标志中生成一次，该标志应为APPLY_SAL。
如果SAL = “1” 和COMM = “1”，则empno 7934行应在最终SQL输出标志中生成两次，该标志应为APPLY_SAL & APPLY_COMM。
如果SAL = null且COMM = “1”，则empno 1234行应在最终SQL输出标志中生成一次，该标志应为APPLY_COMM。
EMPNO ENAME HIREDATE FLAG ----- ------ --------- ---------- 7839 KING 17-NOV-81 APPLY_SAL 7934 MILLER 23-JAN-82 APPLY_SAL 7934 MILLER 23-JAN-82 APPLY_COMM 1234 STYCK 09-JUN-81 APPLY_COMM
empno 7782行应被忽略，因为noth SAL & COMM为空
---------- Sample table structure and dummy data -------------
drop table dept purge;
drop table emp purge;
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
) ;
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),NULL,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,1000,10);
INSERT INTO EMP VALUES (1234,'STYCK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),NULL,1300,10);
COMMIT;
专家解答
如果我理解你的要求，这应该就足够了
或者你可以在最近的版本中享受一些乐趣和横向游戏
SQL> select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, 'APPLY_SAL'
2 from emp
3 where sal is not null
4 union all
5 select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, 'APPLY_COMM'
6 from emp
7 where emp.comm is not null
8 order by 1;
EMPNO ENAME HIREDATE SAL COMM 'APPLY_SAL
---------- ---------- --------- ---------- ---------- ----------
7369 SMITH 17-DEC-80 800 APPLY_SAL
7499 ALLEN 20-FEB-81 1600 300 APPLY_COMM
7499 ALLEN 20-FEB-81 1600 300 APPLY_SAL
7521 WARD 22-FEB-81 1250 500 APPLY_SAL
7521 WARD 22-FEB-81 1250 500 APPLY_COMM
7566 JONES 02-APR-81 2975 APPLY_SAL
7654 MARTIN 28-SEP-81 1250 1400 APPLY_SAL
7654 MARTIN 28-SEP-81 1250 1400 APPLY_COMM
7698 BLAKE 01-MAY-81 2850 APPLY_SAL
7782 CLARK 09-JUN-81 2450 APPLY_SAL
7788 SCOTT 09-DEC-82 3000 APPLY_SAL
7839 KING 17-NOV-81 5000 APPLY_SAL
7844 TURNER 08-SEP-81 1500 APPLY_SAL
7876 ADAMS 12-JAN-83 1100 APPLY_SAL
7900 JAMES 03-DEC-81 950 APPLY_SAL
7902 FORD 03-DEC-81 3000 APPLY_SAL
7934 MILLER 23-JAN-82 1300 APPLY_SAL
17 rows selected.
或者你可以在最近的版本中享受一些乐趣和横向游戏
SQL> select emp.empno, emp.ename, emp.hiredate, emp.sal, emp.comm, decode(x,1,'APPLY_SAL','APPLY_COMM') tag
2 from emp,
3 lateral
4 ( select level x from dual connect by level <= nvl2(emp.comm,2,1) )
5 where sal is not null;
EMPNO ENAME HIREDATE SAL COMM TAG
---------- ---------- --------- ---------- ---------- ----------
7369 SMITH 17-DEC-80 800 APPLY_SAL
7499 ALLEN 20-FEB-81 1600 300 APPLY_SAL
7499 ALLEN 20-FEB-81 1600 300 APPLY_COMM
7521 WARD 22-FEB-81 1250 500 APPLY_SAL
7521 WARD 22-FEB-81 1250 500 APPLY_COMM
7566 JONES 02-APR-81 2975 APPLY_SAL
7654 MARTIN 28-SEP-81 1250 1400 APPLY_SAL
7654 MARTIN 28-SEP-81 1250 1400 APPLY_COMM
7698 BLAKE 01-MAY-81 2850 APPLY_SAL
7782 CLARK 09-JUN-81 2450 APPLY_SAL
7788 SCOTT 09-DEC-82 3000 APPLY_SAL
7839 KING 17-NOV-81 5000 APPLY_SAL
7844 TURNER 08-SEP-81 1500 APPLY_SAL
7876 ADAMS 12-JAN-83 1100 APPLY_SAL
7900 JAMES 03-DEC-81 950 APPLY_SAL
7902 FORD 03-DEC-81 3000 APPLY_SAL
7934 MILLER 23-JAN-82 1300 APPLY_SAL
17 rows selected.
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
