试一下 select type,num,MakePerson from (select distinct type,num,MakePerson from 表) aa where num=(select top 1 num from (select distinct type,num,MakePerson from 表) bb where type=aa.type) order by num
select 字段1,字段2,字段3 from (select distinct 字段1,字段2,字段3 from 表) aa where num=(select top 1 字段2 from (select distinct 字段1,字段2,字段3 from 表) bb where 字段1=aa.字段1) order by num
select type,num from table where type='相框 像框Size10' union select type,num from table where type='相册 Size10彩色人生金属' union select type,num from table where type='礼品 礼品1' union select type,num from table where type='钥匙扣' union select type,num from table where type='喜贴' order by num
sorry,忘了在union后面加上all就ok了
To chenyuandxm(一剑平江湖) :像框Size10是可变的,可以有 像框Size30 像框Size40 像框Size50 像框Size60 相册后面的一样是变化的
select type,num,MakePerson from 表 aa where num=(select top 1 type from 表 where substring(type,1,charindex(' ',type+' ')-1) =substring(aa.type' ',1,charindex(' ',aa.type+' ')-1)) order by num
try select * from 表 order by (case substring(type,1,charindex(' ',type+' ')-1) when '相框' then 0 when '相册' then 1 when '礼品' then 2 when '钥匙扣' then 3 when '喜贴' then 5 --when '嘟嘟' then 6 end)
相框 像框Size10 1
相框 像框Size10 1
相框 像框Size10 1相册 Size10彩色人生金属 1
相册 Size10彩色人生金属 1
相册 Size10彩色人生金属 1
礼品 礼品1 1
礼品 礼品1 1
钥匙扣 10
喜贴 30
select type,num,MakePerson
from (select distinct type,num,MakePerson from 表) aa
where num=(select top 1 num from (select distinct type,num,MakePerson from 表) bb
where type=aa.type)
order by num
from (select distinct 字段1,字段2,字段3 from 表) aa
where num=(select top 1 字段2 from (select distinct 字段1,字段2,字段3 from 表) bb
where 字段1=aa.字段1)
order by num
union
select type,num from table where type='相册 Size10彩色人生金属'
union
select type,num from table where type='礼品 礼品1'
union
select type,num from table where type='钥匙扣'
union
select type,num from table where type='喜贴' order by num
相册后面的一样是变化的
type num makePerson
相框 size10 1 苗
相框 size20 1 苗
相框 size30 2 王
相册 古典艺术 1 苗
相册 罗曼史
相册 浪漫花季 1 苗
礼品 礼品1 1 王
礼品 礼品2 1
礼品 礼品3 1
钥匙扣 10
喜贴 40
from 表 aa
where num=(select top 1 type from 表
where substring(type,1,charindex(' ',type+' ')-1)
=substring(aa.type' ',1,charindex(' ',aa.type+' ')-1))
order by num
select * from 表
order by (case substring(type,1,charindex(' ',type+' ')-1)
when '相框' then 0
when '相册' then 1
when '礼品' then 2
when '钥匙扣' then 3
when '喜贴' then 5
--when '嘟嘟' then 6
end)