Oracle SQL-组和明细记录

用户名

我正在尝试构建一个Oracle SQL查询，当计数大于1时，它将给我按行分组以及组成该组的行。请参见下面的示例以及进行分组的SQL查询。任何帮助或建议，将不胜感激。

例如，使用以下数据集-

======================
 ID | NAME |  AUTHOR
======================
 2  | Abc  | John 
 6  | Abc  | John 
 3  | Xyz  | Mike 
 4  | Abc  | Mike 
 5  | Xyz  | John 
 1  | Abc  | Mike 
 7  | PQR  | Raj 


Expected Result - 
===========================
ID | NAME | AUTHOR | COUNT
===========================
   | Abc  |        |  4
2  | Abc  |  John  |    
6  | Abc  |  John  |    
4  | Abc  |  Mike  |    
1  | Abc  |  Mike  |    
   | PQR  |        |  1
   | Xyz  |        |  2
3  | Xyz  |  Mike  |    
5  | Xyz  |  John  |    


SELECT  NAME, COUNT(NAME) from (
SELECT 2 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
UNION 
SELECT 6 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
UNION
SELECT 3 as ID, ' Xyz  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
UNION
SELECT 4 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
UNION
SELECT 5 as ID, ' Xyz  ' as NAME, ' John ' as AUTHOR FROM DUAL
UNION
SELECT 1 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
UNION
SELECT 7 as ID, ' PQR  ' as NAME, ' Raj ' as AUTHOR FROM DUAL)
GROUP BY NAME
ORDER by NAME;

德米特里·尼基福罗夫（Dmitry Nikiforov）

SQL> with t as (
  2  SELECT 2 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
  3  UNION
  4  SELECT 6 as ID, ' Abc  ' as NAME, ' John ' as AUTHOR FROM DUAL
  5  UNION
  6  SELECT 3 as ID, ' Xyz  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
  7  UNION
  8  SELECT 4 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
  9  UNION
 10  SELECT 5 as ID, ' Xyz  ' as NAME, ' John ' as AUTHOR FROM DUAL
 11  UNION
 12  SELECT 1 as ID, ' Abc  ' as NAME, ' Mike ' as AUTHOR FROM DUAL
 13  UNION
 14  SELECT 7 as ID, ' PQR  ' as NAME, ' Raj ' as AUTHOR FROM DUAL)
 15  select id, name, author, count#
 16  from (
 17  select t.id, t.name, t.author, decode(grouping(id),1,count(*),null) count#,
 18  count(*) over (partition by name) cn, grouping(id) gid
 19  from t
 20  group by grouping sets((id,name,author),(name))
 21  )
 22  where (cn != 2 or count# is not null)
 23  order by name, gid desc, author
 24  /

        ID NAME   AUTHOR     COUNT#                                             
---------- ------ ------ ----------                                             
            Abc                   4                                             
         2  Abc    John                                                         
         6  Abc    John                                                         
         4  Abc    Mike                                                         
         1  Abc    Mike                                                         
            PQR                   1                                             
            Xyz                   2                                             
         5  Xyz    John                                                         
         3  Xyz    Mike

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。