小弟有一个问题请教大家,我有一个表tableAtableAnumber name code city date
111 AAA 北京 2010-3-3 12:00:00
123 AAA 天津 2010-3-8 12:32:00
134 CCC 上海 2010-3-4 18:56:00
111 ABC 23 广州 2010-3-3 12:00:00
145 AAA 重庆 2010-3-6 15:00:00
156 BBB 成都 2010-3-3 12:34:00
167 AAA 西安 2010-3-1 12:00:00
123 ABC 23 长春 2010-3-3 12:00:00
123 BBB 沈阳 2010-3-3 14:23:00
189 AAA 天津 2010-3-5 12:00:00
189 BBB 北京 2010-3-3 23:00:00
156 ABC 23 成都 2010-3-7 21:00:00
190 AAA 上海 2010-3-3 22:00:00
如表所示,我想要的结果是number name code city date
134 CCC 上海 2010-3-4 18:56:00
111 ABC 23 广州 2010-3-3 12:00:00
145 AAA 重庆 2010-3-6 15:00:00
156 BBB 成都 2010-3-3 12:34:00
167 AAA 西安 2010-3-1 12:00:00
123 ABC 23 长春 2010-3-3 12:00:00
123 BBB 沈阳 2010-3-3 14:23:00
189 AAA 天津 2010-3-5 12:00:00
189 BBB 北京 2010-3-3 23:00:00
156 ABC 23 成都 2010-3-7 21:00:00
190 AAA 上海 2010-3-3 22:00:00 对于111和123两个号,如果存在ABC 23的时候,那么就去滤掉AAA的那条记录。
条件:同一个number,只要遇到ABC 23的时候,就滤掉此号的AAA那条。像号码156,只有ABC 23,没有AAA的时候,就只取ABC 23这条。号码145,只有AAA,没有ABC 23,那么只取AAA这条。
小弟不知道如何做,有人说过用decode,请问各位大虾,如何才能实现呢????
111 AAA 北京 2010-3-3 12:00:00
123 AAA 天津 2010-3-8 12:32:00
134 CCC 上海 2010-3-4 18:56:00
111 ABC 23 广州 2010-3-3 12:00:00
145 AAA 重庆 2010-3-6 15:00:00
156 BBB 成都 2010-3-3 12:34:00
167 AAA 西安 2010-3-1 12:00:00
123 ABC 23 长春 2010-3-3 12:00:00
123 BBB 沈阳 2010-3-3 14:23:00
189 AAA 天津 2010-3-5 12:00:00
189 BBB 北京 2010-3-3 23:00:00
156 ABC 23 成都 2010-3-7 21:00:00
190 AAA 上海 2010-3-3 22:00:00
如表所示,我想要的结果是number name code city date
134 CCC 上海 2010-3-4 18:56:00
111 ABC 23 广州 2010-3-3 12:00:00
145 AAA 重庆 2010-3-6 15:00:00
156 BBB 成都 2010-3-3 12:34:00
167 AAA 西安 2010-3-1 12:00:00
123 ABC 23 长春 2010-3-3 12:00:00
123 BBB 沈阳 2010-3-3 14:23:00
189 AAA 天津 2010-3-5 12:00:00
189 BBB 北京 2010-3-3 23:00:00
156 ABC 23 成都 2010-3-7 21:00:00
190 AAA 上海 2010-3-3 22:00:00 对于111和123两个号,如果存在ABC 23的时候,那么就去滤掉AAA的那条记录。
条件:同一个number,只要遇到ABC 23的时候,就滤掉此号的AAA那条。像号码156,只有ABC 23,没有AAA的时候,就只取ABC 23这条。号码145,只有AAA,没有ABC 23,那么只取AAA这条。
小弟不知道如何做,有人说过用decode,请问各位大虾,如何才能实现呢????
所有记录MINUS这些记录就好了
minus
select * from tabA
where name ='ABC 23'
group by number
having count(*)>1
minus
select * from tabA
where name ='ABC'
AND CODE=23
group by number
having count(*)>1
这样可行不
select 111 num, 'AAA' name, '北京' city, to_date('2010-3-3 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 123 num, 'AAA' name, '天津' city, to_date('2010-3-8 12:32:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 134 num, 'CCC' name, '上海' city, to_date('2010-3-4 18:56:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 111 num, 'ABC 23' name, '广州' city, to_date('2010-3-3 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 145 num, 'AAA' name, '重庆' city, to_date('2010-3-6 15:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 156 num, 'BBB' name, '成都' city, to_date('2010-3-3 12:34:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 167 num, 'AAA' name, '西安' city, to_date('2010-3-1 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 123 num, 'ABC 23' name, '长春' city, to_date('2010-3-3 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 123 num, 'BBB' name, '沈阳' city, to_date('2010-3-3 14:23:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 189 num, 'AAA' name, '天津' city, to_date('2010-3-5 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 189 num, 'BBB' name, '北京' city, to_date('2010-3-3 23:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 156 num, 'ABC 23' name, '成都' city, to_date('2010-3-7 21:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 190 num, 'AAA' name, '上海' city, to_date('2010-3-3 22:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual)
SELECT *
FROM test a
WHERE NOT EXISTS (SELECT 1
FROM test b
WHERE a.num = b.num AND
b.name = 'ABC 23' AND
a.name = 'AAA');
--c查询不是特殊条件的记录
select * from tabA
where name <> 'ABC'
AND CODE<>23
union
--特殊条件,但是记录为一条的
select * from tabA
where name ='ABC'
AND CODE=23
group by number
having count(*)=1
看看这个效率
效率高点,
With t As (
select '111' numb, 'AAA' name, '' code, '上海' city, '2010-3-3 12:00:00' datetime from dual union all
select '123' numb, 'AAA' name, '' code, '天津 ' city, '2010-3-8 12:32:00' datetime from dual union all
select '134' numb, 'CCC' name, '' code, '上海 ' city, '2010-3-4 18:56:00' datetime from dual union all
select '111' numb, 'ABC' name, '23' code, '广州 ' city,'2010-3-3 12:00:00' datetime from dual union all
select '145' numb, 'AAA' name, '' code, '重庆 ' city, '2010-3-6 15:00:00' datetime from dual union all
select '156' numb, 'BBB' name, '' code, '成都 ' city, '2010-3-3 12:34:00' datetime from dual union all
select '167' numb, 'AAA' name, '' code, '西安 ' city, '2010-3-1 12:00:00' datetime from dual union all
select '123' numb, 'ABC' name, '23' code, '长春 ' city,'2010-3-3 12:00:00' datetime from dual union all
select '123' numb, 'BBB' name, '' code,'沈阳 ' city, '2010-3-3 14:23:00' datetime from dual union all
select '189' numb, 'AAA' name, '' code,'天津 ' city, '2010-3-5 12:00:00' datetime from dual union all
select '189' numb, 'BBB' name, '' code,'北京 ' city, '2010-3-3 23:00:00' datetime from dual union all
select '156' numb, 'ABC' name, '23' code, '成都 ' city,'2010-3-7 21:00:00' datetime from dual union all
select '190' numb, 'AAA' name, '' code, '上海 ' city, '2010-3-3 22:00:00' datetime from dual
)
Select * From t
Where Not Exists (Select 1 From (
Select numb From (
Select numb,wm_concat(Name) Name,wm_concat(code) code From t
Group By numb
)
Where instr(Name,'AAA') <> 0 And instr(Name,'ABC') <> 0
And code = '23') t1
Where t.numb = t1.numb)写了一个,有待学习
--c查询不是特殊条件的记录
select * from tabA
where name <> 'ABC'
AND CODE<>23
union
--特殊条件,但是记录为一条的
select * from tabA
where name ='ABC'
AND CODE=23
group by number
having count(*)=1---这个根本就不能执行,group by 报错
where name <> 'ABC'
AND CODE<>23
union all
select * from tabA a
where exist
(select 1
from tabA b
where
a.number = b.number
AND b.name ='ABC'
AND b.CODE=23
group by number
having count(*)=1)
With t As (
select '111' numb, 'AAA' name, '' code, '上海' city, '2010-3-3 12:00:00' datetime from dual union all
select '123' numb, 'AAA' name, '' code, '天津 ' city, '2010-3-8 12:32:00' datetime from dual union all
select '134' numb, 'CCC' name, '' code, '上海 ' city, '2010-3-4 18:56:00' datetime from dual union all
select '111' numb, 'ABC' name, '23' code, '广州 ' city,'2010-3-3 12:00:00' datetime from dual union all
select '145' numb, 'AAA' name, '' code, '重庆 ' city, '2010-3-6 15:00:00' datetime from dual union all
select '156' numb, 'BBB' name, '' code, '成都 ' city, '2010-3-3 12:34:00' datetime from dual union all
select '167' numb, 'AAA' name, '' code, '西安 ' city, '2010-3-1 12:00:00' datetime from dual union all
select '123' numb, 'ABC' name, '23' code, '长春 ' city,'2010-3-3 12:00:00' datetime from dual union all
select '123' numb, 'BBB' name, '' code,'沈阳 ' city, '2010-3-3 14:23:00' datetime from dual union all
select '189' numb, 'AAA' name, '' code,'天津 ' city, '2010-3-5 12:00:00' datetime from dual union all
select '189' numb, 'BBB' name, '' code,'北京 ' city, '2010-3-3 23:00:00' datetime from dual union all
select '156' numb, 'ABC' name, '23' code, '成都 ' city,'2010-3-7 21:00:00' datetime from dual union all
select '190' numb, 'AAA' name, '' code, '上海 ' city, '2010-3-3 22:00:00' datetime from dual
)
Select * From t
Where Exists (Select 1 From(
Select numb,code From (
Select numb,wm_concat(Name) Name,wm_concat(code) code From t
Group By numb
)
Where instr(Name,'ABC') <> 0 And instr(Name,'AAA') <> 0) t1
Where t.numb = t1.numb
And t.code Is Not Null
)
Union All
Select * From t
Where Not Exists (Select 1 From (
Select numb From (
Select numb,wm_concat(Name) Name,wm_concat(code) code From t
Group By numb )
Where instr(Name,'AAA') <> 0 And instr(Name,'ABC') <> 0
And code = '23') t1
Where t.numb = t1.numb)改进了下
from test a
where not exists
(
select 1
from test b
where a.number = b.number
and b.name = 'ABC'
and b.code = 23
and a.name = 'AAA'
);
方法一(将需要过滤的编号查询出来,并将结果集根据该方式进行过滤):
--对于子查询内部返回的数据较少的情况(几千条以内,且外部表的number字段最好有索引,使用/*+merge_aj*/小表反向链接查询):
SELECT number,name,code,city,date
FROM tableA a1
WHERE NOT EXISTS(
SELECT /*+merge_aj*/1
FROM tableA a2
WHERE a2.name = 'ABC'
AND a2.code = '23'
AND a1.number = a2.number
AND a1.name = 'AAA'
);
--对于子查询返回数据较多的情况(大部分都符合NOT EXISTS的条件,那么此时使用/*+hash_aj*/进行反向散列链接查询)
SELECT number,name,code,city,date
FROM tableA a1
WHERE NOT EXISTS(
SELECT /*+hash_aj*/1
FROM tableA a2
WHERE a2.name = 'ABC'
AND a2.code = '23'
AND a1.number = a2.number
AND a1.name = 'AAA'
);
方法2(使用外连接,过滤数据掉相同的数据部分,不过对于你的数据量我本地没有模拟过,你试一试也可,该办法要直观一点,不过效率上要根据实际表的数据量索引情况而定了):
SELECT number,name,code,city,date FROM (
SELECT a1.*,a2.number number2
FROM tableA a1,(SELECT number FROM tableA WHERE name = 'ABC' AND code = '23') a2
WHERE a1.number = a2.number(+)
)
WHERE number2 IS NULL OR name <> 'AAA';
select 111 num, 'AAA' name1,'' code, '北京' city, to_date('2010-3-3 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 123 num, 'AAA' name1,'' code, '天津' city, to_date('2010-3-8 12:32:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 134 num, 'CCC' name1,'' code, '上海' city, to_date('2010-3-4 18:56:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 111 num, 'ABC' name1,'23' code, '广州' city, to_date('2010-3-3 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 145 num, 'AAA' name1, '' code, '重庆' city, to_date('2010-3-6 15:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 156 num, 'BBB' name1,'' code, '成都' city, to_date('2010-3-3 12:34:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 167 num, 'AAA' name1,'' code, '西安' city, to_date('2010-3-1 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 123 num, 'ABC' name1,'23' code, '长春' city, to_date('2010-3-3 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 123 num, 'BBB' name1,'' code, '沈阳' city, to_date('2010-3-3 14:23:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 189 num, 'AAA' name1,'' code, '天津' city, to_date('2010-3-5 12:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 189 num, 'BBB' name1,'' code, '北京' city, to_date('2010-3-3 23:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 156 num, 'ABC' name1,'23' code, '成都' city, to_date('2010-3-7 21:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual union all
select 190 num, 'AAA' name1,'' code, '上海' city, to_date('2010-3-3 22:00:00','yyyy-mm-dd hh24:mi:ss') dat from dual)
select * from test
minus
SELECT * FROM TEST WHERE NUM IN (SELECT DISTINCT NUM FROM TEST WHERE NAME1='ABC' AND CODE='23') AND NAME1='AAA'结果111 ABC 23 广州 2010-3-3 12:00:00
123 ABC 23 长春 2010-3-3 12:00:00
123 BBB 沈阳 2010-3-3 14:23:00
134 CCC 上海 2010-3-4 18:56:00
145 AAA 重庆 2010-3-6 15:00:00
156 ABC 23 成都 2010-3-7 21:00:00
156 BBB 成都 2010-3-3 12:34:00
167 AAA 西安 2010-3-1 12:00:00
189 AAA 天津 2010-3-5 12:00:00
189 BBB 北京 2010-3-3 23:00:00
190 AAA 上海 2010-3-3 22:00:00