与分组有关的问题 [英] Problems with grouping

问题描述

 输出示例
 
如何获得像这样的输出，可以抑制公司名称行中的重复项？ ：
 
参考公司名称总电话姓氏电话没有电话
 ----- ------------------- --- -------- ------------ ---------- ------- 
 135 Dasher Services 12 Clarke 5 
赖特4 
特纳3 
 142拨弦航运12山5 
约翰逊5 
管家2 
杰克逊0 ******* 
 107胡安和Co. 12白色4 
特纳3 
克拉克3 
 Roberts 2 
  

查询：

  SELECT CUSTOMERS.CUSTOMER_ID as REF，
 CUSTOMERS.COMPANY_NAME，
 COUNT（CALLS.CALL_REF）AS TOTAL_CALLS，
 CALLERS.CALLER_LAST_NAME AS LAST_NAME，
 COUNT （CALLS.CALL_REF）AS CALLS 
 FROM CUSTOMERS 
 INNER JOIN CALLERS 
 ON COMPANY_ID = CUSTOMER_ID 
 LEFT JOIN CALLS 
 ON CALLERS.CONTACT_ID = CALLS.CONTACT_ID 
 GROUP BY CUSTOMERS.CUSTOMER_ID，
 CUSTOMERS.COMPANY_NAME，
 CALLERS.CALLER_LAST_NAME 
 ORDER BY REF，COMPANY_NAME，CALLS DESC; 
  

会产生以下结果：

  REF COMPANY_NAME TOTAL_CALLS LAST_NAME CALLS 
 ---------- ------------------------ ------ ----------- -------------------- ---------- 
 100宿务服务4安德森4 
 100宿营服务4 Penrice 4 
 100宿营服务3 Best 3 
 100宿营服务2 Williams 2 $ b $ 100宿营服务2 Lewis 2 
 101 Genus Ltd. 4 Phillips 4 
 101 Genus Ltd. 3 Parker 3 
 101 Genus Ltd. 2 Davies 2 
 101 Genus Ltd. 2 Johnson 2 
 102 Corps Ltd. 4 Gritten 4 $ b $ 102 Corps Ltd. 2 Scott 2 
 103列车服务7 Lawson 7 
 103列车服务2 Roberts 2 
  

SQLFiddle here

解决方案

我没有测试过，但它必须工作：

  SELECT case超过
（CUSTOMERS.COMPANY_NAME $ b分区）时的滞后（CUSTOMERS.COMPANY_NAME，1，null） $ b order by COMPANY_NAME，COUNT（CALLS.CALL_REF）DESC）为空
，然后CUSTOMERS.COMPANY_NAME else null结束cmp_name，
 CUSTOMERS.CUSTOMER_ID AS REF， 
 CUSTOMERS.COMPANY_NAME，
 COUNT（CALLS.CALL_REF）作为TOTAL_CALLS，
 CALLERS.CALLER_LAST_NAME AS LAST_NAME，
 COUNT（CALLS.CALL_REF）当从客户拨打
 
 INNER JOIN CALLERS 
 ON COMPANY_ID = CUSTOMER_ID 
 LEFT JOIN CALLS 
 ON CALLERS.CONTACT_ID = CALLS.CONTACT_ID 
 GROUP BY CUSTOMERS.CUSTOMER_ID，
 CUSTOMERS.COMPANY_NAME ，
 CALLERS.CALLER_LAST_NAME 
 ORDER BY REF，COMPANY_NAME，CALLS DESC; 
  

How do i get an output like this where i can suppress duplicates in the company name row?..

Example output:

  Ref Company name        Total calls Last name         Calls No call
----- ------------------- ----------- ------------ ---------- -------
  135 Dasher Services              12 Clarke                5
                                      Wright                4
                                      Turner                3
  142 Diaphonic Shipping           12 Hill                  5
                                      Johnson               5
                                      Butler                2
                                      Jackson               0 *******
  107 Juan and Co.                 12 White                 4
                                      Turner                3
                                      Clarke                3
                                      Roberts               2

Query:

SELECT CUSTOMERS.CUSTOMER_ID AS REF,
       CUSTOMERS.COMPANY_NAME,
       COUNT (CALLS.CALL_REF) AS TOTAL_CALLS,
       CALLERS.CALLER_LAST_NAME AS LAST_NAME,
       COUNT(CALLS.CALL_REF)AS CALLS
  FROM CUSTOMERS
  INNER JOIN CALLERS
    ON COMPANY_ID=CUSTOMER_ID
  LEFT JOIN CALLS
    ON CALLERS.CONTACT_ID=CALLS.CONTACT_ID
  GROUP BY CUSTOMERS.CUSTOMER_ID,
           CUSTOMERS.COMPANY_NAME,
           CALLERS.CALLER_LAST_NAME
  ORDER BY REF, COMPANY_NAME, CALLS DESC;

produces these results:

       REF COMPANY_NAME                   TOTAL_CALLS LAST_NAME                 CALLS
---------- ------------------------------ ----------- -------------------- ----------
       100 Haunt Services                           4 Anderson                      4 
       100 Haunt Services                           4 Penrice                       4 
       100 Haunt Services                           3 Best                          3 
       100 Haunt Services                           2 Williams                      2 
       100 Haunt Services                           2 Lewis                         2 
       101 Genus Ltd.                               4 Phillips                      4 
       101 Genus Ltd.                               3 Parker                        3 
       101 Genus Ltd.                               2 Davies                        2 
       101 Genus Ltd.                               2 Johnson                       2 
       102 Corps Ltd.                               4 Gritten                       4 
       102 Corps Ltd.                               2 Scott                         2 
       103 Train Services                           7 Lawson                        7 
       103 Train Services                           2 Roberts                       2 

SQLFiddle here

解决方案

I hadn't tested, but it has to work:

SELECT case when lag(CUSTOMERS.COMPANY_NAME, 1, null) over 
                 (partition by CUSTOMERS.COMPANY_NAME 
                  order by COMPANY_NAME, COUNT(CALLS.CALL_REF) DESC) is null 
            then CUSTOMERS.COMPANY_NAME else null end cmp_name,
       CUSTOMERS.CUSTOMER_ID AS REF,
       CUSTOMERS.COMPANY_NAME,
       COUNT (CALLS.CALL_REF) AS TOTAL_CALLS,
       CALLERS.CALLER_LAST_NAME AS LAST_NAME,
       COUNT(CALLS.CALL_REF)AS CALLS
  FROM CUSTOMERS
  INNER JOIN CALLERS
    ON COMPANY_ID=CUSTOMER_ID
  LEFT JOIN CALLS
    ON CALLERS.CONTACT_ID=CALLS.CONTACT_ID
  GROUP BY CUSTOMERS.CUSTOMER_ID,
           CUSTOMERS.COMPANY_NAME,
           CALLERS.CALLER_LAST_NAME
  ORDER BY REF, COMPANY_NAME, CALLS DESC;

这篇关于与分组有关的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！