关于SQL，请高手

Question

免费表  a部门      免费点     产品
01         500        01
01         300        02
0101       100        01
0102        50        02注：此表中，如果某部门不存在，要用上级部门的记录，是递归向上查询的部门表 bm编号       上级部门
01           00
0101         01
0102         01
0103         01库存表 kc  id        部门      产品ID   价格
0001        0101       01     5000
0002        0102       02      60
0003        0103       02      40 
0004        0103       01     520
0005        0103       01     110
0006        01         01      120
0007        01         02      700要求用一条SQL，查询出价格在免费点以上的库存产品如：
 id        部门      产品ID   价格
0001        0101       01     5000
0002        0102       02      60
0004        0103       01     520
0007        01         02      700

Answer 1

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as bm
 
SQL> c.productid
SQL> with a as (select  '01' dept,500 free,'01' product from  dual
  2             union
  3             select  '01' dept,300 free,'02' product from  dual
  4             union
  5             select  '0101' dept,100 free,'01' product from  dual
  6             union
  7             select  '0102' dept,50 free,'02' product from  dual
  8             ),
  9       b as (select  '01' dept,'00' priordept from  dual
 10             union
 11             select  '0101' dept,'01' from  dual
 12             union
 13             select  '0102' dept,'01'  from  dual
 14             union
 15             select  '0103' dept,'01'  from  dual
 16             ),
 17      c as (select  '0001' id,'0101' dept,'01' productid,5000 price from  dual
 18            union
 19            select  '0002' id,'0102' dept,'02' productid,60 price from  dual
 20            union
 21            select  '0003' id,'0103' dept,'02' productid,40 price from  dual
 22            union
 23            select  '0004' id,'0103' dept,'01' productid,520 price from  dual
 24            union
 25            select  '0005' id,'0103' dept,'01' productid,110 price from  dual
 26            union
 27            select  '0006' id,'01' dept,'01' productid,120 price from  dual
 28            union
 29            select  '0007' id,'01' dept,'02' productid,700 price from  dual
 30            )
 31  select *
 32    from c
 33   where price > (select a.free
 34                    from a
 35                   where a.product = c.productid
 36                     and c.dept = a.dept)
 37  union
 38  select c.id, c.dept, c.productid, c.price
 39    from (select c.*
 40            from c
 41           where c.dept not in (select a.dept from a)
 42             and c.dept in (select b.dept from b)) c,
 43         b,
 44         a
 45   where c.dept = b.dept
 46     and a.dept = b.priordept
 47     and c.productid = a.product
 48     and c.price > a.free
SQL> /
 
ID   DEPT PRODUCTID      PRICE
---- ---- --------- ----------
0001 0101 01              5000
0002 0102 02                60
0004 0103 01               520
0007 01   02               700
 
SQL> 

Answer 2

谢谢fxianxian with a as (select  '01' dept,540 free,'01' product from  dual
           union
           select  '01' dept,300 free,'02' product from  dual
           union
           select  '0101' dept,100 free,'01' product from  dual
           union
           select  '0102' dept,50 free,'02' product from  dual
           union
           select  '0103' dept,566 free,'02' product from  dual           
           ),
     b as (select  '01' dept,'00' priordept from  dual
           union
           select  '0101' dept,'01' from  dual
           union
           select  '0102' dept,'01'  from  dual
           union
           select  '0103' dept,'01'  from  dual
           union
           select  '010301' dept,'0103'  from  dual
           ),
    c as (select  '0001' id,'0101' dept,'01' productid,5000 price from  dual
          union
          select  '0002' id,'0102' dept,'02' productid,60 price from  dual
          union
          select  '0003' id,'0103' dept,'02' productid,40 price from  dual
          union
          select  '0004' id,'010301' dept,'01' productid,6000 price from  dual
          union
          select  '0005' id,'0103' dept,'01' productid,110 price from  dual
          union
          select  '0006' id,'01' dept,'01' productid,120 price from  dual
          union
          select  '0007' id,'01' dept,'02' productid,700 price from  dual
          )
select *
  from c
 where price > (select a.free
                  from a
                 where a.product = c.productid
                   and c.dept = a.dept)
union
select c.id, c.dept, c.productid, c.price
  from (select c.*
          from c
         where c.dept not in (select a.dept from a)
           and c.dept in (select b.dept from b)
       ) c,
       b,
       a
 where c.dept = b.dept
   and a.dept = b.priordept
   and c.productid = a.product
   and c.price > a.free
如果部门是三层以上，你这查询也不行啊，如上：
          select  '0004' id,'010301' dept,'01' productid,6000 price from  dual
这行是应该出来的
　　但用这SQL，出不来啊

Answer 3

