oracle - 使用内部连接在 oracle 中获取特定列值计数

Question

我通过加入两个表得到了一些列和值

 Select                  
    tbl_orderdetails.category_name,
    tbl_orderdetails.branch_name,
    tbl_ordermaster.created_date,
    tbl_ordermaster.user_id, 
    tbl_orderdetails.order_details_id,
    tbl_orderdetails.branch_id 
 From tbl_orderdetails Inner Join tbl_ordermaster ON 
    tbl_orderdetails.order_master_id=tbl_ordermaster.ordermasterid 
 where tbl_ordermaster.user_id='12'

我想获取特定分支名称出现的次数。我使用了 count 但它不起作用，我只希望分支名称出现的最大次数和前 3 名显示。例如:

vellore=100,
chennai=18,
tvl=80,
harithuwar=90 

它应该只显示

vellore
harithwar 
tvl 

样本数据订单详情

orderdatailsid | order_master_id |分支名称|类别|分支编号

1 | 112|韦洛尔 |纳德 | 123

2 | 112|韦洛尔 |胡 | 123

3 | 113 |钦奈 |记 | 121

4 | 112|韦洛尔 |嗨 | 123

5 | 134 |电视 |用户界面 | 145

6 | 134 |电视 |乔 | 145

主要细节

ordermasterid |用户名

112 | 12

113 | 13

134 | 14

Answer 1

试试这个

SELECT T.*,S.* FROM
(
  Select TD.category_name,TD.branch_name,TM.created_date,TM.user_id,TD.order_details_id,TD.branch_id
  From tbl_orderdetails TD Inner Join tbl_ordermaster TM ON 
    TD.order_master_id = TM.ordermasterid 
  Where TM.user_id='12'
) T Left Join
(
  Select T1.branch_name,Count(T1.branch_name) As No_Of_Branch
  From tbl_orderdetails T1 Inner Join tbl_ordermaster T2 ON 
       T1.order_master_id = T2.ordermasterid 
  Where T2.user_id='12'
  Group By T1.branch_name
) S On S.branch_name = T.branch_name

更新

如果你想得到 Max of count

试试这个

SELECT T.*,S.* FROM
(
  Select TD.category_name,TD.branch_name,TM.created_date,TM.user_id,TD.order_details_id,TD.branch_id
  From tbl_orderdetails TD Inner Join tbl_ordermaster TM ON 
    TD.order_master_id = TM.ordermasterid 
  Where TM.user_id='12'
) T Inner Join
(
SELECT M.branch_name,Max(M.No_Of_Branch) FROM
  (
    Select T1.branch_name,Count(T1.branch_name) As No_Of_Branch
    From tbl_orderdetails T1 Inner Join tbl_ordermaster T2 ON 
         T1.order_master_id = T2.ordermasterid 
    Where T2.user_id='12'
    Group By T1.branch_name
  ) M Group By M.branch_name
) S On S.branch_name = T.branch_name