数据是:
商品id 商品类别关系 显示顺序
0 0 0
1 0,1 0
2 0,2 2
3 0,1,3 0
4 0,2,4 0
5 0,2,5 1
6 0,6 1
大概就是这样的表了,现在要使查询出来的效果为
商品id 商品类别关系 显示顺序
0 0 0
1 0,1 0
3 0,1,3 0
6 0,6 1
2 0,2 2
4 0,2,4 0
5 0,2,5 1 用文字描述就是 象第一层0,1 0,2 0,6的要根据“显示顺序”排序,而且在他们之下的第二层象0,2,4 0,2,5也要排序,也就是同级“商品类别关系”排序,我是新手搞不出来,听说csdn高手多,特来请教了! 麻烦各位了,关系到工作的存留问题呀!
商品id 商品类别关系 显示顺序
0 0 0
1 0,1 0
2 0,2 2
3 0,1,3 0
4 0,2,4 0
5 0,2,5 1
6 0,6 1
大概就是这样的表了,现在要使查询出来的效果为
商品id 商品类别关系 显示顺序
0 0 0
1 0,1 0
3 0,1,3 0
6 0,6 1
2 0,2 2
4 0,2,4 0
5 0,2,5 1 用文字描述就是 象第一层0,1 0,2 0,6的要根据“显示顺序”排序,而且在他们之下的第二层象0,2,4 0,2,5也要排序,也就是同级“商品类别关系”排序,我是新手搞不出来,听说csdn高手多,特来请教了! 麻烦各位了,关系到工作的存留问题呀!
1 0,1 0 2
3 0,1,3 0 3
2 0,2 2 4
4 0,2,4 0 5
5 0,2,5 1 6 6 0,6 1 这条好像 应该在最底下吧?
得有个字段,要不你加一个sort字段,然后在按你定义的这个字段排序好了,表结构太乱了select * from table order by 新增字段 asc
left join # b on
left(a.关系,3)=left(b.关系,3)
and len(b.关系)=3
order by b.顺序,a.关系
create table #(商品id int, 关系 varchar(20), 顺序 int )insert into #
select 0, '0' , 0 union all
select 1 , '0,1' , 0 union all
select 2 , '0,2' , 2 union all
select 3 , '0,1,3' , 0 union all
select 4 , '0,2,4' , 0 union all
select 5 , '0,2,5' , 1 union all
select 6 , '0,6' , 1
select a.* from # a
left join # b on
left(a.关系,3)=left(b.关系,3)
and len(b.关系)=3
order by b.顺序,a.关系/**
商品id 关系 顺序
----------- -------------------- -----------
0 0 0
1 0,1 0
3 0,1,3 0
6 0,6 1
2 0,2 2
4 0,2,4 0
5 0,2,5 1(所影响的行数为 7 行)***/
insert into @table
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,6',1select 商品id,商品类别关系,显示顺序,
convert(int,
left(replace(商品类别关系,',',''),1)+convert(varchar(10),显示顺序)+
right(replace(商品类别关系,',',''),len(replace(商品类别关系,',',''))-1))as order_byfrom @table
order by order_by/*
(7 row(s) affected)
商品id 商品类别关系 显示顺序 order_by
----------- -------------------------------------------------- ----------- -----------
0 0 0 0
1 0,1 0 1
3 0,1,3 0 13
6 0,6 1 16
2 0,2 2 22
4 0,2,4 0 24
5 0,2,5 1 125(7 row(s) affected)
*/
--------------------------------------
先按'顺序'排序但要使第二层的'顺序'与 第一层 的相同,就先左联一次,再关系排你可以这样看看select * from # a
left join # b on
left(a.关系,3)=left(b.关系,3)
and len(b.关系)=3
order by b.顺序,a.关系
--你的在同级下不能排序
declare @table table(商品id int,商品类别关系 varchar(50),显示顺序 int)
insert into @table
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',1 union all
select 7,'0,6',1select 商品id,商品类别关系,显示顺序,
convert(int,
left(replace(商品类别关系,',',''),1)+convert(varchar(10),显示顺序)+
right(replace(商品类别关系,',',''),len(replace(商品类别关系,',',''))-1))as order_byfrom @table
order by order_by
---ljsql(第 1 行: '脑子' 附近有语法错误。)的结果
select a.* from @table a
left join @table b on
left(a.商品类别关系,3)=left(b.商品类别关系,3)
and len(b.商品类别关系)=3
order by b.显示顺序,a.商品类别关系
(8 row(s) affected)
商品id 商品类别关系 显示顺序 order_by
----------- -------------------------------------------------- ----------- -----------
0 0 0 0
1 0,1 0 1
3 0,1,3 0 13
7 0,6 1 16
2 0,2 2 22
4 0,2,4 0 24
6 0,2,3 1 123
5 0,2,5 1 125(8 row(s) affected)
---ljsql(第 1 行: '脑子' 附近有语法错误。)的结果
商品id 商品类别关系 显示顺序
----------- -------------------------------------------------- -----------
0 0 0
1 0,1 0
3 0,1,3 0
7 0,6 1
2 0,2 2
6 0,2,3 1
4 0,2,4 0
5 0,2,5 1(8 row(s) affected)
union all select '1', '0,1', 0
union all select '3', '0,1,3', 0
union all select '5', '0,2,5', 0
union all select '4', '0,2,4', 1
union all select '6', '0,6', 1
union all select '9', '0,2,4,9', 0
union all select '7', '0,2,4,9,7', 0
union all select '2', '0,2', 2
好厉害 !
是啊,有些数据没有排序。。
union all select '1', '0,1', 0
union all select '3', '0,1,3', 0
union all select '5', '0,2,5', 0
union all select '4', '0,2,4', 1
union all select '6', '0,6', 1
union all select '9', '0,2,4,9', 0
union all select '7', '0,2,4,9,7', 0
union all select '2', '0,2', 2
union all select '99','99',0
那就找第2个','事情的位置,再在第2个','号后加入显示顺序就好了~
--------------------------
如果有三个呢~~四个呢~~五个呢~~~HOHO
------------------
麻烦你帮我写一下好嘛? 我的sql函数是笨得要死!
你惨了,都上头条了,被经理发现了
-----------------------------------------------哈哈哈
declare @table table(商品id int,商品类别关系 varchar(50),显示顺序 int)
insert into @table
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',1 union all
select 7,'0,6',1 select 商品id,商品类别关系,显示顺序,(case when charindex(',',商品类别关系)>0 then
convert(int,left(商品类别关系,charindex(',',商品类别关系)-1)+convert(varchar(10),显示顺序)+
replace(right(商品类别关系,len(商品类别关系)-charindex(',',商品类别关系)),',',''))else
convert(int,商品类别关系+convert(varchar(10),显示顺序))
end )as order_byfrom @table
order by order_by
-- 个人意见,仅供参考
的解法较牛,不过对此问题的针对性太强。
我认可您的解法,不过对于第二层的顺序没有考虑进去,您看能否做如下修正:select a.* from # a
left join # b on
left(a.关系,3)=left(b.关系,3)
and len(b.关系)=3
order by b.顺序,len(a.关系),a.顺序不知可否?请指教!谢谢
不过0,1 和0,1,5被分开了,, 结果太长,我贴不上来
insert into table1 select '0', '0', 1
union all select '1', '0,1', 0
union all select '3', '0,1,3', 0
union all select '5', '0,2,5', 0
union all select '4', '0,2,4', 1
union all select '6', '0,6', 1
union all select '9', '0,2,4,9', 0
union all select '7', '0,2,4,9,7', 0
union all select '2', '0,2', 2
union all select '99','99',0create function orderby(@商品id int)
returns varchar(50)
as
begin
declare @var varchar(50)
set @var=''select @var=@var+rtrim(a.显示顺序)
from
(select top 1000 * from table1 order by 商品类别关系) a join
(select 商品类别关系
from table1 where 商品id=@商品id)b
on charindex(','+rtrim(a.商品id)+',',','+b.商品类别关系+',')>0
return @var
endselect * from table1 order by dbo.orderby(商品id)
-------
LZ先拿这个来应付吧~~以后慢慢来学~HOHO~~~
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
0 0 0
1 0,1 0
4 0,4 0
6 0,6 0
8 0,8 0
10 0,10 0
5 0,1,5 0
2 0,2 1
9 0,2,9 0
3 0,2,3 1
7 0,7 3(所影响的行数为 11 行)结果是这样,把上面的复制了,然后贴到记事本里,全屏就可以看到结果了
insert into @table
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',1 union all
select 7,'0,6',1 union all
select 8,'0,1,8',1 select 商品id,商品类别关系,显示顺序,(case when charindex(',',商品类别关系)>0 then
convert(int,left(商品类别关系,charindex(',',商品类别关系)-1)+convert(varchar(10),显示顺序)+
replace(right(商品类别关系,len(商品类别关系)-charindex(',',商品类别关系)),',',''))else
convert(int,商品类别关系+convert(varchar(10),显示顺序))
end )as order_byfrom @table
order by order_by
----brother2605(幽灵) 你的还是有问题
declare @table table(商品id int,商品类别关系 varchar(50),显示顺序 int)
insert into @table
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',2 union all
select 7,'0,6',1 union all
select 8,'0,3',3 如果层数不固定 ,好像真得用循环
你惨了,都上头条了,被经理发现了
---------------------
有才!!
SELECT * FROM TABLE ORDER DBO.MUCH(商品类别关系),显示顺序
create table t_test(商品id int,商品类别关系 varchar(50),显示顺序 int)
goinsert into t_test
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',1 union all
select 7,'0,6',1
go-- 先设定规则,每个级次使用4个字符来表示其排序位置
if exists(select name from sysobjects where name='fn_OrderBy'and type='FK')
drop function [dbo].[fn_OrderBy]
go
create function [dbo].[fn_OrderBy]()
returns @table table(商品id int,商品类别关系 varchar(50),显示顺序 int,次序 varchar(100))
as
begin
declare @v_table table(商品id int,商品类别关系 varchar(50),显示顺序 int,关系 varchar(50),次序 varchar(100))
insert into @v_table(商品id ,商品类别关系 ,显示顺序) select*from t_test
update @v_table set 次序='',关系=商品类别关系
update @v_table set 次序=right(10000+显示顺序,4) where charindex(',',关系)=0
while exists(select 1 from @v_table where charindex(',',关系)>0)
begin
update @v_table
set 次序=次序+right(10000+b.显示顺序,4)
from @v_table a inner join
(select 关系,显示顺序 from @v_table where charindex(',',关系)=0)b
on left(a.关系,charindex(',',a.关系)-1)=b.关系
where charindex(',',a.关系)>0
update @v_table set 关系='' where charindex(',',关系)=0
update @v_table set 关系=right(关系,len(关系)-charindex(',',关系))where charindex(',',关系)>0
end
update @v_table set 次序=次序+right(10000+显示顺序,4) where charindex(',',关系)=0
insert into @table
select 商品id,商品类别关系,显示顺序,次序 from @v_table
return
end
goselect * from [dbo].[fn_OrderBy]() order by 次序,商品类别关系
go
drop table t_test
go/*
商品id 商品类别关系 显示顺序 次序
----------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
0 0 0 00000000
1 0,1 0 00000000
3 0,1,3 0 000000000000
7 0,6 1 00000001
2 0,2 2 00000002
4 0,2,4 0 000000020000
6 0,2,3 1 000000020001
5 0,2,5 1 000000020001(8 row(s) affected)
*/
if exists(select name from sysobjects where name='fn_OrderBy'and type='FK')
改为:if exists(select name from sysobjects where name='fn_OrderBy'and type='TF')
商品:[id] 商品类别关系:[type] 显示顺序:[order]
0 0 0
1 0,1 0
2 0,2 2
3 0,1,3 0
4 0,2,4 0
5 0,2,5 1
6 0,6 1用下列语句即可得到你想要的结果:
select id, [type], t.[order],sort=s.[order]
from test t left join (
select distinct rjoin=substring([type],3,1),[order]
from test
where len(type)=3)s
on substring(t.[type],3,1)=s.rjoin
order by sort,len(type),t.[order]
-----------------
你测一下这组数据
insert into t_test
select 0, '0',0 union all
select 1,'0,1111',1 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',1 union all
select 7,'0,6',1
---------------------------------
declare @table table(商品id int,商品类别关系 varchar(50),显示顺序 int)
insert into @table
select 0, '0',0 union all
select 1,'0,11',1 union all
select 2,'0,2',2 union all
select 3,'0,1,3',0 union all
select 4,'0,2,4',0 union all
select 5,'0,2,5',1 union all
select 6,'0,2,3',1 union all
select 7,'0,6',1
我认为同一级次应该不会有相同的显示顺序,如,1,2和1,1 这两个应该不会有相同的显示顺序吧,否则要“显示顺序”干吗?
即使真实的数据是这样的,也没关系,稍加处理就可以了,函数功能不需要修改,只是在最后调用的时候修改一下排序规则就OK了,对你的问题使用如下语句便可实现:
select * from [dbo].[fn_OrderBy]() order by 次序,Convert(int,replace(商品类别关系,',',''))
create function fn_mstr(@stemp varchar(1000))
returns varchar(1000)
as
begin
declare @s int
declare @e int
declare @mstr varchar(1000)set @mstr = ''
set @s=1
set @stemp = @stemp + ','
while charindex(',',@stemp,@s) > 0
begin
set @e = charindex(',',@stemp,@s)
set @mstr = @mstr + right('0000'+substring(@stemp,@s,@e-@s),4)
set @s = @e +1
end
return @mstr
end
-------------------------------create function fn_order(@stemp varchar(100))
returns varchar(1000)
as
begin
declare @ostr varchar(1000)
declare @ms varchar(100)
declare @s intset @s =1
set @ostr = ''
set @ms = ''
set @stemp = @stemp +','while charindex(',',@stemp,@s)>0
begin
set @ms = (select 顺序 from mtable where 关系=substring(@stemp,1,charindex(',',@stemp,@s)-1))
--select 顺序 from mtable where 关系=substring(@stemp,1,charindex(',',@stemp,@s)-1)
if @@rowcount>0 --这里是基于一种假设,每一个级别都有上一级
set @ostr =@ostr + right('0000'+str(@ms,len(@ms)),4)
set @s = charindex(',',@stemp,@s) +1
end
return @ostr
end-------------------------
create table mtable(商品id int,关系 varchar(20),顺序 int)
insert into mtable
select 0, '0',0 union all
select 1,'0,1',0 union all
select 2,'0,4',0 union all
select 3,'0,6',0 union all
select 4,'0,8',0 union all
select 5,'0,10',0 union all
select 6,'0,1,5',0 union all
select 7,'0,2',1 union all
select 8,'0,2,9',0 union all
select 9,'0,2,3',1 union all
select 10,'0,7',3------------------------------------select * from mtable
order by
substring(dbo.fn_order(关系)+'000000000000000000000000',1,24),
substring(dbo.fn_mstr(关系)+'000000000000000000000000',1,24)------------------------------------
0 0 0
1 0,1 0
6 0,1,5 0
2 0,4 0
3 0,6 0
4 0,8 0
5 0,10 0
7 0,2 1
8 0,2,9 0
9 0,2,3 1
10 0,7 3
他正在问怎么处理你呢, 看看他有好办法吧
http://community.csdn.net/Expert/topic/5699/5699066.xml?temp=.3755609
returns varchar(50)
as
begin
declare @var varchar(100)
set @var=''select @var=@var+显示顺序 from
(select top 100 rtrim(显示顺序)显示顺序
from table1 where charindex(','+rtrim(商品id)+',',
(select ','+商品类别关系+',' from table1 where 商品id=@商品id))>0
order by 商品类别关系)a
return @var
end
---------------------
这样写可能快些
select *
from table1 order by dbo.orderby(商品id)
,父节点其实不用放路径的,放父id用递归一下出来了,给父路径反而麻烦,这种问题只能是考试题,实际工作中如果的确有这样的需求,表结构设计成这样是违反第一范式的,多值字段的带来的插入和删除很麻烦,另外如果表的结构是4层就麻烦了,ljsql(第 1 行: '脑子' 附近有语法错误。) 的解法是不行的,我这里在 ljsql(第 1 行: '脑子' 附近有语法错误。) 的基础上给一个4层结构的解法:create table jk(商品id int, 关系 varchar(20), 顺序 int )insert into jkselect 0, '0' , 0 union all
select 1 , '0,1' , 0 union all
select 2 , '0,2' , 2 union all
select 3 , '0,1,3' , 0 union all
select 4 , '0,2,4' , 0 union all
select 5 , '0,2,5' , 1 union all
select 6 , '0,6' , 1 union all
select 7, '0,1,3,7' , 0 union all
select 8, '0,2,4,8' , 0 union all
select 9, '0,6,9' , 0 union all
select 10, '0,1,3,10' , 1 -------------select m.商品id,m.关系,m.顺序 , m.id2,m.关系2, m.顺序2 , c.商品id,c.关系,c.顺序
from
(
select a.商品id,a.关系,a.顺序, b.商品id as id2,b.关系 as 关系2, b.顺序 as 顺序2
from jk a left join jk b
on left(a.关系,5) = left(b.关系,5) and len(b.关系)=5
) m left join jk c
on left(m.关系,3) = left(c.关系,3) and len(c.关系)=3 order by c.顺序,m.顺序2,m.顺序
select 1 , '0,1' , 0 union all
select 2 , '0,2' , 2 union all
select 3 , '0,1,3' , 0 union all
select 4 , '0,2,4' , 1 union all
select 5 , '0,2,5' , 0 union all
select 6 , '0,6' , 1 union all
select 7, '0,1,3,7' , 0 union all
select 8, '0,2,4,8' , 0 union all
select 9, '0,6,9' , 0 union all
select 10, '0,1,3,10' , 1
你试试这个...
returns varchar(50)
as
begin
declare @var varchar(100)
set @var=''select @var=@var+显示顺序 from
(select top 100 rtrim(显示顺序)显示顺序
from table1 where charindex(','+rtrim(商品id)+',',
','+@商品类别关系+',')>0
order by 商品类别关系)a
return @var
end
select *
from table1 order by dbo.orderby(商品类别关系)
http://community.csdn.net/Expert/topic/5699/5699066.xml?temp=.1141931草,这2个帖子就是一个人写的,无聊!
估计刚被公司开除了,正YY呢!
http://community.csdn.net/Expert/topic/5698/5698584.xml?temp=.3546106
看得出来,楼主不是诚意来提问的,至少他并没有认真对待每一个给他提供的答案或者,此贴本就是哗众取宠,炫耀,忽悠来的.下面给出答案,给各位迫切想知道答案的朋友,关于答案的详细解释可以参考上面提供的链接drop table test2
GO
create table test2 (商品id int, 商品类别关系 varchar(30), 显示顺序 int)
GO
insert into test2 values(0,'0',0)
insert into test2 values(1,'0,1',0)
insert into test2 values(2,'0,2',2)
insert into test2 values(3,'0,1,3',0)
insert into test2 values(4,'0,20',3)
insert into test2 values(4,'0,20,4',0)
insert into test2 values(5,'0,2,5',1)
insert into test2 values(7,'1',4)
insert into test2 values(6,'0,6',1)
insert into test2 values(8,'2',3)
insert into test2 values(9,'2,1',1)
insert into test2 values(10,'2,2',0)
insert into test2 values(11,'2,1,2',3)
insert into test2 values(12,'3',2)SELECT *
FROM
(
SELECT *,REVERSE(REPLICATE('0',LEN(商品类别关系)-LEN(REPLACE(商品类别关系,',','')) + 1 -LEN(TempOrder)) + TempOrder) NewOrder
FROM
(
SELECT *,
CAST((SELECT SUM(显示顺序 * POWER(10,LEN(商品类别关系)-LEN(REPLACE(商品类别关系,',','')) ))
FROM test2 t
WHERE u.商品类别关系 LIKE t.商品类别关系 + '%') AS VARCHAR) AS TempOrder
FROM test2 u
) x
) y
ORDER BY NewOrder
http://community.csdn.net/Expert/topic/5698/5698584.xml?temp=.3546106
看得出来,楼主不是诚意来提问的,至少他并没有认真对待每一个给他提供的答案或者,此贴本就是哗众取宠,炫耀,忽悠来的.下面给出答案,给各位迫切想知道答案的朋友,关于答案的详细解释可以参考上面提供的链接drop table test2
GO
create table test2 (商品id int, 商品类别关系 varchar(30), 显示顺序 int)
GO
insert into test2 values(0,'0',0)
insert into test2 values(1,'0,1',0)
insert into test2 values(2,'0,2',2)
insert into test2 values(3,'0,1,3',0)
insert into test2 values(4,'0,20',3)
insert into test2 values(4,'0,20,4',0)
insert into test2 values(5,'0,2,5',1)
insert into test2 values(7,'1',4)
insert into test2 values(6,'0,6',1)
insert into test2 values(8,'2',3)
insert into test2 values(9,'2,1',1)
insert into test2 values(10,'2,2',0)
insert into test2 values(11,'2,1,2',3)
insert into test2 values(12,'3',2)SELECT *
FROM
(
SELECT *,REVERSE(REPLICATE('0',LEN(商品类别关系)-LEN(REPLACE(商品类别关系,',','')) + 1 -LEN(TempOrder)) + TempOrder) NewOrder
FROM
(
SELECT *,
CAST((SELECT SUM(显示顺序 * POWER(10,LEN(商品类别关系)-LEN(REPLACE(商品类别关系,',','')) ))
FROM test2 t
WHERE u.商品类别关系 LIKE t.商品类别关系 + '%') AS VARCHAR) AS TempOrder
FROM test2 u
) x
) y
ORDER BY NewOrder
根本没往那方面去想。真失败!!!!!!!
select 商品id,商品类别关系,显示顺序
from (
select *,
cast((select sum(显示顺序 * power(10,len(商品类别关系)-len(replace(商品类别关系,',','')) ))
from test2 t
where u.商品类别关系 like t.商品类别关系 + '%') as varchar) as temporder
from test2 u
) x
order by reverse(replicate('0',len(商品类别关系)-len(replace(商品类别关系,',','')) + 1 -len(temporder)) + temporder)