select (select min(idnum) from product where typeno = a.typeno)
,(select max(idnum) from product where typeno = a.typeno)
,typeno,max(pname)
from product a
group by typeno
,(select max(idnum) from product where typeno = a.typeno)
,typeno,max(pname)
from product a
group by typeno
+ '-' +cast((select max(idnum) from product where typeno = a.typeno) as varchar)
,typeno,max(pname),min(pname)
from product a
group by typeno
我查出来是这样的结果集:
1-3 1 test test
4-6 2 test abc
select cast((select min(idnum) from product where (typeno = a.typeno) and (pname=a.pname)) as varchar)
+ '-' +cast((select max(idnum) from product where (typeno = a.typeno) and (pname=a.pname)) as varchar)
as Number ,typeno,max(pname),min(pname)
from product a
group by typeno,pname
order by Number
from product a
group by typeno,pname
from Product
group by TypeNo,PName
order by Min(IDNum)
试验一下
我的机子通过
pro2K+SQL 2000
结果如下
:
你想要的东西 TypeNo PName
1-3 1 test
4-5 2 test
6-6 2 abc
+ '-' +cast((select max(idnum) from product where typeno = a.typeno) as varchar)
,typeno,pname
from product a
group by typeno,pname
,TypeNo,PName
from Product
group by TypeNo,PName
order by Min(IDNum)
,TypeNo int,PName varchar(100))
insert into @product
select 1,1,'test'
union all select 2,1,'test'
union all select 3,1,'test'
union all select 4,2,'test'
union all select 5,2,'test'
union all select 6,2,'abc'select idnum=cast(min(idnum) as varchar)+'-'+cast(max(idnum) as varchar)
,TypeNo,PName
from @Product
group by TypeNo,PName
order by Min(IDNum)/*--测试结果
idnum TypeNo PName
--------- ----------- --------
1-3 1 test
4-5 2 test
6-6 2 abc(所影响的行数为 3 行)
--*/