按月份分类汇总
ID 订单号 日期
1 cno001 2013-6-5
2 cno001 2013-6-10
3 cno002 2013-6-24
4 cno001 2013-7-5
5 cno001 2013-7-10
6 cno002 2013-7-15
得到结果:
订单号 月份
cno001 2013-6
cno002 2013-6
cno001 2013-7
cno002 2013-7
[解决办法]
----------------------------
-- Author :DBA_Huanzj(發糞塗牆)
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
--Oct 19 2012 13:38:57
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[订单号] varchar(6),[日期] datetime)
insert [huang]
select 1,'cno001','2013-6-5' union all
select 2,'cno001','2013-6-10' union all
select 3,'cno002','2013-6-24' union all
select 4,'cno001','2013-7-5' union all
select 5,'cno001','2013-7-10' union all
select 6,'cno002','2013-7-15'
--------------开始查询--------------------------
select distinct [订单号],SUBSTRING(convert(varchar(4),[日期]),1,4)+'-'+convert(varchar(2),datepart(mm,[日期]) )
from [huang]
select distinct 订单号,convert(varchar,year(日期))+'-'+convert(varchar,month(日期))
from TB
declare @tb table(
id int,
订单号 varchar(20),
日期 datetime
)
insert into @tb
select 1,'cno001','2013-6-5' union all
select 2,'cno001','2013-6-10' union all
select 3,'cno002','2013-6-24' union all
select 4,'cno001','2013-7-5' union all
select 5,'cno001','2013-7-10' union all
select 6,'cno002','2013-7-15'
;with tab as
(select 订单号,日期=convert(varchar,year(日期))+'-'+convert(varchar,month(日期)) from @tb)
select * from tab group by 订单号,日期
declare @tb table(
id int,
订单号 varchar(20),
日期 datetime
)
insert into @tb
select 1,'cno001','2013-6-5' union all
select 2,'cno001','2013-6-10' union all
select 3,'cno002','2013-6-24' union all
select 4,'cno001','2013-7-5' union all
select 5,'cno001','2013-7-10' union all
select 6,'cno002','2013-7-15'
select t.订单号, convert(char(7),t.日期,121) as 'YearMonth'
from @tb t
group by t.订单号, convert(char(7),t.日期,121)