100分求一个排序问题

Question

select 名称 , 型号 from
(
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('*',型号) - 1) as int), 型号2 = cast(substring(型号,charindex('*',型号) + 1,charindex('cm',型号) - charindex('*',型号) - 1) as int) from tb where charindex('*',型号) > 0
  union all
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('cm',型号) - 1) as int), 型号2 = 0 from tb where charindex('*',型号) <= 0
) t
order by 名称 , 型号1 , 型号2

Answer 1

select   *   from   玩具表   order   by   名称,left(型号,CHARINDEX('*', 型号)-1)

Answer 2

fcuandy 
人, 无完人;学, 无止境 
等 级：
 发表于：2007-12-12 11:06:421楼 得分:0 
你自己排的那个是字符型,当然100比50小了,呵呵. 
 
----------------------
对呀,它已经是字符了,所以现在没办法了,只有想别的招

Answer 3

create table tb(名称 varchar(20) , 型号 varchar(20))
insert into tb values('玩具娃娃',     '100*800cm') 
insert into tb values('玩具娃娃',     '100*60cm ')
insert into tb values('玩具娃娃',     '50*300cm ')
insert into tb values('玩具小狗',     '200*100mm ')
insert into tb values('玩具小狗',     '230*50mm ')
insert into tb values('玩具小狗',     '50*100mm ')
insert into tb values('玩具熊'  ,       '100cm ')
insert into tb values('玩具熊'  ,       '60cm ')
goselect 名称 , 型号 from
(
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('*',型号) - 1) as int) from tb where charindex('*',型号) > 0
  union all
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('cm',型号) - 1) as int) from tb where charindex('*',型号) <= 0 and charindex('cm',型号) > 0
) t
order by 名称 , 型号1drop table tb
/*
名称                   型号                   
-------------------- -------------------- 
玩具娃娃                 50*300cm 
玩具娃娃                 100*800cm
玩具娃娃                 100*60cm 
玩具小狗                 50*100mm 
玩具小狗                 200*100mm 
玩具小狗                 230*50mm 
玩具熊                  60cm 
玩具熊                  100cm （所影响的行数为 8 行）*/

Answer 4

--就测试数据的三种结果的排序
create table tb(名称 varchar(20) , 型号 varchar(20))
insert into tb values('玩具娃娃',     '100*800cm') 
insert into tb values('玩具娃娃',     '100*60cm ')
insert into tb values('玩具娃娃',     '50*300cm ')
insert into tb values('玩具小狗',     '200*100mm ')
insert into tb values('玩具小狗',     '230*50mm ')
insert into tb values('玩具小狗',     '50*100mm ')
insert into tb values('玩具熊'  ,       '100cm ')
insert into tb values('玩具熊'  ,       '60cm ')
goselect 名称 , 型号 from
(
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('*',型号) - 1) as int) , 型号2 = cast(substring(型号,charindex('*',型号) + 1,charindex('cm',型号) - charindex('*',型号) - 1) as int) from tb where charindex('*',型号) > 0 and charindex('cm',型号) > 0
  union all
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('*',型号) - 1) as int) , 型号2 = cast(substring(型号,charindex('*',型号) + 1,charindex('mm',型号) - charindex('*',型号) - 1) as int) from tb where charindex('*',型号) > 0 and charindex('mm',型号) > 0
  union all
  select 名称 , 型号 , 型号1 = cast(left(型号,charindex('cm',型号) - 1) as int) , 型号2 = 0  from tb where charindex('*',型号) <= 0 and charindex('cm',型号) > 0
) t
order by 名称 , 型号1 , 型号2drop table tb
/*
名称                   型号                   
-------------------- -------------------- 
玩具娃娃                 50*300cm 
玩具娃娃                 100*60cm 
玩具娃娃                 100*800cm
玩具小狗                 50*100mm 
玩具小狗                 200*100mm 
玩具小狗                 230*50mm 
玩具熊                  60cm 
玩具熊                  100cm （所影响的行数为 8 行）
*/

Answer 5

DECLARE @t TABLE(名称 VARCHAR(10), 型号 VARCHAR(20))
INSERT @t SELECT '玩具娃娃',     '100*800cm' 
UNION ALL SELECT '玩具娃娃' ,    '100*60cm' 
UNION ALL SELECT '玩具娃娃'  ,   '50*300cm' 
UNION ALL SELECT '玩具娃娃'  ,   '50*200cm' 
UNION ALL SELECT '玩具小狗'   ,  '200*100mm' 
UNION ALL SELECT '玩具小狗'    , '230*50mm' 
UNION ALL SELECT '玩具小狗'     ,'50*100mm' 
UNION ALL SELECT '玩具熊'        , '100cm' 
UNION ALL SELECT '玩具熊'         ,'60cm'SELECT * FROM @t 
ORDER BY 名称,CAST(STUFF(型号,CHARINDEX('*',型号),100,'') AS INT),
CAST(PARSENAME(REPLACE(REPLACE(REPLACE(型号,'*','.'),'cm',''),'mm',''),1) AS INT)
/*
玩具娃娃 50*200cm
玩具娃娃 50*300cm
玩具娃娃 100*60cm
玩具娃娃 100*800cm
玩具小狗 50*100mm
玩具小狗 200*100mm
玩具小狗 230*50mm
玩具熊 60cm
玩具熊 100cm
*/

