在明天或明智或月份的oracle中汇总数据

本文介绍了在明天或明智或月份的oracle中汇总数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

大家好， 我在上一期的问题中无法解释我的问题所以我发布了一个新问题。 我有一张桌子： 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[^]