不能用存储过程如何写SQL。谢谢大家
（编号，名称，值，标志）
ID MC VALUE BZ
1 A 10 0
2 B 20 0
3 C 30 1
4 D 40 0
5 E 50 1
6 F 60 0
7 G 70 1
ID MC VALUE BZ VALUE2（查询字段）
1 A 10 0 A
2 B 20 0 A
3 C 30 1 A
4 D 40 0 D
5 E 50 1 D
6 F 60 0 F
7 G 70 1 F
3 个解决方案
#1
create table test(id int ,mc varchar(10) , value int , bz int )
go
insert into test(id, mc , value , bz) values
(1,'A', 10,0),
(2,'B', 20,0),
(3,'C', 30,1),
(4,'D', 40,0),
(5,'E', 50,1),
(6,'F', 60,0),
(7,'G', 70,1)
go
select * from test
go
select t.* ,
(select mc from test where id =
(select isnull(max(id),0) + 1 from test where bz = 1 and t.id >id )) value2
from test t
go
drop table test
go
(7 行受影响)
id mc value bz
----------- ---------- ----------- -----------
1 A 10 0
2 B 20 0
3 C 30 1
4 D 40 0
5 E 50 1
6 F 60 0
7 G 70 1
(7 行受影响)
id mc value bz value2
----------- ---------- ----------- ----------- ----------
1 A 10 0 A
2 B 20 0 A
3 C 30 1 A
4 D 40 0 D
5 E 50 1 D
6 F 60 0 F
7 G 70 1 F
(7 行受影响)
#2
WITH a1 (D,MC,VALUE,BZ) AS
(
SELECT 1 ,'A', 10, 0 UNION ALL
SELECT 2 ,'B', 20, 0 UNION ALL
SELECT 3 ,'C', 30, 1 UNION ALL
SELECT 4 ,'D', 40, 0 UNION ALL
SELECT 5 ,'E', 50, 1 UNION ALL
SELECT 6 ,'F', 60, 0 UNION ALL
SELECT 7 ,'G', 70, 1
)
,a2 AS
(
SELECT *
FROM a1 a
CROSS APPLY (SELECT TOP 1 D d2 FROM a1 WHERE d>=a.D AND BZ=1 ORDER BY d) b
)
SELECT D,MC,VALUE,BZ,(SELECT TOP 1 MC FROM a2 WHERE d2=a.d2 ORDER BY d) VALUE2
FROM a2 a
#3
该回复于2015-08-14 10:36:06被管理员删除
#1
create table test(id int ,mc varchar(10) , value int , bz int )
go
insert into test(id, mc , value , bz) values
(1,'A', 10,0),
(2,'B', 20,0),
(3,'C', 30,1),
(4,'D', 40,0),
(5,'E', 50,1),
(6,'F', 60,0),
(7,'G', 70,1)
go
select * from test
go
select t.* ,
(select mc from test where id =
(select isnull(max(id),0) + 1 from test where bz = 1 and t.id >id )) value2
from test t
go
drop table test
go
(7 行受影响)
id mc value bz
----------- ---------- ----------- -----------
1 A 10 0
2 B 20 0
3 C 30 1
4 D 40 0
5 E 50 1
6 F 60 0
7 G 70 1
(7 行受影响)
id mc value bz value2
----------- ---------- ----------- ----------- ----------
1 A 10 0 A
2 B 20 0 A
3 C 30 1 A
4 D 40 0 D
5 E 50 1 D
6 F 60 0 F
7 G 70 1 F
(7 行受影响)
#2
WITH a1 (D,MC,VALUE,BZ) AS
(
SELECT 1 ,'A', 10, 0 UNION ALL
SELECT 2 ,'B', 20, 0 UNION ALL
SELECT 3 ,'C', 30, 1 UNION ALL
SELECT 4 ,'D', 40, 0 UNION ALL
SELECT 5 ,'E', 50, 1 UNION ALL
SELECT 6 ,'F', 60, 0 UNION ALL
SELECT 7 ,'G', 70, 1
)
,a2 AS
(
SELECT *
FROM a1 a
CROSS APPLY (SELECT TOP 1 D d2 FROM a1 WHERE d>=a.D AND BZ=1 ORDER BY d) b
)
SELECT D,MC,VALUE,BZ,(SELECT TOP 1 MC FROM a2 WHERE d2=a.d2 ORDER BY d) VALUE2
FROM a2 a
#3
该回复于2015-08-14 10:36:06被管理员删除