经典的SQL语句(摘抄)
一、某一时间段的取平均值，一条select可以吗
=====================================================================
1、某一时间段内，每阁Ｎ段时间取一个平均值
例如：时间段2009-01-01~2010-01-01 ,n=2天，
则算出在2009-01-01~2010-01-01中每两天的一个平均值
一条select语句可以吗？
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB([ADATE] DATETIME,[NUM] INT)
INSERT TB
SELECT '2009-01-01',10 UNION ALL
SELECT '2009-01-02',70 UNION ALL
SELECT '2009-01-03',40 UNION ALL
SELECT '2009-01-04',30 UNION ALL
SELECT '2009-01-05',20 UNION ALL
SELECT '2009-01-06',80 UNION ALL
SELECT '2009-01-07',70 UNION ALL
SELECT '2009-01-08',60 UNION ALL
SELECT '2009-01-09',50 UNION ALL
SELECT '2009-01-10',40 UNION ALL
SELECT '2009-01-11',20 UNION ALL
SELECT '2009-01-12',30
GO
SELECT BDATE=CONVERT(VARCHAR(10),MIN(ADATE),120)+'~'+CONVERT(VARCHAR(10),MAX(ADATE),120),
AVGNUM=AVG(NUM) FROM TB
GROUP BY DATEDIFF(DAY,'2009-01-01',ADATE)/2
/*
BDATE AVGNUM
--------------------- -----------
2009-01-01~2009-01-02 40
2009-01-03~2009-01-04 35
2009-01-05~2009-01-06 50
2009-01-07~2009-01-08 65
2009-01-09~2009-01-10 45
2009-01-11~2009-01-12 25
（所影响的行数为 6 行）
*/
=====================================================================
2、如果要统计同样上面的内容，但是我希望数据库里面不存在的时间值也参与计算
例如
2009-01-01,
2009-01-04,
2009-01-05,
2009-01-06,
我们看到上面是没有２号和３号的
但是我希望能够将空值返回，这样
可以实现吗？
例如按每天计算的时候
它会算出每天的平均值
不存在的月份就以空返回
IF NOT OBJECT_ID('TB') IS NULL DROP TABLE TB
GO
CREATE TABLE TB([ADATE] DATETIME,[NUM] INT)
INSERT TB
SELECT '2009-01-01',10 UNION ALL
SELECT '2009-01-04',30 UNION ALL
SELECT '2009-01-05',20 UNION ALL
SELECT '2009-01-06',80 UNION ALL
SELECT '2009-01-07',70 UNION ALL
SELECT '2009-01-08',60 UNION ALL
SELECT '2009-01-09',50 UNION ALL
SELECT '2009-01-10',40 UNION ALL
SELECT '2009-01-11',20 UNION ALL
SELECT '2009-01-12',30
GO
select ADATE=CONVERT(VARCHAR(10),MIN(ADATE),120)+'~'+CONVERT(VARCHAR(10),MAX(ADATE),120), NUM=avg(isnull(NUM,0))
from spt_values A left join TB B
on ADATE=dateadd(dd,number,(select min(ADATE) from TB))
where type='p' and dateadd(dd,number,(select min(ADATE) from TB))<=(select max(ADATE) from TB)
group by number/2
/*
ADATE NUM
--------------------- -----------
2009-01-01~2009-01-01 5
2009-01-04~2009-01-04 15
2009-01-05~2009-01-06 50
2009-01-07~2009-01-08 65
2009-01-09~2009-01-10 45
2009-01-11~2009-01-12 25
（所影响的行数为 6 行）
警告: 聚合或其它 SET 操作消除了空值。
*/
drop table TB
==========================================================
SQL递归函数列出父级的所有子级(ID ParentID模式)
--调用方法：
--select * from GetChild('24')
--select id from GetChild('24')
--select * from KuCun where ProductType in(select id from GetChild('24'))
Create function [dbo].[GetChild](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
declare @i int
set @i = 1
insert into @t select @ID,@ID,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,@i
from
Dept a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
--------------------------------------------------------------------------------
--在SQL Server2005中其实提供了CTE[公共表表达式]来实现递归:
Declare @Id Int
Set @Id = 24; ---在此修改父节点
With RootNodeCTE(Id,ParentId)
As
(
Select Id,ParentId From Dept Where ParentId In (@Id)
Union All
Select Dept.Id,Dept.ParentId From RootNodeCTE
Inner Join Dept
On RootNodeCTE.Id = Dept.ParentId
)
Select * From RootNodeCTE
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--生成测试数据
create table Dept(ID int,ParentID int,msg varchar(20))
insert into Dept select 1,0,null
insert into Dept select 2,1,null
insert into Dept select 3,1,null
insert into Dept select 4,2,null
insert into Dept select 5,3,null
insert into Dept select 6,5,null
insert into Dept select 7,6,null
go
--创建用户定义函数
Create function [dbo].[GetChild](@ID varchar(10))
returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
declare @i int
set @i = 1
insert into @t select @ID,@ID,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.ParentID,@i
from
Dept a,@t b
where
a.ParentID=b.ID and b.Level = @i-1
end
return
end
--执行查询
select ID from dbo.GetChild(3)
go
--输出结果
/*
3
5
6
7
*/
--删除测试数据
drop function GetChild
drop table Dept
--http://topic.csdn.net/u/20080409/16/1fb7d941-b1a1-4326-a936-230ddf057cbe.html
