一些比较难的sql问题：4
最近，在
论坛中，遇到了不少比较难的sql问题，虽然自己都能解决，但发现过几天后，就记不起来了，也忘记解决的方法了。
所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、求一个获取连续值的方法。
这个用spt_values也可以。
如果是SQL Server 2005及以后的版本，可以通过递归来实现，非常简单。
我的解法：
[sql] ;with r as ( select 1 as number --第一个数 union all select number + 1 from r where r.number < 10 --最后一个数 ) select * from r --option(maxrecursion 1000) /* number 1 2 3 4 5 6 7 8 9 10 */
2、如何去掉字段内的重复。
不是去整行，行都是唯一的，如，现有下表：
零件号 名称 装入上级 装入数量 总数量
TI4.005 A AA 1 1
TI4.005 A BB 1 1
TI4.005 A CC 2 2
TI4.005 4
希望得到的结果：
TI4.005 A AA 1 1
BB 1 1
CC 2 2
4
如何用sql实现？
我的解法，不过这个解法需要数据是按照一定的顺序输入的，因为语句中只查找上一行的数据，和上一行进行比较，如果没有顺序，可能会有问题：
[sql]
;with t(零件号, 名称, 装入上级 , 装入数量, 总数量)
as
(
select 'TI4.005', 'A', 'AA', 1, 1 union all
select 'TI4.005', 'A', 'BB', 1, 1 union all
select 'TI4.005', 'A', 'CC', 2, 2 union all
select 'TI4.005', null, null, null, 4
),
tt
as
(
select *,
row_number() over(partition by 零件号 order by 零件号) as rownum
from t
)
select case when 零件号 = (select top 1 零件号 from tt t2
where t1.零件号 = t2.零件号
and t2.rownum < t1.rownum
order by t2.rownum desc)
then null
else 零件号
end as 零件号,
case when 名称 = (select top 1 名称 from tt t2
where t1.零件号 = t2.零件号
and t2.rownum < t1.rownum
order by t2.rownum desc)
then null
else 名称
end as 名称,
case when 装入上级 = (select top 1 名称 from tt t2
where t1.零件号 = t2.零件号
and t2.rownum < t1.rownum
order by t2.rownum desc)
then null
else 装入上级
end as 装入上级,
装入数量,
总数量
from tt t1
/*
零件号 名称 装入上级 装入数量 总数量
TI4.005 A AA 1 1
NULL NULL BB 1 1
NULL NULL CC 2 2
NULL NULL NULL NULL 4
*/
3、求sql，看似简单。
No FLAG
1 Z
2 Z
3 Z
4 L
5 Z
6 L
7 L
8 L
查询结果
FLAG-Z FLAG-L
1 4
2 6
3 7
4 8
SQL2000
数据库
我的解法，最关键的是如何生成行号，分别对不同的数据产生行号，由于是2000数据库，稍微有点麻烦：
[sql]
drop table t
create table t(No int,FLAG varchar(10));
insert into t
select 1 , 'Z' union all
select 2 , 'Z' union all
select 3 , 'Z' union all
select 4 , 'L' union all
select 5 , 'Z' union all
select 6 , 'L' union all
select 7 , 'L' union all
select 8 , 'L'
select z.no,L.no
from
(
select *,
(select count(*) from t t2 where t2.no <= t1.no and t2.flag = 'z') as rownum
from t t1
where flag = 'z'
)z
inner join
(
select *,
(select count(*) from t t2 where t2.no <= t1.no and t2.flag = 'L') as rownum
from t t1
where flag = 'L'
)L
on z.rownum = L.rownum
/*
no no
1 4
2 6
3 7
5 8
*/
如果是2005数据库，那么就简单多了：
[sql]
drop table t
create table t(No int,FLAG varchar(10));
insert into t
select 1 , 'Z' union all
select 2 , 'Z' union all
select 3 , 'Z' union all
select 4 , 'L' union all
select 5 , 'Z' union all
select 6 , 'L' union all
select 7 , 'L' union all
select 8 , 'L'
select t1.no,t2.no
from
(
select *,
row_number() over(partition by flag order by no) as rownum
from t
)t1
inner join
(
select *,
row_number() over(partition by flag order by no) as rownum
from t
)t2
on t1.rownum = t2.rownum
and t1.flag = 'z'
and t2.flag = 'L'
/*
no no
1 4
2 6
3 7
5 8
*/