select count(*),sum(yjje) from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc;现在数据库中的j_dwmc字段有两类的值,一类为测井,一类为钻井,所以,查询出来的是两行数据:
COUNT(*) SUM(YJJE)
---------- ----------
2 64
1 20但是这两行数据没有顺序,也就是说,我不知道哪行是测井的哪行是钻井的,而且,数据库中的j_dwmc字段只能有8种固定的值,我要求:按我规定的j_dwmc值的顺序来排列分组后的结果,如果j_dwmc中只有5种值,那么,其它的三种也要按顺序排列出来,只不过要用0来填充。
如何实现?谢谢大家!
COUNT(*) SUM(YJJE)
---------- ----------
2 64
1 20但是这两行数据没有顺序,也就是说,我不知道哪行是测井的哪行是钻井的,而且,数据库中的j_dwmc字段只能有8种固定的值,我要求:按我规定的j_dwmc值的顺序来排列分组后的结果,如果j_dwmc中只有5种值,那么,其它的三种也要按顺序排列出来,只不过要用0来填充。
如何实现?谢谢大家!
解决方案 »
- ORACLE查询语句求助
- 怎样向表中循环插入数据而不至于违反唯一性约束
- sql 语句超难优化问题,请专家分析
- exp备份多表的一个问题
- java如何读取plsql的对象数组了?
- oracle进程内存占用一直增加,达到1G左右的时候就会连接失败,监听进程死掉,或者CPU达到100%,如何解决?
- 急,如何在ORACLE 9I 中导入ACCESS里面的数据库的表啊!!!!!
- oracle9i 与 oracle9i Application server 有何区别啊?
- TO_DATE('2002-AUG-22','YYYY-MON-DD')为什么说月份不对?
- 我建立了一个oracle数据库,在企业管理器的console中登陆未成功,后在企业管理器的配置辅助工具中配置,提示无监听,如何配置?(我的十wi
- 求助SQL问题
- 以组内某个列的最大值给组排序
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje
From
A
Left Join
(select j_dwmc, count(*) As countj_dwmc, sum(yjje) As sumyjje from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc) B
On A.j_dwmc = B.j_dwmc
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje
From
A
Left Join
(select j_dwmc, count(*) As countj_dwmc, sum(yjje) As sumyjje from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc) B
On A.j_dwmc = B.j_dwmc
Order By A.j_dwmc
Distinct
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje
From
A
Left Join
(select j_dwmc, count(*) As countj_dwmc, sum(yjje) As sumyjje from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc) B
On A.j_dwmc = B.j_dwmc
Order By A.j_dwmc
(select 1 orderno,'第一种值' Name union select 2,'第一种值' union select 3,'第一种值')a left join
(select j_dwmc,count(*)行数,sum(yjje)总数 from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc)b
on b.j_dwmc=a.Name order by a.orderno
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje我不明白上面的A,B.countj_dwmc,分别指的是什么?
非常感谢!
弱弱的问一句:B.countj_dwmc指的是什么?
-----------
count(*) As countj_dwmc, sum(yjje) As sumyjje
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje我不明白上面的A,B.countj_dwmc,分别指的是什么?
非常感谢!
---------------------
count(*) As countj_dwmc, sum(yjje) As sumyjjecountj_dwmc是count(*)的別名sumyjje是sum(yjje) 的別名
create table app_jhpz(yjje int,j_dwmc varchar(10),gs_jbrq varchar(10))
-----------------------
0 0
0 0
0 0
create table app_jhpz(yjje int,j_dwmc varchar(10),gs_jbrq varchar(10)) drop table app_jhpz
insert app_jhpz select 100,'第一种值','2007-01-02'
insert app_jhpz select 200,'第一种值','2007-01-03'
insert app_jhpz select 300,'第二种值','2007-01-04'
insert app_jhpz select 400,'第三种值','2007-01-05'
insert app_jhpz select 500,'第二种值','2007-01-06'
insert app_jhpz select 600,'第三种值','2007-01-07'
insert app_jhpz select 700,'第一种值','2007-01-08'
insert app_jhpz select 800,'第二种值','2007-01-08'
insert app_jhpz select 900,'第三种值','2007-02-02'
insert app_jhpz select 1000,'第一种值','2007-02-02'
----------------------------
select isnull(b.行数,0),isnull(b.总数,0) from
(select 1 orderno,'第一种值' Name union select 2,'第二种值' union select 3,'第三种值')a left join
(select j_dwmc,count(*)行数,sum(yjje)总数 from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc)b
on b.j_dwmc=a.Name order by a.orderno
-----------------------
3 1000
3 1600
2 1000
Distinct
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje
From
A
Left Join
(select j_dwmc, count(*) As countj_dwmc, sum(yjje) As sumyjje from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc) B
On A.j_dwmc = B.j_dwmc
Order By A.j_dwmc
-------------
這個語句執行有什麼問題?
ORA-00942: table or view does not exist对了,忘了告诉大家,我用的是ORACLE数据库,我习惯了来sql server
ORA-00942: table or view does not exist对了,忘了告诉大家,我用的是ORACLE数据库,我习惯了来sql server
----------------
ORACLE在ORACLE版應該可以得到答案的,MS SQL和ORACLE的寫法上還是有些區別。我不知道我寫的這個語句在ORACLE中是否可以使用,但是在MS SQL中沒有問題的。另外,看錯誤提示,提示是“表或者視圖不存在”,檢查下那個語句中的表名和你的實際表名是否一致。
Select
Distinct
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje
From
A
Left Join中的别名A不存在
Distinct
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje
From
app_jhpz A
Left Join
(select j_dwmc, count(*) As countj_dwmc, sum(yjje) As sumyjje from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc) B
On A.j_dwmc = B.j_dwmc
Order By A.j_dwmc
Select
Distinct
A.j_dwmc,
IsNull(B.countj_dwmc, 0) As countj_dwmc,
IsNull(B.sumyjje, 0) As sumyjje这句话提示: "ISNULL": invalid identifier
trySelect
Distinct
A.j_dwmc,
NVL(B.countj_dwmc, 0) As countj_dwmc,
NVL(B.sumyjje, 0) As sumyjje
From
app_jhpz A
Left Join
(select j_dwmc, count(*) As countj_dwmc, sum(yjje) As sumyjje from app_jhpz where gs_jbrq like '2007-01%' group by j_dwmc) B
On A.j_dwmc = B.j_dwmc
Order By A.j_dwmc