COMPANY CREATETIME ONEMONEY
A 2010-01-01 100
A 2010-01-01 200
A 2010-01-02 200
B 2010-01-01 300
A 2010-01-03 400
统计2010-01-01到2010-01-04的销售情况
A 2010-01-01 300
A 2010-01-02 300
A 2010-01-03 400
A 2010-01-04 0
B 2010-01-01 300
B 2010-01-02 0
B 2010-01-03 0
B 2010-01-04 0
A 2010-01-01 100
A 2010-01-01 200
A 2010-01-02 200
B 2010-01-01 300
A 2010-01-03 400
统计2010-01-01到2010-01-04的销售情况
A 2010-01-01 300
A 2010-01-02 300
A 2010-01-03 400
A 2010-01-04 0
B 2010-01-01 300
B 2010-01-02 0
B 2010-01-03 0
B 2010-01-04 0
SET @S='2010-01-01'
SET @E='2010-01-04'SELECT DATEADD(DD,NUMBER,@S)
FROM MASTER..SPT_VALUES
WHERE TYPE='P' AND DATEADD(DD,NUMBER,@S)<=@E再LEFT JOIN
from 表
where CREATETIME>='2010-01-01' and CREATETIME<'2010-01-05'
group by COMPANY
select @ds='2010-01-01',@de='2010-01-04'select a.COMPANY, dateadd(day,number,@ds) s,sum(ONEMONEY)
from master..spt_values a left join [Table] b
on a.dateadd(day,number,@ds)=b.CREATETIME
where type='P' and dateadd(day,number,@ds)<=@de
group by a.COMPANY,dateadd(day,number,@ds)
drop table tb
Go
Create table tb([COMPANY] nvarchar(1),[CREATETIME] Datetime,[ONEMONEY] int)
Insert tb
select N'A','2010-01-01',100 union all
select N'A','2010-01-01',200 union all
select N'A','2010-01-02',200 union all
select N'B','2010-01-01',300 union all
select N'A','2010-01-03',400
Go
select m.*,isnull([ONEMONEY],0)[ONEMONEY]
from(
select [COMPANY],CREATETIME
from(
select distinct [COMPANY]
from tb)a,
(select dateadd(day,number,'2010-01-01')CREATETIME
from master..spt_values
where type='P' and dateadd(day,number,'2010-01-01')<='2010-01-04')b
)m
left join tb n on m.[COMPANY]=n.[COMPANY] and m.CREATETIME=n.CREATETIME
/*
COMPANY CREATETIME ONEMONEY
------- ----------------------- -----------
A 2010-01-01 00:00:00.000 100
A 2010-01-01 00:00:00.000 200
A 2010-01-02 00:00:00.000 200
A 2010-01-03 00:00:00.000 400
A 2010-01-04 00:00:00.000 0
B 2010-01-01 00:00:00.000 300
B 2010-01-02 00:00:00.000 0
B 2010-01-03 00:00:00.000 0
B 2010-01-04 00:00:00.000 0*/
from master..spt_values a left join [Table] b
on dateadd(day,number,@ds)=b.CREATETIME
where type='P' and dateadd(day,number,@ds)<=@de
group by a.COMPANY,dateadd(day,number,@ds)
if not object_id('tb') is null
drop table tb
Go
Create table tb([COMPANY] nvarchar(1),[CREATETIME] Datetime,[ONEMONEY] int)
Insert tb
select N'A','2010-01-01',100 union all
select N'A','2010-01-01',200 union all
select N'A','2010-01-02',200 union all
select N'B','2010-01-01',300 union all
select N'A','2010-01-03',400
Go
select m.*,sum(isnull([ONEMONEY],0))[ONEMONEY]
from(
select [COMPANY],CREATETIME
from(
select distinct [COMPANY]
from tb)a,
(select dateadd(day,number,'2010-01-01')CREATETIME
from master..spt_values
where type='P' and dateadd(day,number,'2010-01-01')<='2010-01-04')b
)m
left join tb n on m.[COMPANY]=n.[COMPANY] and m.CREATETIME=n.CREATETIME
group by m.[COMPANY],m.CREATETIME
order by m.[COMPANY],m.CREATETIME
/*
COMPANY CREATETIME ONEMONEY
------- ----------------------- -----------
A 2010-01-01 00:00:00.000 300
A 2010-01-02 00:00:00.000 200
A 2010-01-03 00:00:00.000 400
A 2010-01-04 00:00:00.000 0
B 2010-01-01 00:00:00.000 300
B 2010-01-02 00:00:00.000 0
B 2010-01-03 00:00:00.000 0
B 2010-01-04 00:00:00.000 0*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-16 14:55:59
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([COMPANY] varchar(1),[CREATETIME] datetime,[ONEMONEY] int)
insert [tb]
select 'A','2010-01-01',100 union all
select 'A','2010-01-01',200 union all
select 'A','2010-01-02',200 union all
select 'B','2010-01-01',300 union all
select 'A','2010-01-03',400
--------------开始查询--------------------------
select
a.*,isnull(b.[ONEMONEY],0) as [ONEMONEY]
from
(
select
*
from
(select distinct company from tb)a
cross join
(select
dateadd(day,number,'2010-01-01') as CREATETIME
from
master..spt_values
where
type='P'
and
dateadd(day,number,'2010-01-01')<='2010-01-04')b
)a
left join
tb b
on
a.[COMPANY]=b.[COMPANY] and a.CREATETIME=b.CREATETIME
----------------结果----------------------------
/* company CREATETIME ONEMONEY
------- ----------------------- -----------
A 2010-01-01 00:00:00.000 100
A 2010-01-01 00:00:00.000 200
A 2010-01-02 00:00:00.000 200
A 2010-01-03 00:00:00.000 400
A 2010-01-04 00:00:00.000 0
B 2010-01-01 00:00:00.000 300
B 2010-01-02 00:00:00.000 0
B 2010-01-03 00:00:00.000 0
B 2010-01-04 00:00:00.000 0(9 行受影响)
*/
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-16 14:55:05
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([COMPANY] NVARCHAR(10),[CREATETIME] DATETIME,[ONEMONEY] INT)
INSERT [tb]
SELECT 'A',N'2010-01-01',100 UNION ALL
SELECT 'A',N'2010-01-01',200 UNION ALL
SELECT 'A',N'2010-01-02',200 UNION ALL
SELECT 'B',N'2010-01-01',300 UNION ALL
SELECT 'A',N'2010-01-03',400
GO
--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @BT DATETIME='2010-01-01',@ET DATETIME='2010-01-04'
SELECT A.[COMPANY],CONVERT(VARCHAR,DATEADD(DD,N,@BT),23) [CREATETIME],
SUM(ISNULL(B.[ONEMONEY],0)) [ONEMONEY]
FROM (
SELECT NUMBER AS N,[COMPANY] FROM MASTER..SPT_VALUES A,
(SELECT DISTINCT [COMPANY] FROM tb) B
WHERE TYPE='P' AND NUMBER<=DATEDIFF(DD,@BT,@ET)
) A
LEFT JOIN TB B
ON DATEADD(DD,A.N,@BT)=B.CREATETIME
AND A.[COMPANY]=B.[COMPANY]
GROUP BY A.[COMPANY],DATEADD(DD,A.N,@BT)
ORDER BY COMPANY,CREATETIME
/*
COMPANY CREATETIME ONEMONEY
---------- ------------------------------ -----------
A 2010-01-01 300
A 2010-01-02 200
A 2010-01-03 400
A 2010-01-04 0
B 2010-01-01 300
B 2010-01-02 0
B 2010-01-03 0
B 2010-01-04 0(8 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-16 14:55:59
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([COMPANY] varchar(1),[CREATETIME] datetime,[ONEMONEY] int)
insert [tb]
select 'A','2010-01-01',100 union all
select 'A','2010-01-01',200 union all
select 'A','2010-01-02',200 union all
select 'B','2010-01-01',300 union all
select 'A','2010-01-03',400
--------------开始查询--------------------------
select
a.*,isnull(b.[ONEMONEY],0) as [ONEMONEY]
from
(
select
*
from
(select distinct company from tb)a
cross join
(select
dateadd(day,number,'2010-01-01') as CREATETIME
from
master..spt_values
where
type='P'
and
dateadd(day,number,'2010-01-01')<='2010-01-04')b
)a
outer apply
(select * from tb where a.[COMPANY]=[COMPANY] and a.CREATETIME=CREATETIME)b
----------------结果----------------------------
/* company CREATETIME ONEMONEY
------- ----------------------- -----------
A 2010-01-01 00:00:00.000 100
A 2010-01-01 00:00:00.000 200
A 2010-01-02 00:00:00.000 200
A 2010-01-03 00:00:00.000 400
A 2010-01-04 00:00:00.000 0
B 2010-01-01 00:00:00.000 300
B 2010-01-02 00:00:00.000 0
B 2010-01-03 00:00:00.000 0
B 2010-01-04 00:00:00.000 0(9 行受影响)
*/
不是应该200吗?
select COMPANY, CREATETIME, sum(ONEMONEY) as ONEMONEY into table_temp
from table_name
where CREATETIME<='2010-01-01' and CREATETIME>='2010-01-04'
group COMPANY, CREATETIME
order by COMPANY
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-16 14:55:59
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([COMPANY] varchar(1),[CREATETIME] datetime,[ONEMONEY] int)
insert [tb]
select 'A','2010-01-01',100 union all
select 'A','2010-01-01',200 union all
select 'A','2010-01-02',200 union all
select 'B','2010-01-01',300 union all
select 'A','2010-01-03',400
--------------开始查询--------------------------
select
a.*,isnull(sum(b.[ONEMONEY]),0) as [ONEMONEY]
from
(
select
*
from
(select distinct company from tb)a
cross join
(select
dateadd(day,number,'2010-01-01') as CREATETIME
from
master..spt_values
where
type='P'
and
dateadd(day,number,'2010-01-01')<='2010-01-04')b
)a
outer apply
(select * from tb where a.[COMPANY]=[COMPANY] and a.CREATETIME=CREATETIME)b
group by
a.[COMPANY],a.CREATETIME
order by
a.[COMPANY],a.CREATETIME
----------------结果----------------------------
/* company CREATETIME ONEMONEY
------- ----------------------- -----------
A 2010-01-01 00:00:00.000 300
A 2010-01-02 00:00:00.000 200
A 2010-01-03 00:00:00.000 400
A 2010-01-04 00:00:00.000 0
B 2010-01-01 00:00:00.000 300
B 2010-01-02 00:00:00.000 0
B 2010-01-03 00:00:00.000 0
B 2010-01-04 00:00:00.000 0
警告: 聚合或其他 SET 操作消除了空值。(8 行受影响)*/