文章/答案/技术大牛

发布

社区首页 >问答首页 >如何使两个SELECT语句的结果出现在SQL的相邻列中？

问如何使两个SELECT语句的结果出现在SQL的相邻列中？
EN

Stack Overflow用户

提问于 2019-12-02 17:47:12

回答 1查看 88关注 0票数 0

我正在使用SQL oracle。我在一张桌子上查询了两种类型的物品：

第一次查询：

SELECT SUM(OLI.QUANTITY) AS "SPECIALITY ITEMS ORDERED - SEPT 2019"
FROM ORDER_LINE_ITEM OLI, CUSTOMER_ORDER CO
WHERE CO.ORDER_ID = OLI.ORDER_ID
AND OLI.ORDER_KIND = 'SPL'
AND CO.ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
AND CO.ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')

输出：

SPECIALITY ITEMS ORDERED - SEPT 2019
------------------------------------
            111

第二次查询：

SELECT SUM(OLI.QUANTITY) AS "BUILD YOUR OWN ITEMS ORDERED - SEPT 2019"
FROM ORDER_LINE_ITEM OLI, CUSTOMER_ORDER CO
WHERE CO.ORDER_ID = OLI.ORDER_ID
AND OLI.ORDER_KIND = 'BYO'
AND CO.ORDER_DATE >= TO_DATE('01 SEPTEMBER 2019', 'DD MONTH YYYY')
AND CO.ORDER_DATE <= TO_DATE('30 SEPTEMBER 2019', 'DD MONTH YYYY')

输出：

BUILD YOUR OWN ITEMS ORDERED - SEPT 2019
----------------------------------------
            49

我希望显示这两个查询的结果，并将输出作为相邻的两个列反映如下：

SPECIALITY ITEMS ORDERED - SEPT 2019    |    BUILD YOUR OWN ITEMS ORDERED - SEPT 2019
------------------------------------    |    ----------------------------------------
            111                         |                     49

我该怎么做？

sql

oracle

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-12-02 17:52:04

就像这样：

select sum(case when oli.order_kind = 'SPL' then oli.quantity else 0 end) result_1,
       sum(case when oli.order_kind = 'BYO' then oli.quantity else 0 end) result_2
from order_line_item oli, customer_order co
where co.order_id = oli.order_id
  and oli.order_kind in ('SPL', 'BYO')
  and co.order_date >= to_date('01 SEPTEMBER 2019', 'DD MONTH YYYY')
  and co.order_date <= to_date('30 SEPTEMBER 2019', 'DD MONTH YYYY')

另一个选项(不是我推荐的)是重复使用查询，如下所示：

select 
  (select sum(oli.quantity)
   from order_line_item oli, customer_order co
   where co.order_id = oli.order_id
     and oli.order_kind = 'SPL'
     and co.order_date >= to_date('01 SEPTEMBER 2019', 'DD MONTH YYYY')
     and co.order_date <= to_date('30 SEPTEMBER 2019', 'DD MONTH YYYY')
  ) result_1,
--
  (select sum(oli.quantity)
   from order_line_item oli, customer_order co
   where co.order_id = oli.order_id
     and oli.order_kind = 'BYO'
     and co.order_date >= to_date('01 SEPTEMBER 2019', 'DD MONTH YYYY')
     and co.order_date <= to_date('30 SEPTEMBER 2019', 'DD MONTH YYYY')
  ) result_2
from dual;

或者，使用CTE：

with 
t_first as 
  (select sum(oli.quantity) result_1
   from order_line_item oli, customer_order co
   where co.order_id = oli.order_id
     and oli.order_kind = 'SPL'
     and co.order_date >= to_date('01 SEPTEMBER 2019', 'DD MONTH YYYY')
     and co.order_date <= to_date('30 SEPTEMBER 2019', 'DD MONTH YYYY')
  ),
--
t_second as
(select sum(oli.quantity) result_2
   from order_line_item oli, customer_order co
   where co.order_id = oli.order_id
     and oli.order_kind = 'BYO'
     and co.order_date >= to_date('01 SEPTEMBER 2019', 'DD MONTH YYYY')
     and co.order_date <= to_date('30 SEPTEMBER 2019', 'DD MONTH YYYY')
  ) 
select a.result_1, b.result_2
from t_first a cross join t_second b;

最后，虽然Oracle在使用您使用的联接时工作得很好，但现在建议遵循最新的ANSI语法和显式联接，即

select ...
from order_line_item oli join customer_order co on co.order_id = oli.order_id
where ...

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/59144181

复制

相似问题

问如何使两个SELECT语句的结果出现在SQL的相邻列中？
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使两个SELECT语句的结果出现在SQL的相邻列中？EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使两个SELECT语句的结果出现在SQL的相邻列中？
EN