表A.字段
only_unit_code varchar2(50) -- 唯一
PRIORITY varchar2(2) -- 优先级
AREA varchar2(2) -- 地市编码请编写一段sql,查询条件需接受一个地市参数. 即select * from A where area=&area;
查询结果如下:
1.当 地区编码不等于传入参数 且 存在 有 地区编码不等于传入参数条件下 优先级等于1 的结果
则 只显示 地区编码等于传入参数 且 优先级为 1 的结果
area<>&area and ( (select count(*) from A where priority = 1 and area<>&area) > 1)
2.当 地区编码不等于传入参数 且 不存在 地区编码不等于传入参数条件下 优先级等于1 的结果
则 显示 地区编码等于传入参数 按优先级排序后的结果
逻辑有点复杂,请各位高手耐心看看能否一句sql实现,谢谢附上sql,方便大家测试
select 'GD_MB_PFILE_0001_10' as only_unit,'3' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0003_10' as only_unit,'3' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0005_10' as only_unit,'3' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0007_10' as only_unit,'5' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0009_10' as only_unit,'5' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0011_10' as only_unit,'5' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0013_10' as only_unit,'2' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0015_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0017_10' as only_unit,'2' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0019_10' as only_unit,'2' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0021_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0023_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0025_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0027_10' as only_unit,'1' as priority,'20' area_id from dual union all
select 'GD_MB_PFILE_0029_10' as only_unit,'1' as priority,'11' area_id from dual union all
select 'GD_MB_PFILE_0031_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0032_10' as only_unit,'1' as priority,'15' area_id from dual union all
select 'GD_MB_PFILE_0034_10' as only_unit,'1' as priority,'16' area_id from dual union all
select 'GD_MB_PFILE_0035_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0037_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0039_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0040_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0041_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0043_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0045_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0047_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0049_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0051_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0052_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0054_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0055_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0056_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0057_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0058_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0059_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0060_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0061_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0062_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0063_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0064_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0067_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0069_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0071_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0072_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0073_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0075_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0076_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0079_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0082_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0083_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0084_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0085_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0086_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0087_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0088_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0089_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0090_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0091_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0092_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0093_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0094_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0095_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0096_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0097_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0098_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0099_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0100_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0101_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0102_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0103_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0104_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0105_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0106_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0107_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0108_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0109_10' as only_unit,'1' as priority,'10' area_id from dual
only_unit_code varchar2(50) -- 唯一
PRIORITY varchar2(2) -- 优先级
AREA varchar2(2) -- 地市编码请编写一段sql,查询条件需接受一个地市参数. 即select * from A where area=&area;
查询结果如下:
1.当 地区编码不等于传入参数 且 存在 有 地区编码不等于传入参数条件下 优先级等于1 的结果
则 只显示 地区编码等于传入参数 且 优先级为 1 的结果
area<>&area and ( (select count(*) from A where priority = 1 and area<>&area) > 1)
2.当 地区编码不等于传入参数 且 不存在 地区编码不等于传入参数条件下 优先级等于1 的结果
则 显示 地区编码等于传入参数 按优先级排序后的结果
逻辑有点复杂,请各位高手耐心看看能否一句sql实现,谢谢附上sql,方便大家测试
select 'GD_MB_PFILE_0001_10' as only_unit,'3' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0003_10' as only_unit,'3' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0005_10' as only_unit,'3' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0007_10' as only_unit,'5' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0009_10' as only_unit,'5' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0011_10' as only_unit,'5' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0013_10' as only_unit,'2' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0015_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0017_10' as only_unit,'2' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0019_10' as only_unit,'2' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0021_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0023_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0025_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0027_10' as only_unit,'1' as priority,'20' area_id from dual union all
select 'GD_MB_PFILE_0029_10' as only_unit,'1' as priority,'11' area_id from dual union all
select 'GD_MB_PFILE_0031_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0032_10' as only_unit,'1' as priority,'15' area_id from dual union all
select 'GD_MB_PFILE_0034_10' as only_unit,'1' as priority,'16' area_id from dual union all
select 'GD_MB_PFILE_0035_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0037_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0039_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0040_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0041_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0043_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0045_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0047_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0049_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0051_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0052_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0054_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0055_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0056_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0057_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0058_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0059_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0060_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0061_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0062_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0063_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0064_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0067_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0069_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0071_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0072_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0073_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0075_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0076_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0079_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0082_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0083_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0084_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0085_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0086_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0087_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0088_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0089_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0090_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0091_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0092_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0093_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0094_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0095_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0096_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0097_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0098_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0099_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0100_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0101_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0102_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0103_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0104_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0105_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0106_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0107_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0108_10' as only_unit,'1' as priority,'10' area_id from dual union all
select 'GD_MB_PFILE_0109_10' as only_unit,'1' as priority,'10' area_id from dual
from tab t
where t.priority = 1
and t.area = &area
and exists (select *
from tab t1
where t1.area != &area
and t1.priority = 1)
from tab t
where t.area = &area
and not exists (select *
from tab t1
where t1.area != &area
and t1.priority = 1)
order by t.priority
select *
from tab t
where t.area = &area
and ((t.priority = 1 and exists
(select *
from tab t1
where t1.area != &area
and t1.priority = 1)) or not exists
(select *
from tab t1
where t1.area != &area
and t1.priority = 1))
order by t.priority
SELECT A.*,
CASE
WHEN A.PRIORITY <> 1 AND
(SELECT COUNT(*)
FROM TEST
WHERE AREA_ID <> '10'
AND PRIORITY = 1) = 0 THEN
'T'
WHEN A.Priority <> 1 AND (SELECT COUNT(*)
FROM TEST
WHERE AREA_ID <> '10'
AND PRIORITY = 1) > 0 THEN
'F'
ELSE
'T'
END flag
FROM TEST A ) x where x.flag='T' and x.area_id='10';