需求: 要 把 竖的内容转变成 横显示,根据时间分组但是在分组的时候,总是不对,请教一下如何改正
select time,
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time,p.name
这样写,就会提示我time 不是单组分组函数,可我已经在group by 后面给time 进行分组了啊。难道2嵌套表不能这样吗?
select
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by p.name这样写就会 求JAVA 和C 的请求次数
select time
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time这样也会显示 时间 分组的结果但是二者合起来就报time 不是单分组函数表结构:T_USER_STATISTICS
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
U_ID NUMBER(22) NULL
IMEI VARCHAR2(64)
PLATID NUMBER(6) Y
MNC CHAR(2)
CITYID CHAR(4)
CNT INTEGER 0
TIME DATE SYSDATE ????
ISNEW CHAR(1) '0'
ISMONTH CHAR(1) '0'
ISYEAR CHAR(1) '0'
ISPAY CHAR(1) Y '0' T_STATIC_CLIENT_PLATFORMName Type Nullable Default Comments
------ ------------ -------- ------- --------
PLATID NUMBER(22)
NAME VARCHAR2(32) Y 该如何改SQL 啊?
在线等
select time,
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time,p.name
这样写,就会提示我time 不是单组分组函数,可我已经在group by 后面给time 进行分组了啊。难道2嵌套表不能这样吗?
select
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by p.name这样写就会 求JAVA 和C 的请求次数
select time
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time这样也会显示 时间 分组的结果但是二者合起来就报time 不是单分组函数表结构:T_USER_STATISTICS
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
U_ID NUMBER(22) NULL
IMEI VARCHAR2(64)
PLATID NUMBER(6) Y
MNC CHAR(2)
CITYID CHAR(4)
CNT INTEGER 0
TIME DATE SYSDATE ????
ISNEW CHAR(1) '0'
ISMONTH CHAR(1) '0'
ISYEAR CHAR(1) '0'
ISPAY CHAR(1) Y '0' T_STATIC_CLIENT_PLATFORMName Type Nullable Default Comments
------ ------------ -------- ------- --------
PLATID NUMBER(22)
NAME VARCHAR2(32) Y 该如何改SQL 啊?
在线等
select time,p.name
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time,p.name
就是根据时间 显示 每天 JAVA 和 C 的使用量
例如:
time java c
2010-3-2 15 20
2010-3-3 10 5
这是需求结果现在只能显示
java c
15 20
10 5
或者
time
2010-3-2
2010-3-3 如果联合起来 , 就会提示time 不是单分组函数T_USER_STATISTICS 表结构create table T_USER_STATISTICS (
u_id NUMBER(22) default NULL not null,
imei VARCHAR(64) not null,
platid NUMBER(6),
mnc CHAR(2) not null,
cityid CHAR(4) not null,
cnt NUMERIC default 0 not null,
time DATE default SYSDATE not null,
isNew CHAR(1) default '0' not null
constraint CKC_ISNEW_T_USER_S check (isNew between '0' and '1' and isNew in ('0','1')),
isMonth CHAR(1) default '0' not null
constraint CKC_ISMONTH_T_USER_S check (isMonth between '0' and '1' and isMonth in ('0','1')),
isYear CHAR(1) default '0' not null
constraint CKC_ISYEAR_T_USER_S check (isYear between '0' and '1' and isYear in ('0','1')),
isPay CHAR(1) default '0',
constraint PK_T_USER_STATISTICS primary key (u_id)
)
初始数据insert into t_user_statistics values('1368','35398403031664824','1000','02','0906','2','2009-9-8','1','1','1','0')
insert into t_user_statistics values('1369','35398403121664824','1001','02','0912','2','2009-9-9','1','1','1','0')
T_STATIC_CLIENT_PLATFORM 表结构create table db_agent.T_STATIC_CLIENT_PLATFORM (
platid NUMBER(22) not null,
name VARCHAR(32),
constraint PK_T_STATIC_CLIENT_PLATFORM primary key (platid)
)insert into t_static_client_platform values('1000','Java');
insert into t_static_client_platform values('1001','Windows Mobile');
select
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by p.name
显示结果
JAVA C
14498 79
select time
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time
显示结果time
2010-2-23
2010-2-24然后第一个SQL select time,
sum(case when p.name='Java' then count(t.platid) else 0 end) Java,
sum(case when p.name<>'Java' then count(t.platid) else 0 end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time,p.name就出错了,提示time 不是单分组函数,我在最后给time 做了分组啊·····继续等
如果不加入time 时间,可以正确的现实结果加了time 就提示错误了,非单分组函数
from t_user_statistics t
group by t.platid,t.time结果:
COURSENAME TIME CNT
Java 2009-09-08 2
Windows Mobile 2009-09-09 1
(case when p.name='Java' then t.cn else 0 end) Java,
(case when p.name<>'Java' then t.cn else 0 end) "Windows Mobile"
From (Select Platid, Time, Count(Platid) Cn
From t_User_Statistics
Group By Platid, Time) t,
t_Static_Client_Platform p
Where t.Platid = p.Platid试试这个?但是那个java,Windows Mobile那个列名是固定的。
Select t.Time,
Sum((case when p.name='Java' then t.cn else 0 end)) Java,
Sum((case when p.name<>'Java' then t.cn else 0 end)) "Windows Mobile"
From (Select Platid, Time, Count(Platid) Cn
From t_User_Statistics
Group By Platid, Time) t,
t_Static_Client_Platform p
Where t.Platid = p.Platid
And t.time between to_date('2009-9-8','yyyy-MM-dd') and to_date('2009-9-9','yyyy-MM-dd')
Group By t.Time
不好意思,是这个
然后再对以上结果再进行一次聚合,此时time就会出现错误了
这个sum可以去掉
或者我觉得这个逻辑其实可以写成
select time,
count(case when p.name='Java' then t.platid end) Java,
count(case when p.name<>'Java' then t.platid end) C
from t_user_statistics t join t_static_client_platform p
on p.platid=t.platid
where t.time between to_date('2010-2-23','yyyy-MM-dd') and to_date('2010-2-24','yyyy-MM-dd')
group by time