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

Nandhini Devi 发表于 Dev

Nandhini Devi

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

 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 | vellore | nad | 123

2 | 112 | vellore | | 123

3 | 113 |钦奈| ji | 121

4 | 112 | vellore | 你好 123

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

6 | 134 | 电视| 乔| 145

掌握细节

ordermasterid | 用户身份

112 | 12

113 | 13

134 | 14

Vignesh Kumar A

尝试这个

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的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