sql语句,统计时数字不对去除数据的问题

发布网友发布时间：2022-05-02 13:52

共5个回答

懂视网时间：2022-05-02 18:13

SQL> explain plan for select a.object_name,b.object_id
from static_test a,static_test1 b
where a.object_id=b.object_id; 2 3

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1637592733

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 344 (0)| 00:00:05 |
| 1 | NESTED LOOPS | | 1 | 35 | 344 (0)| 00:00:05 |
| 2 | NESTED LOOPS | | 1 | 35 | 344 (0)| 00:00:05 |
| 3 | TABLE ACCESS FULL | STATIC_TEST1 | 1 | 5 | 343 (0)| 00:00:05 |
|* 4 | INDEX RANGE SCAN | STATIC_TEST_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| STATIC_TEST | 1 | 30 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

17 rows selected.

SQL执行异常系列之——统计信息不准

标签：loop object_id alt cpu 就会 from ops HERE 信息

热心网友时间：2022-05-02 15:21

select b.deptid,COUNT(userid) from Dept_user
where userid in(select id from user ) group by deptid desc

最后再连dept 表查一下,你自己测试检查一下

热心网友时间：2022-05-02 16:39

多对多？一个用户有多个部门？

热心网友时间：2022-05-02 18:14

select d.deptid, sum(t.userid) users
from dept d, (select .userid,.deptid from dept_user , user u
where .userid=u.userid
and u.isdel='0') t
where d.deptid = t.deptid

热心网友时间：2022-05-02 20:05

逻辑删除有标记的话，加一个where条件把逻辑删除的排除再计算不行吗？