在 Oracle 中的单个查询中的多个计数

标签: sql Oracle
发布时间: 2017/4/15 18:46:07
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

是有简单的方法来实现此查询的结果,以提高其性能。我不喜欢联接两个表的每个选择。

select Product, LCTL, LQ, LCTQL from
(
(select 'Phone / HSI' as Product, (select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='m ECOMM ORD') as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD')) as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD' or c.category_long_name='m ECOMM ORD')) as LCTQL from dual
union all

select '(Phone / HSI) + Prism' as Product,(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='m ECOMM PRISM') as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='CRIS ECOMM PRISM') as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM PRISM' or c.category_long_name='CRIS ECOMM PRISM')) as LCTQL from dual

union all
select '(Phone / HSI) + DTV' as Product,(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='m ECOMM DTV') as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and c.category_long_name='CRIS ECOMM DTV') as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM DTV' or c.category_long_name='CRIS ECOMM DTV')) as LCTQL from dual

union all
select 'Total' as Product,(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM DTV' or c.category_long_name='m ECOMM PRISM' or c.category_long_name='m ECOMM DTV')) as LCTL,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='CRIS ECOMM DTV' or c.category_long_name='CRIS ECOMM PRISM' or c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD')) as LQ,
(select count(1) as "LCTL" from tasks t,task_categories c where t.category_id=c.category_id and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))>=?1 and to_date(to_char(t.user_create_date,'DD-MON-YYYY'))<=?2 and (c.category_long_name='m ECOMM DTV' or c.category_long_name='m ECOMM PRISM' or c.category_long_name='m ECOMM DTV' or c.category_long_name='CRIS ECOMM DTV' or c.category_long_name='CRIS ECOMM PRISM' or c.category_long_name='CRIS ECOMM ORD' or c.category_long_name='CRIS ECOMM HELD' )) as LCTQL from dual)
)

输出应如下所示︰

Product                 LCTL    LQ      LCTQL
Phone / HSI         17209   39849   57058
(Phone / HSI) + Prism   4095    6   4101
(Phone / HSI) + DTV 6192    1   6193


Total   10287   39856   50143

解决方法 1:

我认为您的查询可以在这样写︰

SELECT
  product,
  SUM(CASE WHEN c.category_long_name  = 'm ECOMM ORD'      THEN 1 ELSE 0 END) AS LCTL,
  SUM(CASE WHEN c.category_long_name  = 'CRIS ECOMM ORD' 
             OR c.category_long_name  = 'CRIS ECOMM HELD'  THEN 1 ELSE 0 END) AS LQ,
  SUM(CASE WHEN c.category_long_name IN('CRIS ECOMM ORD',
                                        'CRIS ECOMM HELD',
                                        'm ECOMM ORD')     THEN 1 ELSE 0 END) AS LCTQ,
  SUM(CASE WHEN c.category_long_name = 'm ECOMM PRISM'     THEN 1 ELSE 0 END) AS lccc,
  ...
  ...
  COUNT(*) AS "Total"
FROM tasks t
INNER JOIN task_categories c ON t.category_id=c.category_id
WHERE to_date(to_char(t.user_create_date, 'DD-MON-YYYY')) >= ?1
  and to_date(to_char(t.user_create_date, 'DD-MON-YYYY')) <= ?2
GROUP BY product;

使用 CASE 的表达,与 GROUP BY product ,你可以在一个查询中。这是基本的想法。

此外,您可能需要使用 pivot 运算符在oracle,但它尚不清楚是否有可能要写入使用 pivot 运算符或不。

赞助商