关于SQL，请高手

wdswcy 2008-08-23 02:45:15



免费表  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

...全文

159 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

wdswcy 2008-08-24

打赏
举报

三个表都是可以增加减少的

fxianxian 2008-08-24

打赏
举报

[Quote=引用 2 楼 wdswcy 的回复:]
谢谢fxianxian

SQL code
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…
[/Quote]

兄弟你的B表在实际应用中可以加数据吗?

oracledbalgtu 2008-08-23

打赏
举报

你这200分可要兑换承诺哦！！！！
呵呵.
我给你的方法可以解决多级部门嵌套，你可以试试。

[Quote=引用 3 楼 wdswcy 的回复:]
http://topic.csdn.net/u/20080823/12/3afd5c1a-74de-44ab-9535-e68e42dc65a9.html
这个是同样的问题
如果能解决，给200分
[/Quote]

oracledbalgtu 2008-08-23

打赏
举报



在一个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

[Quote=引用楼主 wdswcy 的帖子:]
SQL code
免费表 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 …
[/Quote]

wdswcy 2008-08-23

打赏
举报

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

gxlineji 2008-08-23

打赏
举报

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条件就可以得到想要的结果了

wdswcy 2008-08-23

打赏
举报

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

wdswcy 2008-08-23

打赏
举报

谢谢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，出不来啊

fxianxian 2008-08-23

打赏
举报

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>