Answer 6

Create table ta(名称 nvarchar(50)       ,      型号  nvarchar(50))
insert into ta 
select N'玩具娃娃'   ,  '100*800cm' 
union select N'玩具娃娃'   ,  '100*60cm' 
union select N'玩具娃娃'   ,  '50*300cm' 
union select N'玩具小狗'   ,  '200*100mm' 
union select N'玩具小狗'   ,  '230*50mm' 
union select N'玩具小狗'   ,  '50*100mm' 
union select N'玩具熊'     ,    '100cm' 
union select N'玩具熊'     ,    '60cm' select *,cONVERT(INT,REPLACE(SUBSTRING(型号,1,cHARINDEX('*',型号)),'*','')),
cONVERT(INT,RIGHT(LEFT(型号,LEN(型号)-2),LEN(型号)-2-ISNULL(cHARINDEX('*',型号),1)))
FROM ta
ORDER BY 名称,cONVERT(INT,REPLACE(SUBSTRING(型号,1,cHARINDEX('*',型号)),'*','')),
cONVERT(INT,RIGHT(LEFT(型号,LEN(型号)-2),LEN(型号)-2-ISNULL(cHARINDEX('*',型号),1)))

Answer 7

一句搞定：
create table tb(名称 varchar(20) , 型号 varchar(20))
insert into tb values('玩具娃娃',     '100*800cm') 
insert into tb values('玩具娃娃',     '100*60cm ')
insert into tb values('玩具娃娃',     '50*300cm ')
insert into tb values('玩具小狗',     '200*100mm ')
insert into tb values('玩具小狗',     '230*50mm ')
insert into tb values('玩具小狗',     '50*100mm ')
insert into tb values('玩具熊'  ,       '100cm ')
insert into tb values('玩具熊'  ,       '60cm ')
goselect * from tb
order by 名称
, cast(left(型号, patindex('%[*cm]%',型号)-1) as int)
, cast(substring(型号, charindex('*',型号)+1, patindex('%[cm]%', 型号)-charindex('*',型号)-1)  as int)
drop table tb/*
名称                   型号
-------------------- --------------------
玩具娃娃                 50*300cm 
玩具娃娃                 100*60cm 
玩具娃娃                 100*800cm
玩具小狗                 50*100mm 
玩具小狗                 200*100mm 
玩具小狗                 230*50mm 
玩具熊                  60cm 
玩具熊                  100cm (8 row(s) affected)
*/

Answer 8

Create table ta(名称 nvarchar(50)       ,      型号  nvarchar(50))
insert into ta 
select N'玩具娃娃'   ,  '100*800cm' 
union select N'玩具娃娃'   ,  '100*60cm' 
union select N'玩具娃娃'   ,  '50*300cm' 
union select N'玩具小狗'   ,  '200*100mm' 
union select N'玩具小狗'   ,  '230*50mm' 
union select N'玩具小狗'   ,  '50*100mm' 
union select N'玩具熊'     ,    '100cm' 
union select N'玩具熊'     ,    '60cm' 
select * ,substring(型号,1,case when charindex('*',型号)= 0 then charindex('m',型号)-2 else charindex('*',型号) - 1 end ),
substring(型号,charindex('*',型号)+1,charindex('m',replace(型号,'mm','cm'))-2 -charindex('*',型号))
from ta
order by 
cast(substring(型号,1,case when charindex('*',型号)= 0 then charindex('m',型号)-2 else charindex('*',型号) - 1 end ) as int),
cast(substring(型号,charindex('*',型号)+1,charindex('m',replace(型号,'mm','cm'))-2 -charindex('*',型号)) as int)drop table ta
/*名称                                                 型号                                                                                                                                                       
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
玩具小狗                                               50*100mm                                           50                                                 100
玩具娃娃                                               50*300cm                                           50                                                 300
玩具熊                                                60cm                                               60                                                 60
玩具娃娃                                               100*60cm                                           100                                                60
玩具熊                                                100cm                                              100                                                100
玩具娃娃                                               100*800cm                                          100                                                800
玩具小狗                                               200*100mm                                          200                                                100
玩具小狗                                               230*50mm                                           230                                                50（所影响的行数为 8 行）
*/

Answer 9

