sql—创建新查询以使其与上一个查询具有相同的效果

uxhixvfz  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(223)

我有下表:

Region_id | Region_name
1           Europe
2           Americas
3           Asia
4           Middle East and Africa

还有这个问题:

SELECT region_id, region_name
  FROM hr.regions
 GROUP BY CUBE(region_id, region_name);

返回:

Region_id | Region_name
(null)      (null)
(null)      Asia
(null)      Europe
(null)      Americas
(null)      Middle East and Africa
1           (null)
1           Europe
2           (null)
2           Americas
3           (null)
3           Asia
4           (null)
4           Middle East and Africa

问题和问题:如何创建另一个返回与上面相同结果但使用诸如union和intersect等集合操作而不是 group by cube(...) ?

bis0qfac

bis0qfac1#

您可以使用:

select region_id, region_name
from hr_regions
union all
select NULL as region_id, region_name
from hr_regions
union all
select region_id, NULL as region_name
from hr_regions
union all
select NULL as region_id, NULL as region_name
from dual;

您的示例数据在两列中都没有重复。所以没有聚合或 select distinct 是需要的。唯一的例外是最后一个查询。只有一排两排 NULL 值,它从 dual .

相关问题