关于按销售量报刊亭的选择1

必须满足如下条件的报亭，才能被选择：
2667和48632的平均net_sold_qty大于2（两个都大于2）   1107的平均net_sold_qty大于5
建表语句：

点击(此处)折叠或打开

1. CREATE TABLE Titles
   
2. (product_id INTEGER NOT NULL PRIMARY KEY,
   
3.  magazine_sku INTEGER NOT NULL,
   
4.  issn INTEGER NOT NULL,
   
5.  issn_year INTEGER NOT NULL);
   
6. CREATE TABLE Newsstands
   
7. (stand_nbr INTEGER NOT NULL PRIMARY KEY,
   
8.  stand_name CHAR(20) NOT NULL);
   
9. CREATE TABLE Sales
   
10. (product_id INTEGER NOT NULL REFERENCES Titles(product_id),
    
11.  stand_nbr INTEGER NOT NULL /*REFERENCES Newsstands(stand_nbr)*/,
    
12.  net_sold_qty INTEGER NOT NULL,
    
13.  PRIMARY KEY(product_id, stand_nbr));
    
14. 
    
15. INSERT INTO titles
    
16. SELECT 1,12345,1,2006 FROM dual UNION ALL
    
17. SELECT 2,2667,1,2006 FROM dual UNION ALL
    
18. SELECT 3,48632,1,2006 FROM dual UNION ALL
    
19. SELECT 4,1107,1,2006 FROM dual UNION ALL
    
20. SELECT 5,12345,2,2006 FROM dual UNION ALL
    
21. SELECT 6,2667,2,2006 FROM dual UNION ALL
    
22. SELECT 7,48632,2,2006 FROM dual UNION ALL
    
23. SELECT 8,1107,2,2006 FROM dual;
    
24. 
    
25. INSERT INTO sales
    
26. SELECT 1,1,1 FROM dual UNION ALL
    
27. SELECT 2,1,4 FROM dual UNION ALL
    
28. SELECT 3,1,1 FROM dual UNION ALL
    
29. SELECT 4,1,1 FROM dual UNION ALL
    
30. SELECT 5,1,1 FROM dual UNION ALL
    
31. SELECT 6,1,2 FROM dual UNION ALL
    
32. SELECT 7,1,1 FROM dual UNION ALL
    
33. 
    
34. SELECT 4,2,5 FROM dual UNION ALL
    
35. SELECT 8,2,6 FROM dual UNION ALL
    
36. SELECT 3,2,1 FROM dual UNION ALL
    
37. 
    
38. SELECT 1,3,1 FROM dual UNION ALL
    
39. SELECT 2,3,3 FROM dual UNION ALL
    
40. SELECT 4,3,1 FROM dual UNION ALL
    
41. SELECT 5,3,1 FROM dual UNION ALL
    
42. SELECT 6,3,3 FROM dual UNION ALL
    
43. SELECT 7,3,3 FROM dual UNION ALL
    
44. 
    
45. SELECT 1,4,1 FROM dual UNION ALL
    
46. SELECT 2,4,1 FROM dual UNION ALL
    
47. SELECT 3,4,4 FROM dual UNION ALL
    
48. SELECT 4,4,1 FROM dual UNION ALL
    
49. SELECT 5,4,1 FROM dual UNION ALL
    
50. SELECT 6,4,1 FROM dual UNION ALL
    
51. SELECT 7,4,2 FROM dual;
    
52. 
    
53. INSERT INTO Newsstands
    
54. SELECT DISTINCT stand_nbr,stand_nbr
    
55. FROM sales;

1、将行结果转换成列结果避免在where里面写多个子查询的方法：

点击(此处)折叠或打开

1. select *
   
2.   from (select B.stand_name,
   
3.                sum(nvl(B.s1, 0)) as s1,
   
4.                sum(nvl(B.s2, 0)) as s2,
   
5.                sum(nvl(B.s3, 0)) as s3
   
6.           from (select A.stand_name,
   
7.                        (case A.magazine_sku
   
8.                          when 1107 then
   
9.                           A.avg_sj
   
10.                        end) as s1,
    
11.                        (case A.magazine_sku
    
12.                          when 48632 then
    
13.                           A.avg_sj
    
14.                        end) as s2,
    
15.                        (case A.magazine_sku
    
16.                          when 2667 then
    
17.                           A.avg_sj
    
18.                        end) as s3
    
19.                   from (select ns.stand_name,
    
20.                                tt.magazine_sku,
    
21.                                avg(ss.net_sold_qty) as avg_sj
    
22.                           from sales ss, titles tt, newsstands ns
    
23.                          where ns.stand_nbr = ss.stand_nbr
    
24.                            and tt.product_id = ss.product_id
    
25.                            and tt.magazine_sku in (2667, 48632, 1107)
    
26.                          group by ns.stand_name, tt.magazine_sku
    
27.                          order by ns.stand_name) A) B
    
28.          group by B.stand_name)
    
29.  where s1 > 5
    
30.     or (s2 > 2 and s3 > 2)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29507357/viewspace-1203730/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29507357/viewspace-1203730/