在明天或明智或月份的oracle中汇总数据 [英] Aggregate data in oracle day wise or week wise or month wise

问题描述

大家好，



我在上一期的问题中无法解释我的问题所以我发布了一个新问题。





我有一张桌子：



ename | edate

emp1 | 01-10-17

emp1 | 01-10-17

emp1 | 01-10-17

emp1 | 02-10-17

emp2 | 01-10-17

emp3 | 01-10-17

emp3 | 01-10-17

emp3 | 02-10-17

emp3 | 02-10-17





旋转后的预期输出：

ename | 01-10-17 | 02-10-17

emp1 | 3 | 1

emp2 | 1 | 0

emp3 | 2 | 2



并且有条件，每天的计数应该大于1.



所以最终out put是

ename | 01-10-17 | 02-10-17

emp3 | 2 | 2



至于所有日期的emp 3计数都超过1.



我用过在oracle 11g中转动。但实际上这个查询将在10g上运行，我发现10g不支持pivot。



我尝试过：



oracle 11g，



SELECT * FROM

（

SELECT ename，edate

FROM tblName

）

PIVOT

（

COUNT（edate）

FOR edate in（'01 -oct-2017'，'02 -oct-2017'）

）

按顺序排序;



但是我找不到添加> 1条件的方法，而且现在我手动输入日期但是在实际情况下它们可以是任何多个日期。

解决方案

如果您只想获得count大于1的数据，您可以使用 HAVING子句 [ ^ ]将结果集限制为源o f pivot。

  SELECT  ename，edate，COUNT（*） AS  CountOfDates 
  FROM  tblName 
  GROUP   BY  ename，edate 
  HAVING  COUNT（*）> 1 

关于你的第二个问题：有一种方法可以在不输入日期的情况下创建数据透视表： oracle sql中的动态数据透视 - Stack Overflow [ ^ ]

所以，我会尝试（但我没有安装Oracle服务器）来改进引用的sql语句：

清除列
 COLUMN temp_in_statement new_value str_in_statement 
 SELECT DISTINCT 
 LISTAGG（''''|| edate || '''AS'|| edate，'，'）
 WITHIN GROUP（ORDER BY myLetter）AS temp_in_statement 
 FROM（SELECT DISTINCT edate FROM tblName）; 
 
  - 这应该以格式列出日期列表：< code> '01 -Oct-2017'为2017年10月1日< / code>等等...变成
 
 SELECT * 
 FROM（
 SELECT ename，edate，COUNT（*）AS CountOfDates 
 FROM tblName 
 GROUP BY ename，edate 
 HAVING COUNT（*）> 1 
）
 PIVOT（Sum（CountOfDates）AS val FOR edate IN（& str_in_statement））; 

注意： 但有一些限制：您只能连接一个最多4000个字节的字符串。



详情请见：

Oracle SQL * PLUS new_value参数 [ ^ ]

命令参考，14/52 [ ^

Hi All,

I could not explain my problem in my last question so I am posting a fresh question.


I have a table:

ename | edate
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 01-10-17
emp1 | 02-10-17
emp2 | 01-10-17
emp3 | 01-10-17
emp3 | 01-10-17
emp3 | 02-10-17
emp3 | 02-10-17


Expected output after pivoting:
ename | 01-10-17 | 02-10-17
emp1 | 3 | 1
emp2 | 1 | 0
emp3 | 2 | 2

and there is condition that count for each day should be greater than 1.

So final out put is
ename | 01-10-17 | 02-10-17
emp3 | 2 | 2

as for only emp 3 count for all days is more than 1.

I have used pivot in oracle 11g. But the actually this query will run on 10g and I found out that pivot is not supported in 10g.

What I have tried:

In oracle 11g,

SELECT * FROM
(
SELECT ename, edate
FROM tblName
)
PIVOT
(
COUNT(edate)
FOR edate in('01-oct-2017', '02-oct-2017')
)
ORDER BY ename;

But I cannot find way to add the "> 1" condition and also now I have typed the dates manually but in real situation they can be any multiple dates.

解决方案

[EDIT]
If you would like to get only these data where count is bigger than 1, you can use HAVING clause[^] to limit result set as a source of pivot.

SELECT ename, edate, COUNT(*) AS CountOfDates
FROM tblName
GROUP BY ename, edate
HAVING COUNT(*)>1

As to your second question: there's a way to create pivot table without typing dates: Dynamic pivot in oracle sql - Stack Overflow[^]
So, i would try (but i'm not having installed Oracle server) to improve referenced sql statements:

clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT 
    LISTAGG('''' || edate || ''' AS ' || edate,',')
        WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement 
    FROM (SELECT DISTINCT edate FROM tblName);

--this should put a list of dates in format: <code>'01-Oct-2017' as 01-Oct-2017</code> and so on...  into a variable

SELECT *
FROM (
    SELECT ename, edate, COUNT(*) AS CountOfDates
    FROM tblName
    GROUP BY ename, edate
    HAVING COUNT(*)>1
)
PIVOT (Sum(CountOfDates) AS val FOR edate IN (&str_in_statement));

Note: There are limitations though: You can only concatenate a string up to 4000 bytes.

For further details, please see:
Oracle SQL*PLUS new_value parameter[^]
Command Reference, 14 of 52[^]

这篇关于在明天或明智或月份的oracle中汇总数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！