22,210
社区成员
发帖
与我相关
我的任务
分享
DECLARE @TB TABLE([ID] INT, [Point] INT)
INSERT @TB
SELECT 1, 0 UNION ALL
SELECT 2, 78 UNION ALL
SELECT 3, 21 UNION ALL
SELECT 4, 58 UNION ALL
SELECT 5, 34 UNION ALL
SELECT 6, 21 UNION ALL
SELECT 7, 46 UNION ALL
SELECT 8, 41 UNION ALL
SELECT 9, 2 UNION ALL
SELECT 10, 1 UNION ALL
SELECT 11, 41 UNION ALL
SELECT 12, 15 UNION ALL
SELECT 13, 12 UNION ALL
SELECT 14, 20 UNION ALL
SELECT 15, 60 UNION ALL
SELECT 16, 52 UNION ALL
SELECT 17, 94 UNION ALL
SELECT 18, 30 UNION ALL
SELECT 19, 95 UNION ALL
SELECT 20, 61 UNION ALL
SELECT 21, 88 UNION ALL
SELECT 22, 0
select *,seq=identity(int,1,1) into # from @TB where Point<>0 order by Point
declare @P int
set @P=100
select top 6 *
from (
select ID,Point
from #
where seq<=(
select top 1 seq
from (
select *,(select sum(Point) from # where seq<=t.seq) as totalP
from # AS t
) ta
order by abs(totalP-@P))
) tb
order by Point desc
drop table #
--树型结构处理之双编号(广度深度排序)
if OBJECTPROPERTY(object_id('tb'),'isusertable')<>0
drop table tb
create table tb(ybh nvarchar(10),ebh nvarchar(10),beizhu nvarchar(1000))
insert tb
select '0001',null,'云南省'
union all select '0002','0001','昆明市'
union all select '0003','0001','昭通市'
union all select '0009','0001','大理市'
union all select '0008',null,'四川省'
union all select '0004',null,'贵州省'
union all select '0005','0002','五华区'
union all select '0007','0002','水富县'
union all select '0006','0005','西园路192号'
union all select '0010','0006','金色梧桐'
union all select '0011','0010','科技有限公司'
union all select '0015','0007','两碗乡'
union all select '0013','0015','两碗村'
union all select '0012','0013','某跨国集团董事长'
union all select '0014','0008','成都市'
--select * from tb
--广度排序(先显示第一层节点,再显示第二次节点......)
--定义辅助表
declare @level_tb table(bh nvarchar(10),level int)
declare @level int
set @level=0
insert @level_tb(bh,level)
select ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tb(bh,level)
select ybh,@level
from tb a,@level_tb b
where a.ebh=b.bh
and b.level=@level-1
end
select a.*,b.* from tb a,@level_tb b where a.ybh=b.bh order by level
--深度排序(模拟单编码法)
declare @level_tt table(ybh nvarchar(1000),ebh nvarchar(1000),level int)
declare @level int
set @level=0
insert @level_tt(ybh,ebh,level)
select ybh,ybh,@level from tb where ebh is null
while @@ROWCOUNT>0
begin
set @level=@level+1
insert @level_tt(ybh,ebh,level)
select a.ybh,b.ebh+a.ybh,@level
from tb a,@level_tt b
where a.ebh=b.ybh and b.level=@level-1
end
select space(b.level*2)+'----'+a.beizhu,a.*,b.*
from tb a,@level_tt b
where a.ybh=b.ybh
order by b.ebh
--> 生成测试数据表:ta
If not object_id('[p]') is null
Drop table [p]
Go
CREATE TABLE [dbo].[P](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Point] [int] NOT NULL,
)
set nocount on
declare @i int
declare @rnd int
set @i=0
while @i <4000
begin
select @rnd = cast(rand()*1000 as int)
insert into P values(@rnd)
set @i=@i+1
end
set nocount off
declare @s varchar(800)
set @s='100,11,600,300,244,1111,10000'
select top 6 * from p order by charindex(','+ltrim(point)+',',','+@s+',')
/*
ID Point
7 49
6 319
5 604
4 717
3 393
2 741
*/
先弄一个