declare @t table(a varchar(10),b varchar(10),c varchar(10),d varchar(10)) insert @t select '01', '1', '10', null insert @t select '02', '1', '20', 10 insert @t select '03', '2', '30', null insert @t select '04', '1', '40', 20 select * from @t where a in(select min(a) from @t group by b)a b c d ---------- ---------- ---------- ---------- 01 1 10 NULL 03 2 30 NULL 是这意思吗? 前一个值,是最小的那个?
a b c d 01 1 10 null 02 1 20 10 03 2 30 null 04 1 40 20 --update a set d = (select top 1 c from ta where b = a.b and a < a.a order by a desc) from ta a
SELECT a,b,c, d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC) FROM # A
SELECT * FROM employee WHERE gh='10403'CREATE TABLE #(a varchar(5), b int, c int) insert into # select '01', 1, 10 union all select '02', 1, 20 union all select '03', 2, 30 union all select '04', 1, 40 SELECT a,b,c, d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC) FROM # A/* a b c d ----- ----------- ----------- ----------- 01 1 10 NULL 02 1 20 10 03 2 30 NULL 04 1 40 20(所影响的行数为 4 行) */
数据结构是这样的: a b c 01 1 10 02 1 20 03 2 30 04 1 40 我要显示为:a b c d 01 1 10 null 02 1 20 10 03 2 30 null 04 1 40 20
declare @t table(a varchar(10),b varchar(10),c varchar(10),d varchar(10)) insert @t select '01', '1', '10', null insert @t select '02', '1', '20', 10 insert @t select '03', '2', '30', null insert @t select '04', '1', '40', 20 SELECT a,b,c, d=(SELECT top 1 c FROM @t WHERE t.b=b AND a<t.a ORDER BY a DESC) FROM @t t
SELECT * FROM employee WHERE gh='10403'CREATE TABLE #(a varchar(5), b int, c int) insert into # select '01', 1, 10 union all select '02', 1, 20 union all select '03', 2, 30 union all select '04', 1, 40 SELECT a,b,c, d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC) FROM # A
SELECT a,b,c, d=(SELECT MAX(c) FROM # WHERE A.b=b AND a<A.a) FROM # A
insert @t select '01', '1', '10', null
insert @t select '02', '1', '20', 10
insert @t select '03', '2', '30', null
insert @t select '04', '1', '40', 20
select * from @t where a in(select min(a) from @t group by b)a b c d
---------- ---------- ---------- ----------
01 1 10 NULL
03 2 30 NULL
是这意思吗?
前一个值,是最小的那个?
01 1 10 null
02 1 20 10
03 2 30 null
04 1 40 20 --update a
set d = (select top 1 c from ta where b = a.b and a < a.a order by a desc)
from ta a
SELECT a,b,c,
d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC)
FROM # A
insert into #
select '01', 1, 10 union all
select '02', 1, 20 union all
select '03', 2, 30 union all
select '04', 1, 40
SELECT a,b,c,
d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC)
FROM # A/*
a b c d
----- ----------- ----------- -----------
01 1 10 NULL
02 1 20 10
03 2 30 NULL
04 1 40 20(所影响的行数为 4 行)
*/
a b c
01 1 10
02 1 20
03 2 30
04 1 40 我要显示为:a b c d
01 1 10 null
02 1 20 10
03 2 30 null
04 1 40 20
insert @t select '01', '1', '10', null
insert @t select '02', '1', '20', 10
insert @t select '03', '2', '30', null
insert @t select '04', '1', '40', 20
SELECT a,b,c,
d=(SELECT top 1 c FROM @t WHERE t.b=b AND a<t.a ORDER BY a DESC)
FROM @t t
insert into #
select '01', 1, 10 union all
select '02', 1, 20 union all
select '03', 2, 30 union all
select '04', 1, 40
SELECT a,b,c,
d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC)
FROM # A
d=(SELECT MAX(c) FROM # WHERE A.b=b AND a<A.a)
FROM # A