以下场景的Sql查询

Question

我有以下场景，我需要为其编写sql查询。

我有ICCID表和ICCID属性表，其中包含以下信息。

​

​

我想找出2012年12月所有处于移除状态的活动ICCID和ICCID。对于处于移除状态的ICCID，在ICCID属性表本身的date.to.change key中记录ICCID的移除日期。

this is my attempt, but that did not worked
     select e.ID_ICCID from ICCID_PROPERTY e where 
     e.c_key ='STATE' and e.c_value='Active' or(
     e.c_key ='STATE' and 
     e.c_value='Removed' and
     e.c_key='date.to.change' and  
     to_date(e.c_value,'yyyymmdd')  >=to_date('2012-DEC-01 00:00:00', 'YYYY-MON-DD HH24:MI:SS') and 
     to_date(e.c_value,'yyyymmdd')  <= to_date('2012-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS')
     ))

提前感谢您的帮助

Answer 1

这是像这样的键-值对设计的问题之一……

您不能只检查单个属性行以查看它是否与搜索条件匹配，因为本例中的条件将跨越多个属性...您必须检查单个父行是否具有匹配的所有子属性：

SELECT
    i.ICCID
FROM
    ICCID i
WHERE
    EXISTS (
        SELECT 1
        FROM ICCID_PROPERTY ip
        WHERE 
            ip.ID_ICCID = i.ID_ICCID
            AND ip.c_key = 'STATE'
            AND ip.c_value = 'Active'
    ) OR (
        EXISTS (
            SELECT 1
            FROM ICCID_PROPERTY ip
            WHERE 
                ip.ID_ICCID = i.ID_ICCID
                AND ip.c_key = 'STATE'
                AND ip.c_value = 'Removed'
        ) AND
        EXISTS (
            SELECT 1
            FROM ICCID_PROPERTY ip
            WHERE 
                ip.ID_ICCID = i.ID_ICCID
                AND ip.c_key = 'date.to.change'
                AND to_date(ip.c_value,'yyyymmdd') >= 
                    to_date('2012-DEC-01 00:00:00', 'YYYY-MON-DD HH24:MI:SS') 
                AND to_date(ip.c_value,'yyyymmdd') <= 
                    to_date('2012-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS')
        )
    )

Answer 2

我认为您可以加入属性表三次--可能是这样的(未经测试)：

SELECT I.ID_ICCID
FROM ICCID I
  JOIN ICCID_Property IP ON I.ID_ICCID = IP.ID_ICCID AND IP.C_Key = 'STATE' AND IP.C_Value = 'Active'
  JOIN ICCID_Property IP2 ON I.ID_ICCID = IP.ID_ICCID AND IP2.C_Key = 'STATE' AND IP2.C_Value= 'Removed'
  JOIN ICCID_Property IP3 ON I.ID_ICCID = IP.ID_ICCID AND IP3.C_Key = 'date.to.change' AND to_date(IP3.C_Value,'yyyymmdd') >= to_date('2012-DEC-01 00:00:00', 'YYYY-MON-DD HH24:MI:SS') 
            AND to_date(IP3.C_Value,'yyyymmdd') <= 
                to_date('2012-DEC-31 23:59:59', 'YYYY-MON-DD HH24:MI:SS')

祝好运。