Create table ta(名称 nvarchar(50)       ,      型号  nvarchar(50))
insert into ta 
select N'玩具娃娃'   ,  '100*800cm' 
union select N'玩具娃娃'   ,  '100*60cm' 
union select N'玩具娃娃'   ,  '50*300cm' 
union select N'玩具小狗'   ,  '200*100mm' 
union select N'玩具小狗'   ,  '230*50mm' 
union select N'玩具小狗'   ,  '50*100mm' 
union select N'玩具熊'     ,    '100cm' 
union select N'玩具熊'     ,    '60cm' 
select * ,substring(型号,1,case when charindex('*',型号)= 0 then charindex('m',型号)-2 else charindex('*',型号) - 1 end ),
substring(型号,charindex('*',型号)+1,charindex('m',replace(型号,'mm','cm'))-2 -charindex('*',型号))
from ta
order by 
cast(substring(型号,1,case when charindex('*',型号)= 0 then charindex('m',型号)-2 else charindex('*',型号) - 1 end ) as int),
cast(substring(型号,  case when charindex('*',型号)> 0 then charindex('*',型号)+1 else 100 end,charindex('m',replace(型号,'mm','cm'))-2 -charindex('*',型号) ) as int)drop table ta
/*名称                                                 型号                                                                                                                                                       
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
玩具小狗                                               50*100mm                                           50                                                 100
玩具娃娃                                               50*300cm                                           50                                                 300
玩具熊                                                60cm                                               60                                                 60
玩具熊                                                100cm                                              100                                                100
玩具娃娃                                               100*60cm                                           100                                                60
玩具娃娃                                               100*800cm                                          100                                                800
玩具小狗                                               200*100mm                                          200                                                100
玩具小狗                                               230*50mm                                           230                                                50（所影响的行数为 8 行）
*/

Answer 10

Create table ta(名称 nvarchar(50)       ,      型号  nvarchar(50))
insert into ta 
select N'玩具娃娃'   ,  '100*800cm' 
union select N'玩具娃娃'   ,  '100*60cm' 
union select N'玩具娃娃'   ,  '50*300cm' 
union select N'玩具小狗'   ,  '200*100mm' 
union select N'玩具小狗'   ,  '230*50mm' 
union select N'玩具小狗'   ,  '50*100mm' 
union select N'玩具熊'     ,    '100cm' 
union select N'玩具熊'     ,    '60cm' 
select * ,substring(型号,1,case when charindex('*',型号)= 0 then charindex('m',型号)-2 else charindex('*',型号) - 1 end ),
substring(型号,  case when charindex('*',型号)> 0 then charindex('*',型号)+1 else 100 end,charindex('m',replace(型号,'mm','cm'))-2 -charindex('*',型号) ) 
from ta
order by 
cast(substring(型号,1,case when charindex('*',型号)= 0 then charindex('m',型号)-2 else charindex('*',型号) - 1 end ) as int),
cast(substring(型号,  case when charindex('*',型号)> 0 then charindex('*',型号)+1 else 100 end,charindex('m',replace(型号,'mm','cm'))-2 -charindex('*',型号) ) as int)drop table ta
/*名称                                                 型号                                                                                                                                                       
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
玩具小狗                                               50*100mm                                           50                                                 100
玩具娃娃                                               50*300cm                                           50                                                 300
玩具熊                                                60cm                                               60                                                 60
玩具熊                                                100cm                                              100                                                100
玩具娃娃                                               100*60cm                                           100                                                60
玩具娃娃                                               100*800cm                                          100                                                800
玩具小狗                                               200*100mm                                          200                                                100
玩具小狗                                               230*50mm                                           230                                                50（所影响的行数为 8 行）
*/

Answer 11

create table tb(名称 varchar(20) , 型号 varchar(20))
insert into tb values('玩具娃娃',     '100*800cm') 
insert into tb values('玩具娃娃',     '100*60cm')
insert into tb values('玩具娃娃',     '50*300cm')
insert into tb values('玩具小狗',     '200*100mm')
insert into tb values('玩具小狗',     '230*50mm ')
insert into tb values('玩具小狗',     '50*100mm ')
insert into tb values('玩具熊'  ,       '100cm ')
insert into tb values('玩具熊'  ,       '60cm ')
select * from tb order by 名称,
cast(substring(型号,1,patindex('%[^0-9]%',型号)-1) as int),
cast(substring(型号,charindex('*',型号)+1,patindex('%[cm]%', 型号)-charindex('*',型号)-1)  as int)

Answer 12

忘说了一点,型号有时还有100x20x80cm这类形式的.

Answer 13

试试我在7楼写的, 如果第三列数不参与排序,那么那条就可以了.
如果要参与,那还多一条排序条件,语句的内容为把我语句中的第三个排序列中parsename的第二个参数改为2即可.