求其它科目每月对应的数量都大于科目为 101 相应月份的数量
create table kemu
(
accid number,
kemu varchar2(8),
amt number,
mth number
)
insert into kemu values(1, '101', 10, 1);
insert into kemu values(2, '101', 20, 2);
insert into kemu values(3, '101', 30, 3);insert into kemu values(4, '102', 20, 1);
insert into kemu values(5, '102', 30, 2);
insert into kemu values(6, '102', 40, 3);insert into kemu values(7, '103', 50, 1);
insert into kemu values(8, '103', 60, 2);
insert into kemu values(9, '103', 70, 3);insert into kemu values(10, '104', 51, 1);
insert into kemu values(11, '104', 8, 2); -- 2月份的小于 科目 101 中2月份的数量
insert into kemu values(12, '104', 73, 3);insert into kemu values(13, '105', 9, 1); -- 1月份的小于 科目 101 中1月份的数量
insert into kemu values(14, '105', 62, 2);
insert into kemu values(15, '105', 73, 3);select * from kemu;-- 这是我写出的SQL, 感觉不是很好, 请教其它的写法, 谢谢!
select distinct kemu
from kemu k
where k.kemu not in
(
select kemu
from kemu k1
where exists
(
select 1
from kemu k2
where k1.amt < k2.amt and k1.mth = k2.mth and k2.kemu = '101'
)
)
and k.kemu <> '101'
create table kemu
(
accid number,
kemu varchar2(8),
amt number,
mth number
)
insert into kemu values(1, '101', 10, 1);
insert into kemu values(2, '101', 20, 2);
insert into kemu values(3, '101', 30, 3);insert into kemu values(4, '102', 20, 1);
insert into kemu values(5, '102', 30, 2);
insert into kemu values(6, '102', 40, 3);insert into kemu values(7, '103', 50, 1);
insert into kemu values(8, '103', 60, 2);
insert into kemu values(9, '103', 70, 3);insert into kemu values(10, '104', 51, 1);
insert into kemu values(11, '104', 8, 2); -- 2月份的小于 科目 101 中2月份的数量
insert into kemu values(12, '104', 73, 3);insert into kemu values(13, '105', 9, 1); -- 1月份的小于 科目 101 中1月份的数量
insert into kemu values(14, '105', 62, 2);
insert into kemu values(15, '105', 73, 3);select * from kemu;-- 这是我写出的SQL, 感觉不是很好, 请教其它的写法, 谢谢!
select distinct kemu
from kemu k
where k.kemu not in
(
select kemu
from kemu k1
where exists
(
select 1
from kemu k2
where k1.amt < k2.amt and k1.mth = k2.mth and k2.kemu = '101'
)
)
and k.kemu <> '101'
FROM kemu t
WHERE t.kemu <> '101'
AND NOT EXISTS (SELECT a.kemu
FROM kemu a, kemu b
WHERE b.kemu = '101'
AND a.mth = b.mth
AND a.amt < b.amt
AND a.kemu = t.kemu)
--写法大同小异,关键是应该有一张科目的基础表,就可以不用DISTINCT排序去重操作,效率更好