首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >要汇总的Oracle Sql查询,但获取仅显示一次的主列

要汇总的Oracle Sql查询,但获取仅显示一次的主列
EN

Stack Overflow用户
提问于 2019-02-21 01:58:21
回答 1查看 134关注 0票数 0

我之前使用的数据

代码语言:javascript
复制
 TOWNSHIP             PCT      TOTAL
 -------------------- --- ----------
 Agat                 4          688
 Agat                 04A        611
 Agat                 04B        603
 Agat                           1902
 Piti                 3          441
 Piti                            441
 Yigo                 19         376
 Yigo                 19A        405
 Yigo                 19B        465
 Yigo                 19C        418
 Yigo                 19D        353
 Yigo                 19E        373
 Yigo                 19F        318
 Yigo                           2708
 Yona                 10         395
 Yona                 10A        424
 Yona                 10B        343

新数据如下所示

代码语言:javascript
复制
with tab as 
(
select 'Hagatna' as township, '1' as pct, 373 as voters,'1---(A-Z)' as 
precinct_inc,'Guam Congress Building' as polling location, 'Anigua' as 
landmark from dual union all
select 'Hagatna',    '1',   373,  '1---(A-Z)',     'Guam Congress Building',                         
'Anigua' from dual union all
select 'Piti',       '3',   441,  '3---(A-Z)',     'Jose L.G. Rios Middle 
School Cafeteria',         'Nimitz Hill Estate'                   from dual 
union all
select 'Agat',       '4',   688,  '4---(A-D)',     'Marcial Sablan Elem. 
School Classrooms',         'Mt.Lamlam, Finile Beach'              from dual 
union all  
select 'Agat',       '04A', 611,  '4A-(E-P)',      'Marcial Sablan Elem. 
School Classrooms',         'Mt.Lamlam, Finile Beach'              from dual 
union all    
select 'Agat',       '04B', 603,  '4B-(Q-Z)',      'Marcial Sablan Elem. 
School Classrooms',         'Mt.Lamlam, Finile Beach'              from dual 
union all    
select 'Santa Rita', '5',   537   '5---(A-K)',     'Harry S. Truman Elem 
School Classrooms',         'Apra Hts., Naval Station, Santa Rosa' from dual 
union all    
select 'Santa Rita', '05A', 555   '5A-(L-Z)',      'Harry S. Truman Elem 
School Classrooms',         'Apra Hts., Naval Station, Santa Rosa' from dual 
union all    
select 'Umatac',     '6',   315   '6---(A-Z)',     'Umatac Mayors Office',                           
'n/a'                                  from dual union all
select 'Merizo',     '7',   501   '7---(A-K)',     'Merizo  Martyrs Memorial 
Elem School Cafeteria', 'n/a'                                  from dual 
union all
select 'Merizo',     '07A', 531   '7A-(L-Z)',      'Merizo  Martyrs Memorial 
Elem School Cafeteria', 'n/a'                                  from dual 
union all select 'Inarajan',   '8',   412   '8---(A-Fi)',    'Inarajan 
Middle School Classrooms',              'Malojloj'                             
from dual union all
select 'Inarajan',   '08A', 433   '8A-(Fj-Pa)',    'Inarajan Middle School 
Classrooms',              'Malojloj'                             from dual 
union all
select 'Inarajan',   '08B', 383   '8B-(Pb-Z)',     'Inarajan Middle School 
Classrooms',              'Malojloj'                             from dual 
union all
select 'Talofofo',   '9',   624   '9---(A-M)',     'Talofofo Elem.  School 
Classrooms',              'Babulao, Ipan'                        from dual 
union all
select 'Talofofo',   '09A', 589   '9A-(N-Z)',      'Talofofo Elem.  School 
Classrooms',               'Babulao, Ipan'                        from dual 
union all
select 'Yona',       '10',  395   '10--(A-E)',     'MU Lujan Elem.  School 
Cafeteria',               'Baza Gardens, Togcha'                 from dual 
union all  
select 'Yona',       '10A', 424   '10A-(F-P)',     'MU Lujan Elem.  School 
Cafeteria',               'Baza Gardens, Togcha'                 from dual 
union all
select 'Yona',       '10B', 343   '10B-(Q-Z)',     'MU Lujan Elem.  School 
Cafeteria',               'Baza Gardens, Togcha'                 from dual ) 

这就是我的数据现在的样子,我不想显示一次以上的乡级列,如何在sql中实现这一点?

它应该喜欢下面关于如何实现这一点的任何想法。

代码语言:javascript
复制
  TOWNSHIP             PCT      TOTAL
  -------------------- --- ----------
  Agat                 4          688
                       04A        611
                       04B        603
  Total                           1902

  MY QUERY:

 COL VILLAGE FOR A20
 SET PAGESIZE 50000
  set lines 154
  --grouping(district), grouping(pct)
 select NVL(DISTRICT,'')
 district, pct,
 sum(VOTERS) as TOTAL FROM 
 (SELECT distinct DISTRICT,
 PCT,
 COUNT(*) AS VOTERS
   FROM
  REG_TAB
 JOIN PCT_LOOKUP
 ON
 PCT=PERCINCT_MAP
 AND VILLAGE IN (UPPER(SUBSTR(DISTRICT,1,INSTR(DISTRICT,'/',1)-1)),UPPER( 
 SUBSTR(REPLACE(DISTRICT,'.',''),INSTR(DISTRICT,'/',1)+1,99))) GROUP BY   
 PCT,DISTRICT ORDER BY to_number(substr(PCT,1,length(PCT)- 
 nvl(length(replace(translate(PCT,'0123456789','0000000000'),'0','')),0))),
 substr(PCT,1+length(PCT)- 
 nvl(length(replace(translate(PCT,'0123456789','0000000000'),'0','')),0)) 
 NULLS FIRST) group by ROLLUP(district, pct) ;

我想要像上表一样,我尝试了rollup,但这带来了列名,但我不希望该列名显示多次,一次就足够了。

只是想知道如何实现这一点。

EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/54792579

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档