所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、求一个聚合的SQL该怎么写
http://bbs.csdn.net/topics/390640648?page=1#post-396068172
有这样的数据
员工 开始日期 终了日期
A 2013-05-01 00:00:00.000 2013-06-08 00:00:00.000
A 2013-09-02 00:00:00.000 2013-12-31 00:00:00.000
A 2013-09-05 00:00:00.000 NULL
求员工A的所能表示的最大范围的日期，取日期的并集，最后结果应该如下:
员工 开始日期 终了日期
A 2013-05-01 00:00:00.000 2013-06-08 00:00:00.000
A 2013-09-02 00:00:00.000 NULL
请问谁知道这样的SQL语句该如何写。
我的解法：
;with t(员工 , 开始日期,终了日期)
as
(
select 'A', '2013-05-01 00:00:00.000','2013-06-08 00:00:00.000'
union all select 'A','2013-09-02 00:00:00.000','2013-12-31 00:00:00.000'
union all select 'A','2013-09-05 00:00:00.000',NULL
union all select 'A','2013-09-15 00:00:00.000', '2013-11-08 00:00:00.000'
),
tt
as
(
select 员工,开始日期,终了日期,
(select MIN(t2.开始日期)
from t t2
where t1.员工 = t2.员工
and t1.开始日期 between t2.开始日期 and isnull(t2.终了日期,'3000-01-01')
) as min_开始日期,
(select max(isnull(t2.终了日期,'3000-01-01'))
from t t2
where t1.员工 = t2.员工
and t1.开始日期 between t2.开始日期 and isnull(t2.终了日期,'3000-01-01')
) as max_终了日期
from t t1
)
select 员工,min_开始日期 as 开始日期,
nullif(max(max_终了日期),'3000-01-01') as 终了日期
from tt
group by 员工,min_开始日期
/*
员工 开始日期 终了日期
A 2013-05-01 00:00:00.000 2013-06-08 00:00:00.000
A 2013-09-02 00:00:00.000 NULL
*/
2、sql中怎么判断某个字段的值是否连续？
http://bbs.csdn.net/topics/390615670
比如：A表的字段AID的值为：1、2、4、5、7、8、10
怎么用sql查询出2、5、8的结果呢？
要查的结果就是查询这组数据从哪里开始不连续的。
我的解法：
create table A(AID int)
insert into A(AID)
select 1 union all
select 2 union all
select 4 union all
select 5 union all
select 7 union all
select 8 union all
select 10
select aid
from
(
select a.aid,
(select min(aid) from a aa where aa.aid > a.aid) min_aid
from A
)a
where aid +1 < min_aid
/*
aid
2
5
8
*/
3、求教：我有个表有入库时间，有出库时间，我想得到该样式号每月的销售和历史库存
http://bbs.csdn.net/topics/390629790
库结构大概是这样：
货号 样式号 入库时间 出库时间
a001 10 2011-1-10
b002 10 2011-1-10 2011-2-1
c003 10 2012-1-15 2012-2-2
d004 10 2013-2-3 2013-1-5
e005 10 2013-2-3
f006 15 2011-2-15 2011-3-16
g007 15 2011-2-16 2012-3-16
h009 15 2013-1-10
m012 18 2011-1-4
c009 18 2011-4-5 2012-5-6
f008 18 2012-2-19 2013-1-1
e008 18 2013-1-5 2013-2-6
我想得到的结果：
样式号 日期 销售件数 库存件数
10 2011-2 1 1
10 2012-2 1 1
10 2013-1 1 2
15 2011-3 1 0
15 2012-3 1 1
18 2012-5 1 1
18 2013-1 1 1
18 2013-2 1 1
我的解法：
if object_id('tb') is not null
drop table tb
go
create table tb
(
[货号] varchar(20),[样式号] int,
[入库时间] datetime,
[出库时间] datetime
)
insert into tb
SELECT 'a001',10,'2011-01-10',null UNION ALL
SELECT 'b002',10,'2011-01-10','2011-02-01' UNION ALL
SELECT 'c003',10,'2012-01-15','2012-02-02' UNION ALL
SELECT 'd004',10,'2013-01-03','2013-01-05' UNION ALL
SELECT 'e005',10,'2013-01-03',null UNION ALL
SELECT 'f006',15,'2011-02-15','2011-03-16' UNION ALL
SELECT 'g007',15,'2011-02-16','2012-03-16' UNION ALL
SELECT 'h009',15,'2013-01-10',null UNION ALL
SELECT 'm012',18,'2011-01-04',null UNION ALL
SELECT 'c009',18,'2011-04-05','2012-05-06' UNION ALL
SELECT 'f008',18,'2012-02-19','2013-01-01' UNION ALL
SELECT 'e008',18,'2013-01-05','2013-02-06'
go
;with t
as(
select *,
row_number() over(partition by 样式号
order by 入库时间,出库时间) as rownum
from tb
),
tt
as
(
select *,
case when 出库时间 is null
then (select top 1 出库时间
from t t2
where t1.样式号 = t2.样式号 and
t1.rownum > t2.rownum
order by t2.rownum desc)
else 出库时间
end as prior_row,
case when 出库时间 is null
then (select top 1 出库时间
from t t2
where t1.样式号 = t2.样式号 and
t1.rownum < t2.rownum
order by t2.rownum )
else 出库时间
end as next_row
from t t1
),
ttt
as
(
select 样式号,
convert(varchar(7),isnull(next_row,prior_row),120) as 日期,
count(出库时间) 销售件数,
count(入库时间) 库存件数,
count(入库时间) - count(出库时间) 剩余库存
--row_number() over(partition by 样式号
--order by convert(varchar(7),isnull(next_row,prior_row),120)) as rownum
from tt
group by 样式号,
convert(varchar(7),isnull(next_row,prior_row),120)
)
select t1.样式号,t1.日期,
isnull(t1.销售件数,0) as 销售件数 ,
isnull(t1.库存件数,0) +
isnull((select sum(库存件数)-sum(销售件数) as 剩余库存 from ttt t2
where t2.样式号 = t1.样式号
and t2.日期 < t1.日期
),0) -
isnull(t1.销售件数,0) as 库存件数
from ttt t1
order by t1.样式号
/*
样式号 日期 销售件数 库存件数
10 2011-02 1 1
10 2012-02 1 1
10 2013-01 1 2
15 2011-03 1 0
15 2012-03 1 1
18 2012-05 1 1
18 2013-01 1 1
18 2013-02 1 1
*/