使用内部联接在oracle中获取特定的列值计数 [英] Get a particular column value count in oracle with inner join

问题描述

我通过加入两个表和值来获得2个表

I am having 2 tables by joining both i am getting some columns and values

 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个. 例如:

I want to get number of times a particular branch name came.I used count but its not working and i want only max number of times the branch name came and top 3 to be displayed . Eg:

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

它应该只显示

vellore
harithwar 
tvl 

样本数据 订单详情

masterdetails

masterdetails

推荐答案

尝试一下

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

更新

如果要获得count的Max

尝试一下

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

这篇关于使用内部联接在oracle中获取特定的列值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！