现在有3张表
表A 账单表 : 账单信息 商户代码
表B 商户表: 商户ID 商户父ID 商户名称 商户代码 (有上下级关系,比如中国联通-北京联通-海淀区联通)
表C 商户人员表: 商户ID 人员ID有这么一个需求,根据人员ID,查询出商户包括下级商户的所有账单信息.
我考虑是这么操作:
1/根据人员ID,查询出商户ID,包括下级商户的所有ID,商户代码.2/根据商户代码查询出账单信息.
对于第一步,我如何通过一个sql语句,能够循环查出所有的下级ID.
表A 账单表 : 账单信息 商户代码
表B 商户表: 商户ID 商户父ID 商户名称 商户代码 (有上下级关系,比如中国联通-北京联通-海淀区联通)
表C 商户人员表: 商户ID 人员ID有这么一个需求,根据人员ID,查询出商户包括下级商户的所有账单信息.
我考虑是这么操作:
1/根据人员ID,查询出商户ID,包括下级商户的所有ID,商户代码.2/根据商户代码查询出账单信息.
对于第一步,我如何通过一个sql语句,能够循环查出所有的下级ID.
解决方案 »
- 清除日志
- oracle 没有dba权限,如何杀掉死锁的进程。
- ORACLE 改表名索引是否失效?
- 问一个棘手的问题
- oracle关系数据库转多维数据库
- 存储过程utl_file的问题,请高手解决
- 求救,一个sqlserver中运行的正常的更新语句在oracle中运行报错“sql命令未正确结束”
- >>>>>用toad进行导出时遇到的问题
- 请问在enterprise manage configuration assistant 里的用户名,密码和服务器应该怎么添
- oracle9客户端连接oracle816:tns 出错:
- 从网络地址加载文件到本地的批处理命令怎么写
- SQL查询LIST排序的问题,求助!
connect by
--Hierarchical Queries
--START WITH and CONNECT BY PRIOR clauses.SELECT employee_id, manager_id, first_name, last_name
FROM employee_jh
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 0 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn13 rows selected.--2.Using a Subquery in a START WITH Clause
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' || last_name AS employee
FROM employee_jh
START WITH employee_id = (SELECT employee_id FROM employee_jh WHERE first_name = 'Kevin' AND last_name = 'Black')
CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn--3.Including Other Conditions in a Hierarchical Query
SELECT LEVEL,
LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
last_name AS employee, salary
FROM employee_jh
WHERE salary <= 50000
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id; LEVEL EMPLOYEE SALARY
---------- ------------------------- ----------
3 Rob Green 40000
3 Jane Brown 45000
4 Henry Heyson 30000
3 John Grey 30000
4 Jean Blue 29000
3 Keith Long 50000
3 Frank Howard 45000
3 Doreen Penn 470008 rows selected.
CREATE TABLE employee_jh (
employee_id INTEGER,
manager_id INTEGER,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0)
);insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 1 , 0 , 'James' , 'Smith' ,'CEO' ,800000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 2 , 1 , 'Ron' , 'Johnson','Sales Manager' ,600000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 3 , 2 , 'Fred' , 'Hobbs' ,'Sales Person' ,200000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 4 , 1 , 'Susan' , 'Jones' ,'Support Manager',500000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 5 , 2 , 'Rob' , 'Green' ,'Sales Person' ,40000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 6 , 4 , 'Jane' , 'Brown' ,'Support Person' ,45000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 7 , 4 , 'John' , 'Grey' ,'Support Manager',30000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 8 , 7 , 'Jean' , 'Blue' ,'Support Person' ,29000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 9 , 6 , 'Henry' , 'Heyson' ,'Support Person' ,30000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 10, 1 , 'Kevin' , 'Black' ,'Ops Manager' ,100000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 11, 10, 'Keith' , 'Long' ,'Ops Person' ,50000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 12, 10, 'Frank' , 'Howard' ,'Ops Person' ,45000);
insert into employee_jh (EMPLOYEE_ID, MANAGER_ID,FIRST_NAME,LAST_NAME,TITLE,SALARY) values( 13, 10, 'Doreen', 'Penn' ,'Ops Person' ,47000);