一些比较难的sql问题：1
最近，在
论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、分组查询问题
例子表结构数据如下：
id status date price
1 1 2013-10-01 218
2 1 2013-10-02 218
3 0 2013-10-03 218
4 0 2013-10-04 238
5 0 2013-10-05 238
6 0 2013-10-06 238
7 0 2013-10-07 258
8 0 2013-10-08 258
9 0 2013-10-09 218
想获取的结果集一：
2013-10-01至2013-10-03 218
2013-10-04至2013-10-06 238
2013-10-07至2013-10-08 258
2013-10-09至2013-10-09 218
想获取的结果集二：
1 2013-10-01至2013-10-02 218
0 2013-10-03至2013-10-03 218
0 2013-10-04至2013-10-06 238
0 2013-10-07至2013-10-08 258
0 2013-10-09至2013-10-09 218
我的解法：
[sql]
--drop table tb
create table tb(id int,status int,date varchar(10),price int)
insert into tb
select 1, 1, '2013-10-01', 218 union all
select 2, 1, '2013-10-02', 218 union all
select 3, 0, '2013-10-03', 218 union all
select 4, 0, '2013-10-04', 238 union all
select 5, 0, '2013-10-05', 238 union all
select 6, 0, '2013-10-06', 238 union all
select 7, 0, '2013-10-07', 258 union all
select 8, 0, '2013-10-08', 258 union all
select 9, 0, '2013-10-09', 218 --union all
--select 10, 0, '2013-10-10', 218
go
--第一个结果集
;with t
as
(
select *,
row_number() over(partition by price order by id) as rownum,
min(id) over(partition by price) as min_id
from tb
),
tt
as
(
select id,
price,
a.date,
rownum - (id - min_id) as interval
from t a
)
select min(date) + '至' + max(date) as date,
price
from tt
group by price,interval
order by 1
/*
date price
2013-10-01至2013-10-03 218
2013-10-04至2013-10-06 238
2013-10-07至2013-10-08 258
2013-10-09至2013-10-09 218
*/
--第2个结果集
;with t
as
(
select *,
row_number() over(partition by status,price order by id) as rownum,
min(id) over(partition by status,price) as min_id
from tb
),
tt
as
(
select id,
price,
a.date,
a.status,
rownum - (id - min_id) as interval
from t a
)
select status,min(date) + '至' + max(date),price
from tt
group by status,price,interval
order by 2
/*
status date price
1 2013-10-01至2013-10-02 218
0 2013-10-03至2013-10-03 218
0 2013-10-04至2013-10-06 238
0 2013-10-07至2013-10-08 258
0 2013-10-09至2013-10-09 218
*/
2、sql中怎么判断某个字段的值是否连续？
比如：A表的字段AID的值为：1、2、4、5、7、8、10
怎么用sql查询出2、5、8的结果呢？
要查的结果就是查询这组数据从哪里开始不连续的。
我的解法：
[sql]
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、MS-SQL 根据实际所需规格table去比对另一个库存table取浪费最少的数据
情境描述：根据表A里的物料去比对表B,然后表A根据A1括号里两个尺寸浪费