PL/SQL编程之道之CASE使用(19)

思路
先得到各个每个报亭各个杂志的平均销售数量
SELECT STAND_NBR,
       AVG(CASE
             WHEN MAGAZINE_SKU = 2667 THEN
              NET_SOLD_QTY
           END) AVG_2667,
       AVG(CASE
             WHEN MAGAZINE_SKU = 48632 THEN
              NET_SOLD_QTY
           END) AVG_48632,
       AVG(CASE
             WHEN MAGAZINE_SKU = 1107 THEN
              NET_SOLD_QTY
           END) AVG_1107
  FROM SALES, TITLES
 WHERE SALES.PRODUCT_ID = TITLES.PRODUCT_ID
 GROUP BY STAND_NBR


 

STAND_NBR    AVG_2667    AVG_48632    AVG_1107
1                3            1          1
2                              1          5.5
4                1            3          1
3                3            3          1
然后在按照条件筛选
 
 


SELECT STAND_NBR
  FROM (SELECT STAND_NBR,
               AVG(CASE
                     WHEN MAGAZINE_SKU = 2667 THEN
                      NET_SOLD_QTY
                   END) AVG_2667,
               AVG(CASE
                     WHEN MAGAZINE_SKU = 48632 THEN
                      NET_SOLD_QTY
                   END) AVG_48632,
               AVG(CASE
                     WHEN MAGAZINE_SKU = 1107 THEN
                      NET_SOLD_QTY
                   END) AVG_1107
          FROM SALES, TITLES
         WHERE SALES.PRODUCT_ID = TITLES.PRODUCT_ID
         GROUP BY STAND_NBR)
 WHERE AVG_1107 > 5
    OR (AVG_2667 > 2 AND AVG_48632 > 2) STAND_NBR

2
3

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

转载于:http://blog.itpub.net/24558043/viewspace-676931/