select TerminalID,count(TerminalID) as count from Table where TERMINALID in ('72848','3DD35','42463') group by terminalid查询出来的结果是:
72848 14
3DD35 23但是没有42463的结果 我想要这样的结果:
72848 14
3DD35 23
42463 0 --->此处没有就补零
怎么写啊?求助
72848 14
3DD35 23但是没有42463的结果 我想要这样的结果:
72848 14
3DD35 23
42463 0 --->此处没有就补零
怎么写啊?求助
解决方案 »
- select count(distinct colname) from table1 提示标识符无效
- oracle系统自带的练习表有哪些,怎样找?
- 配置 Workflow Configuration Assistant出错
- 急求一数据库管理系统解决方案
- 两种返回记录集的方式有什么区别呢?是不是性能或者其它方面有区别?
- count与distinct用在一个语句中是否不太好
- 关于在存储过程中创建数据库用户,答者有分啦~~
- Oracle9i紧急求助!
- oracle数据库中非活动连接占用系统资源的问题。
- 哪位大虾把QQ留下,我有事请教!
- plsql 将date转换成varchar2
- 安装oracle9i时
这样应该可以解决你的问题了
如果in里面的东西比较多,你建一个参数表,将IN 里面的数据插入在里面,然后使用外连接,就可以满足你的要求。
SQL> with t as(
2 select '72848' terminalid,45 num from dual union all
3 select '72848',12 from dual union all
4 select '72848',14 from dual union all
5 select '3DD35',45 from dual union all
6 select '72848',70 from dual union all
7 select '3DD35',10 from dual union all
8 select '3DD35',98 from dual union all
9 select '72848',11 from dual)
10 select t2.terminalid,count(t1.num) cnt
11 from t t1,
12 (select decode(rownum, 1, '72848', 2, '3DD35', 3, '42463') terminalid
13 from dual connect by rownum < 3) t2
14 where t2.terminalid=t1.terminalid(+)
15 group by t2.terminalid;
/*
TERMINALID CNT
---------- ----------
3DD35 3
42463 0
72848 5
*/
BOBO12082119的语句查询出来还是只有前两行啊,'42463'这条还是没有的。
--看看这样行不行
SQL> with t as(
2 select '72848' terminalid,45 num from dual union all
3 select '72848',12 from dual union all
4 select '72848',14 from dual union all
5 select '3DD35',45 from dual union all
6 select '72848',70 from dual union all
7 select '3DD35',10 from dual union all
8 select '3DD35',98 from dual union all
9 select '72848',11 from dual)
10 select t2.terminalid,count(t1.num) cnt
11 from t t1,
12 (select decode(rownum, 1, '72848', 2, '3DD35', 3, '42463') terminalid
13 from dual connect by rownum <= 3) t2
14 where t2.terminalid=t1.terminalid(+)
15 group by t2.terminalid;
SQL> with t as(
2 select '72848' terminalid,45 num from dual union all
3 select '72848',12 from dual union all
4 select '72848',14 from dual union all
5 select '3DD35',45 from dual union all
6 select '72848',70 from dual union all
7 select '3DD35',10 from dual union all
8 select '3DD35',98 from dual union all
9 select '72848',11 from dual)
10 select t2.terminalid,count(t1.num) cnt
11 from t t1,
12 (select decode(rownum, 1, '72848', 2, '3DD35', 3, '42463') terminalid
13 from dual connect by rownum <= 3) t2
14 where t2.terminalid=t1.terminalid(+)
15 group by t2.terminalid;
--t是你的存有数据的表,t2是在查询时构建的
select t2.terminalid,count(t1.num) cnt
from t t1,
(select decode(rownum, 1, '72848', 2, '3DD35', 3, '42463') terminalid
from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)
group by t2.terminalid;
--你把你的部分原数据和想要得到的结果贴出来,我看看,详细点!
select t2.terminalid,count(t1.num) cnt
from t t1,
(select decode(rownum, 1, '72848', 2, '3DD35', 3, '42463') terminalid
from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)
and t1.date_1 > to_date('2011-3-25','yyyy-mm-dd')
group by t2.terminalid;
select t2.terminalid,count(t1.terminalid) cnt from EC_DATA20101230 t1,
(select decode(rownum,1,'72848B8089EE6BD4',2,'3DD35CD023CD9CEB',3,'FEC9BC10CB69EE9E1') terminalid from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)
group by t2.terminalid
结果如下:
1 72848B8089EE6BD4 1462
2 3DD35CD023CD9CEB 1462
3 FEC9BC10CB69EE9E1 0加了一个条件后:select t2.terminalid,count(t1.terminalid) cnt from EC_DATA20101230 t1,
(select decode(rownum,1,'72848B8089EE6BD4',2,'3DD35CD023CD9CEB',3,'FEC9BC10CB69EE9E1') terminalid from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)
and TIMEDATA>=to_date('2010-12-30 0:00:00','yyyy-mm-dd hh24:mi:ss') and TIMEDATA<=to_date('2010-12-30 12:00:00','yyyy-mm-dd hh24:mi:ss')
group by t2.terminalid
结果如下:
1 72848B8089EE6BD4 721
2 3DD35CD023CD9CEB 721
这个条件影响了FEC9BC10CB69EE9E1为零的结果。不加条件的话表示我直接在table里面count补零,加了条件表示我在一天中前12小时补零。
麻烦你在帮我看看。
select t2.terminalid,count(t1.terminalid) cnt from EC_DATA20101230 t1,
(select decode(rownum,1,'72848B8089EE6BD4',2,'3DD35CD023CD9CEB',3,'FEC9BC10CB69EE9E1') terminalid from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)
and 1=1
group by t2.terminalid
为什么这样就可以?and t1.timedata<>sysdate就不行 用到timedata字段就不行呢?
这么说,问题解决了?
我也不太明白where 1=1这个条件呢;
给你个网址看看where 1=1的用法:http://www.myext.cn/oracle/12921.html
-----------------------------------
关于这个题我的想法是,将from EC_DATA20101230 t1换成:
from
(select . from tab_name where TIMEDATA>=to_date('2010-12-30 0:00:00','yyyy-mm-dd hh24:mi:ss')
and TIMEDATA<=to_date('2010-12-30 12:00:00','yyyy-mm-dd hh24:mi:ss')) t1
这样其实就是先将符合时间条件的数据放到一个临时表中,再与制定terminalid进行连接!
你觉得呢?
(select terminalid,count(terminalid) cnt from EC_DATA20101230
where
TIMEDATA>=to_date('2010-12-30 0:00:00','yyyy-mm-dd hh24:mi:ss') and TIMEDATA<=to_date('2010-12-30 12:00:00','yyyy-mm-dd hh24:mi:ss') group by terminalid ) t1
,(select decode(rownum,1,'72848B8089EE6BD4',2,'3DD35CD023CD9CEB',3,'FEC9BC10CB69EE9E1') terminalid from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)
结果:
1 72848B8089EE6BD4 721
2 3DD35CD023CD9CEB 721
3 FEC9BC10CB69EE9E1 null我感觉有时间就没有办法补零 能补零就不能刷选时间 这个sql真不好写 只能这样了 null我程序里还可以处理 此问题还需解决!谢谢你啦
最终版本(暂不考虑效率的情况):
select t2.terminalid,nvl(t1.cnt,0) count from
(select terminalid,count(terminalid) cnt from EC_DATA20101230
where
TIMEDATA>=to_date('2010-12-30 0:00:00','yyyy-mm-dd hh24:mi:ss') and TIMEDATA<=to_date('2010-12-30 1:00:00','yyyy-mm-dd hh24:mi:ss') group by terminalid ) t1
,(select decode(rownum,1,'72848B8089EE6BD4',2,'3DD35CD023CD9CEB',3,'FEC9BC10CB69EE9E1') terminalid from dual connect by rownum <= 3) t2
where t2.terminalid=t1.terminalid(+)