用一个SELECT 实现

[DavidNoWay]

例如t1为：
a b
qwe 1
dsfg 2
小计1 18
ger 19
kuyre 20
we34 23
小计2 25
db 26
tyuq 29
小计3 35
能不能用一条SELECT语句实现
---
a b c
qwe 1 18
dsfg 2 18
小计1 18 18
ger 19 25
kuyre 20 25
we34 23 25
小计2 25 25
db 26 35
tyuq 29 35
小计3 35 35
其中b为升序排列

[toddzst]

bu

[yhtapmys]

动态sql

[ojuju10]

把你的查询语句帖出来！
在查询字段里加(select sum(num) from table where a.b=b and a.a=a ) as c

[wuya8115]

得到的C这一列是干什么用的?怎么没有看懂你要干什么啊?

[smile9961]

create table dbo.one
(
id int identity(1,1),
a nvarchar(10),
b int
)

insert into one
select 'qwe', 1 union all
select 'dsfg ', 2 union all
select N'小计1', 18 union all
select 'ger', 19 union all
select 'kuyre', 20 union all
select 'we34', 23 union all
select N'小计2', 25 union all
select 'db', 26 union all
select 'tyuq', 29 union all
select N'小计3', 35

select y.oid as yid, x.oid as xid, x.b
from
(
select min(id) as oid, convert(int, replace(a, N'小计', '')) as id, min(b) as b
from one group by a having substring(a, 1,2) = N'小计'
) x
inner join
(
select min(id) as oid, convert(int, replace(a, N'小计', '')) as id, min(b) as b
from one group by a having substring(a, 1,2) = N'小计'
) y
on x.id = y.id + 1

union all

select 1 as oid, oid as id, b
from
(
select top 1 min(id) as oid, min(b) as b
from one group by a having substring(a, 1,2) = N'小计'
) z

drop table one

--结果
yid xid b
----------- ----------- -----------
3 7 25
7 10 35
1 3 18

哪位再处理一下?

[DavidNoWay]

在小计1之前的都是值18，在小计2之前，小计1之后的值都是25
而且t1表是动态的，每次小计1、2、3的B值有可能不一样。
不过都是按B的值升序排列的。
感觉有点难度。O_0好你越说越乱了

[DavidNoWay]

好像

[lwl0606]

看看这个，借助楼上兄弟创建的表one


SELECT A,B,MIN(C) AS C
FROM (
select one.a,one.b,(case when one.b<= tem.b then tem.c end) as c from one ,(
select a,b,(case when a like N'小计%' then b end ) as c
from one) tem
) BB
WHERE C IS NOT NULL
GROUP BY A,B


--结果
qwe 1 18
dsfg 2 18
小计1 18 18
ger 19 25
kuyre 20 25
we34 23 25
小计2 25 25
db 26 35
tyuq 29 35
小计3 35 35

[-狙击手-]

create table one(
id int identity(1,1),
a nvarchar(10),
b int
)
go
insert into one
select 'qwe', 1 union all
select 'dsfg ', 2 union all
select N'小计1', 18 union all
select 'ger', 19 union all
select 'kuyre', 20 union all
select 'we34', 23 union all
select N'小计2', 25 union all
select 'db', 26 union all
select 'tyuq', 29 union all
select N'小计3', 35
go
-- select id from one where left(a,2) = '小计'
declare dd cursor for select id,b from one where left(a,2) = '小计'
declare @b int
declare @i int
declare @ii int
set @i = 0
set @ii =0
declare @sql varchar(8000)
set @sql = ''
open dd
FETCH NEXT FROM dd into @i,@b
WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = @sql+ 'union all select a,b,'+cast(@b as char(2)) + ' as c from one where id <= ' + cast(@i as char(2)) + ' and id > '+ cast(@ii as char(2))
set @ii = @i
FETCH NEXT FROM dd into @i,@b
END
set @sql = right(@sql, len(@sql) - 9)

exec(@sql)

CLOSE dd
DEALLOCATE dd

drop table one

/*


a b c
---------- ----------- -----------
qwe 1 18
dsfg 2 18
小计1 18 18
ger 19 25
kuyre 20 25
we34 23 25
小计2 25 25
db 26 35
tyuq 29 35
小计3 35 35


*/

[smile9961]

佩服lwl0606(寒泉), 看來我需要學習的是高手思維方式……

[wgzaaa]

select C.*,(select top 1 b from tb1 A where A.a like '%计%' and A.b-C.b>=0 ) C from tb1 C order by C.b
测试通过

[smile9961]

牛人真多...

[DavidNoWay]

呵呵，谢谢大家！！