所以，觉得有必要记录下来，这样以后再次碰到这类问题，也能从中获取解答的思路。
1、求一查询语句
http://bbs.csdn.net/topics/390633004
CREATE #temp (cName CHAR(1),re int)
INSERT #temp
SELECT 'A',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'A',4 UNION ALL
SELECT 'A',5
如何查询得到如下的结果：
cName,re,xh
-------------------
'A',1,1
'A',2,1
'B',3,2
'A',4,3
'A',5,3
下面是我的解答：
select *,
dense_rank() over(order by case when exists(select t2.re from #temp t2
where t1.cname = t2.cname
and t1.re= t2.re + 1)
then (select t2.re from #temp t2
where t1.cname = t2.cname
and t1.re= t2.re + 1)
else t1.re
end
) as xh
from #temp t1
/*
cName re xh
A 1 1
A 2 1
B 3 2
A 4 3
A 5 3
*/
但是这个解答是有问题的，因为当连续的记录超过3条时，就会有问题，
所以修改了一下，这个是正确的解法：
create table #temp (cName CHAR(1),re int)
insert into #temp
SELECT 'A',1 UNION ALL
SELECT 'A',2 UNION ALL
SELECT 'B',3 UNION ALL
SELECT 'A',4 UNION ALL
SELECT 'A',5 union all
SELECT 'A',6 union all
SELECT 'A',7 union all
SELECT 'D',8 union all
SELECT 'D',9 union all
SELECT 'D',10 union all
select 'B',11 union all
select 'A',12
;with t
as
(
select *,
row_number() over(partition by cname order by re) as rownum
from #temp
)
select cname,
re,
dense_rank() over(order by case when exists(select min(t2.re) from t t2
where t1.cname = t2.cname
and t1.re-t1.rownum= t2.re-t2.rownum)
then (select min(t2.re) from t t2
where t1.cname = t2.cname
and t1.re-t1.rownum= t2.re-t2.rownum)
else t1.re
end
) as xh
from t t1
/*cname re xh
A 1 1
A 2 1
B 3 2
A 4 3
A 5 3
A 6 3
A 7 3
D 8 4
D 9 4
D 10 4
B 11 5
A 12 6
*/
2、sql循环取差值，该怎么写？
http://bbs.csdn.net/topics/390636438?page=1#post-396012416
现在有一组 条件 和 一个 数字（比如是10）
根据条件可以查出若干条数据 ，比如是这么样子的
ID Num NumOut
1 4 0
2 5 0
3 8 0
4 6 0……
按顺序 用每一条记录的 Num减去 数字，够减NumOut赋值为差值，不够减，NumOut赋值与Num相同，再用下一个减剩余数量。
也就是说，以上的数据会被更新为：
ID Num NumOut
1 4 4
2 5 5
3 8 1
4 6 0……
这应该如何用sql语句表达？？？
按我的想法应该写个循环，判断的，但从来没写过sql的循环，查出的数据又该怎么保存呢？急用，多谢指教了！！！
下面是我的解法：
--drop table tb
create TABLE tb (ID int,Num int,NumOut int)
INSERT into tb
SELECT 1,4,0 UNION ALL
SELECT 2,5,0 UNION ALL
SELECT 3,8,0 UNION ALL
SELECT 4,6,0
go
--你要减的数
declare @num int
set @num = 10
;with t
as
(
select *,
row_number() over(order by @@servername) as rownum
from tb
),
tt
as
(
select id,
num,
numout,
(select sum(num) from t t2
where t2.rownum <= t1.rownum) as sum_num
from t t1
)
--更新
update tt
set numout = case when sum_num <= @num
then num
when sum_num > @num and
@num - (sum_num - num) >=0
then @num - (sum_num - num)
else 0
end
--查询
select * from tb
/*
ID Num NumOut
1 4 4
2 5 5
3 8 1
4 6 0
*/
3、求sql，看似简单。
http://bbs.csdn.net/topics/390620423
No FLAG
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
我的解法，最关键的是如何生成行号，分别对不同的数据产生行号，由于是2000数据库，稍微有点麻烦：
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数据库，那么就简单多了：
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
*/