从两个查询输出到一个 [英] Out put from two queries into one

问题描述

亲爱的所有



我有两个疑问

dear All

I have two queries

select a.brcd,round(sum(outamt)/1000,0) amt from cis 
group by a.brcd order by amt desc
)


and 

<pre>select brcd, round(sum(outamt)/1000,0) amt from cis  where   assetcd !='IR00001' group by a.brcd order by amt desc

如何合并两个查询以便



来自带有区域的表格明智的分店销售，

我希望选择十大分行，

销售总额与最大销售额不包括项目<给定代码>和这些分店的总销售额



我尝试过：

how to merge two queries so that

From table with region wise branch sales,
I wish to select top ten branches with ,
sum of sales with maximum sales excluding an item< given code> and total sale of those branches

What I have tried:

select * from(
    select a.brcd,
    round(sum(b.outamt)/1000,0) sale,
    round(sum(a.outamt)/1000,0) totalsale from cis a
    inner join
    (select brcd, outamt from cis  where   assetcd !='IR00001') b 
    on b.brcd=a.brcd
    group by a.brcd order by sale desc
)  where    rownum <11;

推荐答案

--I hope this solution may help you
select 
   A.brcd,
   round(sum(A.outamt)/1000,0) amt1,
   (
     select round(sum(B.outamt)/1000,0)   FROM cis AS B
        where  
           B.assetcd<>'IR00001' 
       AND B.rownum <11
       AND A.brcd=B.brcd
  ) AS Amt2  
from cis AS A  group by A.brcd order by amt1 DESC,Amt2 DESC

使用以下查询解决

resolved by using following query

select * from
 (select * from
(select brcd, round(sum(outamt)/1000,0) amt from cis  where   assetcd !='IR00001'
group by brcd order by amt desc)  where    rownum <11)  a

inner join

(select brcd,round(sum(outamt)/1000,0) amt2  from cis  group by brcd) b

on a.brcd=b.brcd
order by amt desc
;

任何更好的方法。

any better way to do so.

我的问题



100个分店销售3件商品。我们必须找到10个最大销售分支，其中销售金额不包括一项（2项）



以及



这些分支机构的总销售额（全部3项）

my problem

100 branches selling 3 items. we have to find 10 branches with maximum sale where sale amount exclude of one item(2 items)

and also

total sale of those branches(all 3 items )

这篇关于从两个查询输出到一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！