sql求助

[crazy915]

数据显示为：

a b c d
01 1 10 null
02 1 20 10
03 2 30 null
04 1 40 20


a是主键，求d的值是根据b为相同值的前一笔c值

[chuifengde]

不懂

[水族杰纶]

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

[regithanhu]

完全不知道你在说什么！昏了！楼猪可以描述清楚点不？

[fa_ge]

SELECT a,b,c,
       d=(SELECT top 1 c FROM # WHERE A.b=b AND a<A.a ORDER BY a DESC)
FROM # A

[fa_ge]

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 行）
*/

[crazy915]

数据结构是这样的：
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

[crazy915]

ok

“鶴嘯”的强，我咋就没相对这样写了。。

以后还得多到这里混混。。。。

[-狙击手-]

晕，晕，　晕

三晕

[zoujp_xyz]

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

[crazy915]

呵呵

这里的响应速度真快！！！！！！！！！！！！！！

[zoujp_xyz]

SELECT a,b,c,
       d=(SELECT MAX(c) FROM # WHERE A.b=b AND a<A.a)
FROM # A

[linzi_0535]

[Quote=引用 6 楼 fa_ge 的回复:]
SQL code

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
----- ------…
[/Quote]

顶这个

[crazy915]

max和top 1 order by desc 的比较。

那个效率高啊？

[水族杰纶]

0