oracle sql count(*)，行不匹配(oracle sql count(*) with rows not matched)
我有一个oracle查询：
select to_char(te.HORA, 'hh24:mi') HORARIO, count(1) CANTIDAD
from db.t_error te
where (te.error LIKE 'ERR-108' or te.error LIKE 'ERR-256')
and te.HORA >= to_HORA('29-07-2016 18:50', 'dd-mm-yyyy hh24:mi')
and te.HORA <= to_HORA('29-07-2016 19:00', 'dd-mm-yyyy hh24:mi')
group by to_char(te.HORA, 'hh24:mi')
order by to_char(te.HORA, 'hh24:mi');
结果(表)：
HORARIO | CANTIDAD
18:53 2
18:56 2
18:58 1
18:59 1
但我需要结果包括所有分钟(表格)：
HORARIO | CANTIDAD
18:50 0
18:51 0
18:52 0
18:53 2
18:54 0
18:55 0
18:56 2
18:57 0
18:58 1
18:59 1
19:00 0
使用0值计算分钟到分钟或计数(1)不匹配的结果。
我希望有所帮助！
谢谢。
I have a oracle query:
select to_char(te.HORA, 'hh24:mi') HORARIO, count(1) CANTIDAD
from db.t_error te
where (te.error LIKE 'ERR-108' or te.error LIKE 'ERR-256')
and te.HORA >= to_HORA('29-07-2016 18:50', 'dd-mm-yyyy hh24:mi')
and te.HORA <= to_HORA('29-07-2016 19:00', 'dd-mm-yyyy hh24:mi')
group by to_char(te.HORA, 'hh24:mi')
order by to_char(te.HORA, 'hh24:mi');
Result (Table):
HORARIO | CANTIDAD
18:53 2
18:56 2
18:58 1
18:59 1
but I need Results to include all minutes (table):
HORARIO | CANTIDAD
18:50 0
18:51 0
18:52 0
18:53 2
18:54 0
18:55 0
18:56 2
18:57 0
18:58 1
18:59 1
19:00 0
Count minute to minute with 0 values or not matched result for count(1).
I hope help!
Thanks.
原文：https://stackoverflow.com/questions/38668229
更新时间：2019-12-30 05:58
最满意答案
这不是一个好的解决方案(它应该被实现为直接使用您的代码，而不是作为附加组件)，但它说明了应该如何完成。
在这种情况下， to_date将时间(使用hh24:mi格式模型)添加到当月的第一天，但是你不关心这个，因为你提取小时和分钟并丢弃其余的。
在代码中修复的最重要的事情是使用字符串。 最好将所有内容放在日期数据类型中，按日期数据类型(到分钟)，并且仅在最后使用to_char()用于显示目的。 如果您需要帮助，请回信。
with
your_table ( horario, cantidad ) as (
select '18:53', 2 from dual union all
select '18:56', 2 from dual union all
select '18:58', 1 from dual union all
select '18:59', 1 from dual
),
all_times ( horario ) as (
select to_char( to_date('18:50', 'hh24:mi') + (level - 1) / (24 * 60), 'hh24:mi')
from dual
connect by level <= 1 + (to_date('19:00', 'hh24:mi') -
to_date('18:50', 'hh24:mi')) * 24 * 60
)
select a.horario, nvl(y.cantidad, 0) as cantidad
from all_times a left outer join your_table y
on a.horario = y.horario
order by horario
;
HORARIO CANTIDAD
------- ----------
18:50 0
18:51 0
18:52 0
18:53 2
18:54 0
18:55 0
18:56 2
18:57 0
18:58 1
18:59 1
19:00 0
11 rows selected.
编辑 ：
对于没有额外工作的“廉价”解决方案，您可以将原始查询直接插入“your_table”因子子查询中，就像这样(但我没有您的基表，所以我无法测试)。
with
your_table ( horario, cantidad ) as (
select to_char(te.HORA, 'hh24:mi') HORARIO, count(1) CANTIDAD
from db.t_error te
where (te.error LIKE 'ERR-108' or te.error LIKE 'ERR-256')
and te.HORA >= to_HORA('29-07-2016 18:50', 'dd-mm-yyyy hh24:mi')
and te.HORA <= to_HORA('29-07-2016 19:00', 'dd-mm-yyyy hh24:mi')
group by to_char(te.HORA, 'hh24:mi')
),
all_times ( horario ) as (
select to_char( to_date('18:50', 'hh24:mi') + (level - 1) / (24 * 60), 'hh24:mi')
from dual
connect by level <= 1 + (to_date('19:00', 'hh24:mi') -
to_date('18:50', 'hh24:mi')) * 24 * 60
)
select a.horario, nvl(y.cantidad, 0) as cantidad
from all_times a left outer join your_table y
on a.horario = y.horario
order by horario
;
This is not a good solution (it should be implemented to work directly with your code, not as an add-on), but it illustrates how this should be done.
to_date in this context adds the time (using the hh24:mi format model) to the first day of the current month, but you don't care about that since you extract the hour and minute and discard the rest anyway.
Biggest thing to fix in your code is that you work with strings. Best to put everything in date datatype, group by date datatype (to the minute), and only use to_char() for display purposes at the very end. Please write back if you need help with that.
with
your_table ( horario, cantidad ) as (
select '18:53', 2 from dual union all
select '18:56', 2 from dual union all
select '18:58', 1 from dual union all
select '18:59', 1 from dual
),
all_times ( horario ) as (
select to_char( to_date('18:50', 'hh24:mi') + (level - 1) / (24 * 60), 'hh24:mi')
from dual
connect by level <= 1 + (to_date('19:00', 'hh24:mi') -
to_date('18:50', 'hh24:mi')) * 24 * 60
)
select a.horario, nvl(y.cantidad, 0) as cantidad
from all_times a left outer join your_table y
on a.horario = y.horario
order by horario
;
HORARIO CANTIDAD
------- ----------
18:50 0
18:51 0
18:52 0
18:53 2
18:54 0
18:55 0
18:56 2
18:57 0
18:58 1
18:59 1
19:00 0
11 rows selected.
Edited:
For a "cheap" solution with no extra effort, you can plug your original query directly into the "your_table" factored subquery, like so (but I don't have your base tables, so I can't test).
with
your_table ( horario, cantidad ) as (
select to_char(te.HORA, 'hh24:mi') HORARIO, count(1) CANTIDAD
from db.t_error te
where (te.error LIKE 'ERR-108' or te.error LIKE 'ERR-256')
and te.HORA >= to_HORA('29-07-2016 18:50', 'dd-mm-yyyy hh24:mi')
and te.HORA <= to_HORA('29-07-2016 19:00', 'dd-mm-yyyy hh24:mi')
group by to_char(te.HORA, 'hh24:mi')
),
all_times ( horario ) as (
select to_char( to_date('18:50', 'hh24:mi') + (level - 1) / (24 * 60), 'hh24:mi')
from dual
connect by level <= 1 + (to_date('19:00', 'hh24:mi') -
to_date('18:50', 'hh24:mi')) * 24 * 60
)
select a.horario, nvl(y.cantidad, 0) as cantidad
from all_times a left outer join your_table y
on a.horario = y.horario
order by horario
;
2016-07-29
相关问答
只需添加COUNT(*)分析函数即可 select
userid,amount,term,apr, cnt
from
( select userid, amount, term, apr
RANK() OVER (PARTITION BY userid ORDER BY amount,term,apr) amount_rank,
COUNT(*) OVER (PARTITION BY userid) cnt
from
tableXYZ
)
where am
...
是的，您可以使用listagg() ，然后使用另一个group by ： select products, count(*)
from (select listagg(product) within group (order by product) as products
from t
group by area, store
) p
group by products;
Yes, you can use listagg() and then another g
...
你不能遵循该查询，因为它是用于sqlserver而不是oracle。 它在oracle中更简单，不需要自己加入。 此更新将仅根据数据表中的帐号数量设置id 1625的计数。 在此处查看演示; http://sqlfiddle.com/#!4/d154c/1 update dataTable a
set ACCR = 15481,
ACCF = 3,
a.ACCX = (
select COUNT(*)
from dataTable b
...
这不是一个好的解决方案(它应该被实现为直接使用您的代码，而不是作为附加组件)，但它说明了应该如何完成。 在这种情况下， to_date将时间(使用hh24:mi格式模型)添加到当月的第一天，但是你不关心这个，因为你提取小时和分钟并丢弃其余的。 在代码中修复的最重要的事情是使用字符串。 最好将所有内容放在日期数据类型中，按日期数据类型(到分钟)，并且仅在最后使用to_char()用于显示目的。 如果您需要帮助，请回信。 with
your_table ( horario, cantidad
...
当您想要在输出中获取数据时，即使表中没有匹配的数据，您也应该考虑LEFT JOIN 。 这个查询会做到这一点。 即使表中没有匹配项，它也能确保您获得搜索条件的行。 SELECT * FROM (
WITH search_values AS ( SELECT 'col2_value_you_want' col2_value, 'col3_value_you_want' col3_value FROM DUAL )
select COUNT(yt.some_non_null_column) CNT
...
你会有这样的事情： declare
v_cnt number;
begin
with input as (
select id,date,quantity
from abc a join
xyz z
.......
)
select count(*) into v_cnt
from input t
where .....;
end;
You
...
好吧，首先，我会编写一个查询，生成要更新和/或插入的行： WITH performance AS (SELECT 1234 student_id, 5678 course_id, 'Mandatory' enrollment_type, 70 mark, 'ACTIVE' status, 2 VERSION FROM dual UNION ALL
SELECT 1234 student_id, 5678 course_id, 'Optional
...
您可以首先查询每个客户计算他购买'apple' ，其中： SELECT id,COUNT(*) AS total
FROM transactions
WHERE type = 'apple'
GROUP BY id
现在我们只需要在DESC结束顺序中对该total进行ORDER BY ，并返回第一行与FETCH FIRST n ROWS ONLY ，如： SELECT id,COUNT(*) AS total
FROM transactions
WHERE type = 'apple'
GROU
...
什么是永远不会有效的是当前的方法是将表的名称和列的名称设置为以下代码段中的PreparedStatement： String SQLStatement = null;
...
SQLStatement = "SELECT count(*) FROM ? WHERE ? = ?";
ps = conn.prepareStatement(SQLStatement);
ps.setString(1, DBtablename);
ps.setString(2, DBStatus);
...
你不能直接用distinct做到这一点。 您可以在子查询中添加分析count() ，然后应用distinct的子查询，但使用聚合count()和group by更简单。 您必须在group by子句中包含所有现有的选择列表项。 所以，如果你有一个像这样的表： create table t42 (col1 number, col2 varchar2(10));
insert into t42 values (42, 'AAA');
insert into t42 values (42, 'AAA')
...
386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
