Oracle SQL Group By if [英] Oracle SQL Group By if

查看：102
本文介绍了Oracle SQL Group By if的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在我的应用程序中，我在下表中记录了打开的文件： 

  TESTID SITE LATEST_READ READ_COUNT FILE_ORIGIN_ID 
  - ------------ ---------- ----------- ---------- ------- ------- 
 File1 | Site1 | 02/05/13 | 2 | 1 
 File1 | Site2 | 22/01/14 | 3 | 2 
 File2 | Site1 | 02/06/14 | 8 | 0 
 File3 | Site1 | 19/09/14 | 17 | 0 
 File4 | Site2 | 19/09/14 | 14 | 2 
 File4 | Site2 | 19/09/14 | 34 | 1 
 File4 | Site3 | 19/09/14 | 10 | 0 
 File5 | Site2 | 19/09/14 | 44 | 2 
 File5 | Site3 | 19/09/14 | 1 | 2

我想获得每个文件读取计数的总和，如果至少有一个 FILE_ORIGIN_ID 对于该文件与 2 不同。



这个例子应该给： 

  TESTID SITE LATEST_READ SUM FILE_ORIGIN_ID 
 -------------  - --------- ----------- ---------- -------------- 
 File1 | Site1 | 02/05/13 | 5 | 1 
 File2 | Site1 | 02/06/14 | 8 | 0 
 File3 | Site1 | 19/09/14 | 17 | 0 
 File4 | Site2 | 19/09/14 | 58 | X<  - 可以是0或1 
 File5 | Site2 | 19/09/14 | 44 | 2 
 File5 | Site3 | 19/09/14 | 1 | 2

我尝试了以下操作：

  SELECT TESTID，SUM（READ_COUNT），LATEST_READ，FILE_ORIGIN_ID，site 
 FROM FILE_USAGE_LOG 
 GROUP BY TESTID，TESTID，LATEST_READ，
 CASE 
当FILE_ORIGIN_ID<> '2'然后1 
 ELSE 0 
 END，site 
 ORDER BY TESTID;

但是它没有做我想做的事情......我该如何改进？我怎样才能在分组行的情况下将 FILE_ORIGIN_ID 设置为0或1

解决方案

div>

SQL小提琴



Oracle 11g R2架构设置：

$ p $ CREATE TABLE FILE_USAGE_LOG（TESTID，SITE，LATEST_READ， READ_COUNT，FILE_ORIGIN_ID）AS
SELECT'File1'，'Site1'，DATE'2013-05-02'，2，1 FROM DUAL
UNION ALL SELECT'File1'，'Site2'，DATE'2014 -01-22'，3，2 FROM DUAL
UNION ALL SELECT'File2'，'Site1'，DATE'2014-06-02'，8，0 FROM DUAL
UNION ALL SELECT'File3' ，'Site1'，DATE'2014-09-19'，17，0 FROM DUAL
UNION ALL SELECT'File4'，'Site2'，DATE'2014-09-19'，14，2 FROM DUAL
UNION ALL SELECT'File4'，'Site2'，DATE'2014-09-19'，34，1 FROM DUAL
UNION ALL SELECT'File4'，'Site3'，DATE'2014-09-19' ，10，0从DUAL
UNION ALL SELECT'File5'，'Site2'，DATE'2014-09-19'，44，2 FROM DUAL
UNION ALL SELECT'File5'，'Site3'，DATE'2014-09-19'，1 ，2 FROM DUAL;

查询1 ：

  SELECT TESTID，
 REGEXP_REPLACE（
 LISTAGG（SITE，'，'）
 WITHIN GROUP（ORDER BY SITE），
 '（[^，] +）（，\ 1）+（$ |，）'，
'\1\3'
）AS SITES，
 MAX（LATEST_READ） AS LATEST_READ，
 SUM（READ_COUNT）AS Total_Read_Count 
 FROM FILE_USAGE_LOG 
 GROUP BY 
 TESTID 
 HAVING COUNT（CASE FILE_ORIGIN_ID WHEN 2 THEN NULL ELSE 1 END）> 0 
 UNION ALL 
 SELECT TESTID，
 SITE，
 LATEST_READ，
 READ_COUNT 
 FROM FILE_USAGE_LOG l 
 WHERE FILE_ORIGIN_ID = 2 
 AND NOT EXISTS（SELECT'X'
 FROM FILE_USAGE_LOG x 
 WHERE x.TESTID = l.TESTID 
 AND x.FILE_ORIGIN_ID<> 2 
）
 ORDER BY 1,2

