修改了上面lzw_0736的代码,加了查询条件 rq >='2013-01':--2013-02之前的数据 select rq,th,mc,count(*) sl from 表 where rq<='2013-02' and rq >='2013-01' group by rq,th,mc
-- 2013-03之前的数据 select rq,th,mc,count(*) sl from 表 where rq<='2013-03' and rq >='2013-01' group by rq,th,mc
按月统计显示的 rq th mc sl 2013-01 001 A 1 2013-01 002 B 1 2013-02 001 A 1 我想要这样的 rq th mc sl 2013-02 001 A 2 2013-02 002 B 1
试试这个: select MAX(rq) as rq,th,mc,count(*) sl from 表 where rq<='2013-02' group by th,mc
---------------------------------------------------------------- -- Author :TravyLee(走自己的路,让狗去叫吧!) -- Date :2014-02-18 14:49:27 -- Version: -- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) -- Sep 16 2010 19:43:16 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb ( [rq] varchar(7), [th] varchar(3), [mc] varchar(1), [lsh] varchar(4) ) insert #tb select '2014-01','001','A','1-1' union all select '2014-01','002','B','1-2' union all select '2014-02','001','A','1-11' union all select '2014-03','003','C','1-02' union all select '2014-04','004','D','2-1' union all select '2014-04','003','C','2-2' union all select '2014-05','002','B','2-3' union all select '2014-05','001','A','2-4' go;with t as ( select cast([rq]+'-01' as datetime) as rq, [th], [mc] from #tb ) select CONVERT(varchar(7),GETDATE(),120) as rq, th,mc,COUNT(1) as sl from t where rq<=GETDATE() group by th,mc /* rq th mc sl ------------------------------- 2014-02 001 A 2 2014-02 002 B 1 */
---------------------------------------------------------------- -- Author :TravyLee(走自己的路,让狗去叫吧!) -- Date :2014-02-18 14:49:27 -- Version: -- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) -- Sep 16 2010 19:43:16 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb ( [rq] varchar(7), [th] varchar(3), [mc] varchar(1), [lsh] varchar(4) ) insert #tb select '2014-01','001','A','1-1' union all select '2014-01','002','B','1-2' union all select '2014-02','001','A','1-11' union all select '2014-03','003','C','1-02' union all select '2014-04','004','D','2-1' union all select '2014-04','003','C','2-2' union all select '2014-05','002','B','2-3' union all select '2014-05','001','A','2-4' go --三月 ;with t as ( select cast([rq]+'-01' as datetime) as rq, [th], [mc] from #tb ) select CONVERT(varchar(7),dateadd(mm,1,GETDATE()),120) as rq, th,mc,COUNT(1) as sl from t where rq<=dateadd(mm,1,GETDATE()) group by th,mc /* rq th mc sl ------------------------------- 2014-03 001 A 2 2014-03 002 B 1 2014-03 003 C 1 */
---------------------------------------------------------------- -- Author :TravyLee(走自己的路,让狗去叫吧!) -- Date :2014-02-18 14:49:27 -- Version: -- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) -- Sep 16 2010 19:43:16 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:#tb if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb ( [rq] varchar(7), [th] varchar(3), [mc] varchar(1), [lsh] varchar(4) ) insert #tb select '2014-01','001','A','1-1' union all select '2014-01','002','B','1-2' union all select '2014-02','001','A','1-11' union all select '2014-03','003','C','1-02' union all select '2014-04','004','D','2-1' union all select '2014-04','003','C','2-2' union all select '2014-05','002','B','2-3' union all select '2014-05','001','A','2-4' go declare @rq varchar(7) select @rq=CONVERT(varchar(7),GETDATE(),120) select CONVERT(varchar(7),GETDATE(),120) as rq, th,mc, COUNT(1) as sl from #tb where rq<=@rq group by th,mc --其实这样就行了,rq字段加上索引 /* rq th mc sl -------------------------- 2014-02 001 A 2 2014-02 002 B 1 */
select rq,th,mc,count(*) sl
from 表
where rq<='2013-02' and rq >='2013-01'
group by rq,th,mc
-- 2013-03之前的数据
select rq,th,mc,count(*) sl
from 表
where rq<='2013-03' and rq >='2013-01'
group by rq,th,mc
rq th mc sl
2013-01 001 A 1
2013-01 002 B 1
2013-02 001 A 1
我想要这样的
rq th mc sl
2013-02 001 A 2
2013-02 002 B 1
试试这个:
select MAX(rq) as rq,th,mc,count(*) sl
from 表
where rq<='2013-02'
group by th,mc
-- Author :TravyLee(走自己的路,让狗去叫吧!)
-- Date :2014-02-18 14:49:27
-- Version:
-- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
-- Sep 16 2010 19:43:16
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null
drop table #tb
go
create table #tb
(
[rq] varchar(7),
[th] varchar(3),
[mc] varchar(1),
[lsh] varchar(4)
)
insert #tb
select '2014-01','001','A','1-1' union all
select '2014-01','002','B','1-2' union all
select '2014-02','001','A','1-11' union all
select '2014-03','003','C','1-02' union all
select '2014-04','004','D','2-1' union all
select '2014-04','003','C','2-2' union all
select '2014-05','002','B','2-3' union all
select '2014-05','001','A','2-4'
go;with t as
(
select
cast([rq]+'-01' as datetime) as rq,
[th],
[mc]
from
#tb
)
select
CONVERT(varchar(7),GETDATE(),120) as rq,
th,mc,COUNT(1) as sl
from t where rq<=GETDATE()
group by th,mc
/*
rq th mc sl
-------------------------------
2014-02 001 A 2
2014-02 002 B 1
*/
-- Author :TravyLee(走自己的路,让狗去叫吧!)
-- Date :2014-02-18 14:49:27
-- Version:
-- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
-- Sep 16 2010 19:43:16
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null
drop table #tb
go
create table #tb
(
[rq] varchar(7),
[th] varchar(3),
[mc] varchar(1),
[lsh] varchar(4)
)
insert #tb
select '2014-01','001','A','1-1' union all
select '2014-01','002','B','1-2' union all
select '2014-02','001','A','1-11' union all
select '2014-03','003','C','1-02' union all
select '2014-04','004','D','2-1' union all
select '2014-04','003','C','2-2' union all
select '2014-05','002','B','2-3' union all
select '2014-05','001','A','2-4'
go
--三月
;with t as
(
select
cast([rq]+'-01' as datetime) as rq,
[th],
[mc]
from
#tb
)
select
CONVERT(varchar(7),dateadd(mm,1,GETDATE()),120) as rq,
th,mc,COUNT(1) as sl
from t where rq<=dateadd(mm,1,GETDATE())
group by th,mc
/*
rq th mc sl
-------------------------------
2014-03 001 A 2
2014-03 002 B 1
2014-03 003 C 1
*/
-- Author :TravyLee(走自己的路,让狗去叫吧!)
-- Date :2014-02-18 14:49:27
-- Version:
-- Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
-- Sep 16 2010 19:43:16
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:#tb
if object_id('tempdb.dbo.#tb') is not null
drop table #tb
go
create table #tb
(
[rq] varchar(7),
[th] varchar(3),
[mc] varchar(1),
[lsh] varchar(4)
)
insert #tb
select '2014-01','001','A','1-1' union all
select '2014-01','002','B','1-2' union all
select '2014-02','001','A','1-11' union all
select '2014-03','003','C','1-02' union all
select '2014-04','004','D','2-1' union all
select '2014-04','003','C','2-2' union all
select '2014-05','002','B','2-3' union all
select '2014-05','001','A','2-4'
go
declare @rq varchar(7)
select @rq=CONVERT(varchar(7),GETDATE(),120)
select
CONVERT(varchar(7),GETDATE(),120) as rq,
th,mc,
COUNT(1) as sl
from #tb where rq<=@rq
group by th,mc
--其实这样就行了,rq字段加上索引
/*
rq th mc sl
--------------------------
2014-02 001 A 2
2014-02 002 B 1
*/