http://topic.csdn.net/u/20080823/12/3afd5c1a-74de-44ab-9535-e68e42dc65a9.html
这个是同样的问题
如果能解决，给200分

Answer 4

select t1.id,t1.deptno,t1.product,price from
(
   select id,
     decode(a.deptno,'',bm.prior_deptno,a.deptno) deptno,
     kc.product product,
     price 
  from kc
  left join a on a.deptno=kc.deptno
  and a.product=kc.product
  join bm on bm.deptno=kc.deptno
) t1,
a where
a.deptno=t1.dept
and a.product=t1.product
and free<price
/大概思路是~~(不知道表达清楚了没有)通过left join三个表,看看库存表(kc)中哪个部门(kc.deptno) 在免费表(a)中是没有的(a.deptno==null),
用decode(a.deptno,'',bm.prior_deptno,a.deptno) deptno 给它赋值为它的上级部门bm.prior_deptno.
将left join的结果集 取名为  t1;这样在t1中的每个deptno 都能与免费表(a)中的deptno对应起来.
这样就解决了如果某部门不存在，要用上级部门的记录的问题.之后再用一个where 连接t1和a表,再加上free<price条件  就可以得到想要的结果了

Answer 5

你的方法估计还是不行，我感觉这种问题非递归解决不可
我写了一函数，但速度很慢
http://topic.csdn.net/u/20080823/12/3afd5c1a-74de-44ab-9535-e68e42dc65a9.html

Answer 6

在一个sql中完成太复杂了；添加一个函数，简化代码，当自己没有免费点的时候用来取父部门的免费点。
DROP TABLE a;
DROP TABLE bm;
DROP TABLE kc;
CREATE TABLE a (depid VARCHAR2(10),free INT,product VARCHAR2(10));
--部门      免费点     产品;
INSERT INTO A VALUES ('01', 500, '01');
INSERT INTO A VALUES ('01', 300, '02');
INSERT INTO A VALUES ('0101', 100, '01');
INSERT INTO A VALUES ('0102', 50, '02');
--注：此表中，如果某部门不存在，要用上级部门的记录，是递归向上查询的;
--部门表 bm;
CREATE TABLE bm(deptid VARCHAR2(10),parent_id VARCHAR2(10));
--编号       上级部门;
INSERT INTO BM VALUES ('01', '00');
INSERT INTO BM VALUES ('0101', '01');
INSERT INTO BM VALUES ('0102', '01');
INSERT INTO BM VALUES ('0103', '01');
--库存表 kc ;
CREATE TABLE kc(ID VARCHAR2(10),deptid VARCHAR2(10),product VARCHAR2(10),price INT);
--id        部门      产品ID   价格;
INSERT INTO KC VALUES ('0001', '0101', '01', 5000);
INSERT INTO KC VALUES ('0002', '0102', '02', 60);
INSERT INTO KC VALUES ('0003', '0103', '02', 40);
INSERT INTO KC VALUES ('0004', '0103', '01', 520);
INSERT INTO KC VALUES ('0005', '0103', '01', 110);
INSERT INTO KC VALUES ('0006', '01', '01', 120);
INSERT INTO KC VALUES ('0007', '01', '02', 700);
COMMIT;CREATE OR REPLACE FUNCTION GET_FREE(P_DEPTID VARCHAR2, P_PRODUCT VARCHAR2)
  RETURN INTEGER IS
  V_RETURN INTEGER;
BEGIN
  WITH M AS(
    SELECT X.LL, A.PRODUCT, A.DEPID, A.FREE
      FROM A,
           (SELECT BM.*, LEVEL LL
              FROM BM
             START WITH DEPTID = P_DEPTID
            CONNECT BY PRIOR PARENT_ID = DEPTID) X
     WHERE A.DEPID = X.DEPTID
       AND A.PRODUCT = P_PRODUCT)
      SELECT FREE
        INTO V_RETURN
        FROM M
       WHERE LL = (SELECT MIN(LL) FROM M);  RETURN(V_RETURN);
END GET_FREE;
/
SELECT KC.*
  FROM KC,
       (SELECT BM.PARENT_ID, BM.DEPTID, A.FREE, A.PRODUCT
          FROM A, BM
         WHERE A.DEPID(+) = BM.DEPTID) A
 WHERE KC.PRICE > DECODE(FREE, NULL, GET_FREE(KC.DEPTID, KC.PRODUCT), FREE)
   AND KC.DEPTID = A.DEPTID
   AND KC.PRODUCT = DECODE(A.PRODUCT, NULL, KC.PRODUCT, A.PRODUCT)
/*   AND kc.deptid='0103'
   AND kc.product='01'*/
   ;输出:
1 0001 0101 01 5000
2 0002 0102 02 60
3 0004 0103 01 520
4 0007 01 02 700