结果 ： 

  TESTID |网站| LATEST_READ | TOTAL_READ_COUNT | 
 | -------- | -------------- | --------------------- -------- | ------------------ | 
 | File1 | Site1，Site2 | 2014年1月22日00:00:00 | 5 | 
 | File2 | Site1 |六月，02 2014 00:00:00 | 8 | 
 | File3 | Site1 | 2014年9月19日00:00:00 | 17 | 
 | File4 | Site2，Site3 | 2014年9月19日00:00:00 | 58 | 
 | File5 | Site2 | 2014年9月19日00:00:00 | 44 | 
 | File5 | Site3 | 2014年9月19日00:00:00 | 1 |

查询2 ：

  SELECT TESTID，
 REGEXP_REPLACE（
 LISTAGG（SITE，'，'）
 WITHIN GROUP（ORDER BY SITE），
 '（[^，] +）（，\ 1）+（$ |，）'，
'\1\3'
）AS SITES，
 MAX（LATEST_READ） AS LATEST_READ，
 SUM（READ_COUNT）AS Total_Read_Count 
 FROM FILE_USAGE_LOG 
 WHERE TESTID NOT LIKE'this％'
 AND LATEST_READ BETTING'DATE'2014-01-01'and DATE'2014 -12-31'
 GROUP BY 
 TESTID 
 HAVING COUNT（CASE FILE_ORIGIN_ID 2 then NULL ELSE 1 END）> 0 
 UNION ALL 
 SELECT TESTID，
 SITE，
 LATEST_READ，
 READ_COUNT 
 FROM FILE_USAGE_LOG l 
 WHERE FILE_ORIGIN_ID = 2 
 AND NOT EXISTS（SELECT'X'
 FROM FILE_USAGE_LOG x 
 WHERE x.TESTID = l.TESTID 
 AND x.FILE_ORIGIN_ID<> 2 
 AND TESTID NOT LIKE'this ％'
 AND LATEST_READ BETWEEN DATE'2014-01-01'and DATE'2014-12-31'
）
和TESTID NOT LIKE'this％'
 AND LATEST_READ BETWEEN DATE'2014-01-01'AND DATE'2014-12-31'
 ORDER BY 1,2

结果 ：

  | TESTID |网站| LATEST_READ | TOTAL_READ_COUNT | 
 | -------- | -------------- | --------------------- -------- | ------------------ | 
 | File1 | Site2 | 2014年1月22日00:00:00 | 3 | 
 | File2 | Site1 |六月，02 2014 00:00:00 | 8 | 
 | File3 | Site1 | 2014年9月19日00:00:00 | 17 | 
 | File4 | Site2，Site3 | 2014年9月19日00:00:00 | 58 | 
 | File5 | Site2 | 2014年9月19日00:00:00 | 44 | 
 | File5 | Site3 | 2014年9月19日00:00:00 | 1 |


In my application I log the file opening in the following table:

TESTID        SITE       LATEST_READ READ_COUNT FILE_ORIGIN_ID
------------- ---------- ----------- ---------- --------------
File1        |Site1     |02/05/13   |         2|             1 
File1        |Site2     |22/01/14   |         3|             2 
File2        |Site1     |02/06/14   |         8|             0 
File3        |Site1     |19/09/14   |        17|             0 
File4        |Site2     |19/09/14   |        14|             2 
File4        |Site2     |19/09/14   |        34|             1  
File4        |Site3     |19/09/14   |        10|             0 
File5        |Site2     |19/09/14   |        44|             2  
File5        |Site3     |19/09/14   |         1|             2

I want to get the sum of the read count per file if at least one of the FILE_ORIGIN_ID for the file is different from 2.

This example should give:

TESTID        SITE       LATEST_READ SUM        FILE_ORIGIN_ID
------------- ---------- ----------- ---------- --------------
File1        |Site1     |02/05/13   |         5|             1 
File2        |Site1     |02/06/14   |         8|             0 
File3        |Site1     |19/09/14   |        17|             0 
File4        |Site2     |19/09/14   |        58|             X <-- can be 0 or 1 
File5        |Site2     |19/09/14   |        44|             2  
File5        |Site3     |19/09/14   |         1|             2

I've tried with the following:

SELECT TESTID, SUM(READ_COUNT), LATEST_READ, FILE_ORIGIN_ID, site
FROM FILE_USAGE_LOG 
GROUP BY TESTID, TESTID, LATEST_READ, 
          CASE 
            WHEN FILE_ORIGIN_ID <> '2' Then 1
            ELSE 0
          END, site
ORDER BY TESTID;

But it's not doing what I want to do... How can I improve this? And how can I, in case of grouped lines set the FILE_ORIGIN_ID to 0 or 1

解决方案

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE FILE_USAGE_LOG (TESTID, SITE, LATEST_READ, READ_COUNT, FILE_ORIGIN_ID ) AS
          SELECT 'File1', 'Site1', DATE '2013-05-02', 2, 1 FROM DUAL
