select * from T
where (key='starttime' and value<'20140823')
or (key='endtime' and value>'20140823')
or (key='type' and value='ok')
where (key='starttime' and value<'20140823')
or (key='endtime' and value>'20140823')
or (key='type' and value='ok')
where (key='starttime' and value<'20140823')
or (key='endtime' and value>'20140823')
or (key='type' and value='ok')
with t as (select 1 id, 'starttime' key, '20140822' value, 8 code
from dual
union all
select 2 id, 'starttime' key, '20140922' value, 9 code
from dual
union all
select 3 id, 'endtime' key, '20140925' value, 9 code
from dual
union all
select 4 id, 'type' key, 'ok' value, 9 code
from dual
union all
select 5 id, 'endtime' key, '20140830' value, 8 code
from dual
union all
select 6 id, 'type' key, 'ok' value, 8 code
from dual)
select distinct code
from t
where t.code not in
(select distinct code
from t
where (key = 'starttime' and value >= '20140823')
or (key = 'endtime' and value <= '20140823')
or (key = 'type' and value <> 'ok'));
8 20140822 20140830 ok
8 20140822 20140830 no
9 20140922 20140925 yes
9 20140922 20140925 ok
9 20140922 20140925 good就是说,把上面按列存储的各个数据,按code为标实,重新组成一个新表的结果
--测试数据
WITH t AS
(SELECT 1 id, 'starttime' key, '20140822' VALUE, 8 code
FROM dual
UNION ALL
SELECT 2 id, 'starttime' key, '20140922' VALUE, 9 code
FROM dual
UNION ALL
SELECT 3 id, 'endtime' key, '20140925' VALUE, 9 code
FROM dual
UNION ALL
SELECT 4 id, 'type' key, 'ok' VALUE, 9 code
FROM dual
UNION ALL
SELECT 5 id, 'endtime' key, '20140830' VALUE, 8 code
FROM dual
UNION ALL
SELECT 6 id, 'type' key, 'ok' VALUE, 8 code
FROM dual
UNION ALL
SELECT 7 id, 'type' key, 's' VALUE, 8 code
FROM dual)
--查询语句
SELECT m.*, n.value
FROM (SELECT code,
MIN(decode(key, 'starttime', VALUE, NULL)) starttime,
MAX(decode(key, 'endtime', VALUE, NULL)) endtime
FROM t
GROUP BY code) m
LEFT JOIN (SELECT code, VALUE
FROM t
WHERE key = 'type') n
ON m.code = n.code
--结果
CODE STARTTIME ENDTIME VALUE
---------- --------- -------- --------
9 20140922 20140925 ok
8 20140822 20140830 ok
8 20140822 20140830 s