如何基于表的比较值创建预言机视图

Question

我必须基于一个表创建一个预言机视图，如下所示：

<table border="1">
  <tr>
    <td>Id</td><td>store-id</td><td>prod-id</td><td>s-date</td>
  </tr>
  <tr>
    <td>1</td><td>21</td><td>2</td><td>NULL</td>
  </tr>
  <tr>
    <td>2</td><td>21</td><td>3</td><td>12-12-16</td>
  </tr>
  <tr>
    <td>3</td><td>22</td><td>2</td><td>NULL</td>
  </tr>
  <tr>
    <td>4</td><td>22</td><td>3</td><td>NULL</td>
  </tr>
  <tr>
    <td>5</td><td>23</td><td>4</td><td>12-12-16</td>
  </tr>
  <tr>
    <td>6</td><td>23</td><td>5</td><td>12-12-16</td>
  </tr>
</table>

现在在视图中，我必须添加名为 Status 的新列，该列基于 store-id 的 s 日期值，如果一个商店的所有 s 日期为空，则状态 = 0如果商店的所有 S 日期都不为空，则 starus = 2，如果商店没有所有空 s-date，则状态 =1，因此视图如下所示：

<table border="1">
  <tr>
    <td>Id</td><td>store-id</td><td>prod-id</td><td>s-date</td><td>status</td>
  </tr>
  <tr>
    <td>1</td><td>21</td><td>2</td><td>NULL</td><td>1</td>
  </tr>
  <tr>
    <td>2</td><td>21</td><td>3</td><td>12-12-16</td><td>1</td>
  </tr>
  <tr>
    <td>3</td><td>22</td><td>2</td><td>NULL</td><td>0</td>
  </tr>
  <tr>
    <td>4</td><td>22</td><td>3</td><td>NULL</td><td>0</td>
  </tr>
  <tr>
    <td>5</td><td>23</td><td>4</td><td>12-12-16</td><td>2</td>
  </tr>
  <tr>
    <td>6</td><td>23</td><td>5</td><td>12-12-16</td><td>2</td>
  </tr>
</table>

Answer 1

我已经将

前四列的数据插入到表格STORE_DATA中。

SELECT A.*, TAB1.STATUS
  FROM STORE_DATA A,
       (SELECT T.STORE_ID, COUNT(T.S_DATE) STATUS
          FROM STORE_DATA T
         GROUP BY T.STORE_ID) TAB1
 WHERE A.STORE_ID = TAB1.STORE_ID;

特定列的Count将仅返回非空值。

Answer 2

我已经在你的数据上试过了。

CREATE TABLE stores  
(  
   ID NUMBER(2),  
   store_id NUMBER(3),  
   proc_id NUMBER(3),  
   s_date DATE  
);  
INSERT INTO stores VALUES(1,21,2,NULL);
INSERT INTO stores VALUES(2,21,3,TRUNC(SYSDATE));
INSERT INTO stores VALUES(3,22,2,NULL);
INSERT INTO stores VALUES(4,22,3,NULL);
INSERT INTO stores VALUES(5,23,4,TRUNC(SYSDATE));
INSERT INTO stores VALUES(6,23,5,TRUNC(SYSDATE));  
COMMIT;  

您可以使用创建下面的视图。

CREATE VIEW stores_view  
AS  
SELECT s.id,  
       s.store_id,  
       s.proc_id,  
       s.s_date,  
       (CASE  
         WHEN (SELECT COUNT(1)     
                 FROM stores s1    
                WHERE s1.store_id = s.store_id    
                  AND s1.s_date IS NOT NULL) = 0 THEN   
              0  
         WHEN (SELECT COUNT(1)     
                 FROM stores s1    
                WHERE s1.store_id = s.store_id    
                  AND s1.s_date IS NULL) = 0 THEN    
              2    
         ELSE    
              1    
       END) status  
  FROM stores s;    

Answer 3

谢谢大家的回复：

我已经解决了这个问题，如下所示：

首先，我创建了一个函数来计算状态，如下所示：

FUNCTION CALC_STATUS(s_date_count IN NUMBER, store_count  IN NUMBER)RETURN NUMBER
AS
  n_status NUMBER;
BEGIN
  IF s_date_count = 0 THEN
    n_status     := 0;
    RETURN n_status;
  END IF;
  IF s_date_count < store_count THEN
    n_status     := 1;
    RETURN n_status;
  END IF;
  IF s_date_count = store_count THEN
    n_status     := 2;
    RETURN n_status;
  END IF;
END CALC_STATUS;

然后上面的用户函数创建一个视图：

CREATE OR REPLACE VIEW V_SRORE (ID, STORE_ID, PROD_ID, S_DATE, STATUS)
                      AS
  SELECT A.ID         AS ID,
    A.STORE_ID        AS STORE_ID,
    A.PROD_ID         AS PROD_ID,
    A.S_DATE          AS S_DATE,
    TEMP_TABLE.STATUS AS STATUS
  FROM T_STORE A,
    (SELECT B.STORE_ID,
      CALC_STATUS(COUNT(B.S_DATE), COUNT(B.STORE_ID)) AS STATUS
    FROM T_STORE B
    GROUP BY B.STORE_ID
    ) TEMP_TABLE
  WHERE A.STORE_ID=TEMP_TABLE.STORE_ID
  ORDER BY A.STORE_ID DESC;