UNION ALL SELECT 'File1', 'Site2', DATE '2014-01-22', 3, 2 FROM DUAL
UNION ALL SELECT 'File2', 'Site1', DATE '2014-06-02', 8, 0 FROM DUAL
UNION ALL SELECT 'File3', 'Site1', DATE '2014-09-19', 17, 0 FROM DUAL
UNION ALL SELECT 'File4', 'Site2', DATE '2014-09-19', 14, 2 FROM DUAL
UNION ALL SELECT 'File4', 'Site2', DATE '2014-09-19', 34, 1 FROM DUAL
UNION ALL SELECT 'File4', 'Site3', DATE '2014-09-19', 10, 0 FROM DUAL
UNION ALL SELECT 'File5', 'Site2', DATE '2014-09-19', 44, 2 FROM DUAL
UNION ALL SELECT 'File5', 'Site3', DATE '2014-09-19', 1, 2 FROM DUAL;

Query 1:

SELECT  TESTID,
        REGEXP_REPLACE( 
          LISTAGG( SITE, ', ' )
            WITHIN GROUP( ORDER BY SITE ),
          '([^, ]+)(, \1)+($|, )',
          '\1\3'
        ) AS SITES, 
        MAX( LATEST_READ ) AS LATEST_READ,
        SUM(READ_COUNT) AS Total_Read_Count
FROM    FILE_USAGE_LOG 
GROUP BY
        TESTID
HAVING  COUNT( CASE FILE_ORIGIN_ID WHEN 2 THEN NULL ELSE 1 END ) > 0
UNION ALL
SELECT  TESTID,
        SITE,
        LATEST_READ,
        READ_COUNT
FROM    FILE_USAGE_LOG l
WHERE   FILE_ORIGIN_ID = 2
AND     NOT EXISTS ( SELECT 'X'
                     FROM   FILE_USAGE_LOG x
                     WHERE  x.TESTID      = l.TESTID
                     AND    x.FILE_ORIGIN_ID <> 2
                   )
ORDER BY 1,2

Results:

| TESTID |        SITES |                 LATEST_READ | TOTAL_READ_COUNT |
|--------|--------------|-----------------------------|------------------|
|  File1 | Site1, Site2 |   January, 22 2014 00:00:00 |                5 |
|  File2 |        Site1 |      June, 02 2014 00:00:00 |                8 |
|  File3 |        Site1 | September, 19 2014 00:00:00 |               17 |
|  File4 | Site2, Site3 | September, 19 2014 00:00:00 |               58 |
|  File5 |        Site2 | September, 19 2014 00:00:00 |               44 |
|  File5 |        Site3 | September, 19 2014 00:00:00 |                1 |

Query 2:

SELECT  TESTID,
        REGEXP_REPLACE( 
          LISTAGG( SITE, ', ' )
            WITHIN GROUP( ORDER BY SITE ),
          '([^, ]+)(, \1)+($|, )',
          '\1\3'
        ) AS SITES, 
        MAX( LATEST_READ ) AS LATEST_READ,
        SUM(READ_COUNT) AS Total_Read_Count
FROM    FILE_USAGE_LOG 
WHERE   TESTID NOT LIKE 'this%'
AND     LATEST_READ BETWEEN DATE '2014-01-01' AND DATE '2014-12-31'
GROUP BY
        TESTID
HAVING  COUNT( CASE FILE_ORIGIN_ID WHEN 2 THEN NULL ELSE 1 END ) > 0
UNION ALL
SELECT  TESTID,
        SITE,
        LATEST_READ,
        READ_COUNT
FROM    FILE_USAGE_LOG l
WHERE   FILE_ORIGIN_ID = 2
AND     NOT EXISTS ( SELECT 'X'
                     FROM   FILE_USAGE_LOG x
                     WHERE  x.TESTID      = l.TESTID
                     AND    x.FILE_ORIGIN_ID <> 2
                     AND    TESTID NOT LIKE 'this%'
                     AND    LATEST_READ BETWEEN DATE '2014-01-01' AND DATE '2014-12-31'
                   )
AND     TESTID NOT LIKE 'this%'
AND     LATEST_READ BETWEEN DATE '2014-01-01' AND DATE '2014-12-31'
ORDER BY 1,2

Results:

| TESTID |        SITES |                 LATEST_READ | TOTAL_READ_COUNT |
|--------|--------------|-----------------------------|------------------|
|  File1 |        Site2 |   January, 22 2014 00:00:00 |                3 |
|  File2 |        Site1 |      June, 02 2014 00:00:00 |                8 |
|  File3 |        Site1 | September, 19 2014 00:00:00 |               17 |
|  File4 | Site2, Site3 | September, 19 2014 00:00:00 |               58 |
|  File5 |        Site2 | September, 19 2014 00:00:00 |               44 |
|  File5 |        Site3 | September, 19 2014 00:00:00 |                1 |

这篇关于Oracle SQL Group By if的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