用一个SELECT 实现
例如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为升序排列
[解决办法]
bu
[解决办法]
动态sql
[解决办法]
把你的查询语句帖出来！
在查询字段里加(select sum(num) from table where a.b=b and a.a=a ) as c
[解决办法]
得到的C这一列是干什么用的?怎么没有看懂你要干什么啊?
[解决办法]
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
哪位再处理一下?
[解决办法]
看看这个，借助楼上兄弟创建的表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
--结果
qwe118
dsfg 218
小计11818
ger1925
kuyre2025
we342325
小计22525
db2635
tyuq2935
小计33535
[解决办法]
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
*/
[解决办法]
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
测试通过
