select b.*, nvl(month_count, 0), nvl(year_count, 0)
from (select person_id,
sum(decode(to_char(cdate, 'yyyymm'),
to_char(sysdate, 'yyyymm'),
1)) month_count,
count(*) month_count,
sum(decode(to_char(cdate, 'yyyy'),
to_char(sysdate, 'yyyy'),
1)) year_count
from tab
where person_id = :id
group by person_id) aa,
tab b
where aa.person_id(+) = b.person_id
order by person_id
--我这个只有一次汇总,且没有登录也可以显示
from (select person_id,
sum(decode(to_char(cdate, 'yyyymm'),
to_char(sysdate, 'yyyymm'),
1)) month_count,
count(*) month_count,
sum(decode(to_char(cdate, 'yyyy'),
to_char(sysdate, 'yyyy'),
1)) year_count
from tab
where person_id = :id
group by person_id) aa,
tab b
where aa.person_id(+) = b.person_id
order by person_id
--我这个只有一次汇总,且没有登录也可以显示
解决方案 »
- 11gR2数据库日志报错:Fatal NI connect error 12170、TNS-12535、TNS-00505
- Oracle中的check约束问题——4位字符型数字的约束问题
- 一个很小的问题!
- oracle统计中总计小计显示的问题
- 错误 ORA-08103: object no longer exists , 有一个表 返回10条数据没问题,但是返回1000条 数据 就报这个错误
- [Oracle/基础和管理]请问OCIStmtPrepare中的语句长度要不要加上“\0”呢?
- oracle 中如何传递参数
- Oracle中在存储过程中怎么取更新过的记录数!
- 请大家推荐几本学习oracle的好书
- 卸载ORACLE后,如何在重新安装ORACLE!!急!急!!急!!!
- ora-12537:tns:连接关闭
- 请教一个SQL
--drop table s_customer_temp
--drop table s_customer_loginlog
CREATE TABLE s_customer_temp
(id INT IDENTITY(1,1),customName NVARCHAR(30),createTime DATETIME)
CREATE TABLE s_customer_loginlog
(id INT IDENTITY(1,1),customLoginId INT,loginTime DATETIME)GO
INSERT INTO [dbo].[s_customer_temp] ([customName],[createTime])
SELECT '王小'+ltrim(sv.number),getdate()
FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P'INSERT INTO [dbo].[s_customer_loginlog] ([customLoginId],[loginTime])
SELECT top 1000 sv.number,DATEADD(hh,sv.number,'2012-12-12')
FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' ORDER BY NEWID()
INSERT INTO [dbo].[s_customer_loginlog] ([customLoginId],[loginTime])
SELECT top 1000 sv.number,DATEADD(hh,sv.number,'2013-12-12')
FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' ORDER BY NEWID()INSERT INTO [dbo].[s_customer_loginlog] ([customLoginId],[loginTime])
SELECT top 1000 sv.number,DATEADD(hh,sv.number,'2012-12-12')
FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' ORDER BY NEWID()
INSERT INTO [dbo].[s_customer_loginlog] ([customLoginId],[loginTime])
SELECT top 1000 sv.number,DATEADD(hh,sv.number,'2013-12-12')
FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' ORDER BY NEWID()
INSERT INTO [dbo].[s_customer_loginlog] ([customLoginId],[loginTime])
SELECT top 500 1, '2013-12-12'
FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' ORDER BY NEWID()GO
select COUNT(*) from s_customer_loginlog
where datediff(month,[loginTime],getdate())=0 /*统计当月的在线人数*/select COUNT(*) from s_customer_loginlog
where datediff(YEAR,[loginTime],getdate())=0 /*统计当年的在线人数*/
select DATEADD(MM, DATEDIFF (MM , 0 , DATEADD(MM, 0, GETDATE())),0) as [当前月起始]; select DATEADD(MM, DATEDIFF (MM , 0 , DATEADD(MM, 1, GETDATE())),0) as [下月起始];
select CONVERT(datetime,ltrim(YEAR(GETDATE()))+'0101') as [当年起始];
select DATEADD(yy,1,ltrim(YEAR(GETDATE()))+'0101') as [下年起始];select *
,(
select COUNT(*) from s_customer_loginlog ll
where
ll.loginTime>=DATEADD(MM, DATEDIFF (MM , 0 , DATEADD(MM, 0, GETDATE())),0)
AND ll.loginTime<DATEADD(MM, DATEDIFF (MM , 0 , DATEADD(MM, 1, GETDATE())),0)
AND ll.customLoginId= t.id
) AS monthnum
,(
select COUNT(*) from s_customer_loginlog ll
where
ll.loginTime>=CONVERT(datetime,ltrim(YEAR(GETDATE()))+'0101')
AND ll.loginTime<DATEADD(yy,1,ltrim(YEAR(GETDATE()))+'0101')
AND ll.customLoginId= t.id
) AS yearnum
from (
select *,row_number() over (order by id) as rank from s_customer_temp a
where 1=1
) as t
where
t.rank between 0 and 10
order by createTime DESC
---loginTime你要加索引。。
--CREATE INDEX IX_s_customer_loginlog_loginTime ON s_customer_loginlog(loginTime);