create table #table
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
)select * from #table
truncate table #table
insert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19
--获得数据
A B C 25
B B D 25
A B D 19
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
)select * from #table
truncate table #table
insert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19
--获得数据
A B C 25
B B D 25
A B D 19
from ta
group by name,id,price
*
from
#table t
where
priceName=(select min(priceName) from #table where name=t.name and id=t.id and price=t.price)
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
) select * from #table
truncate table #table
insert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19 select
*
from
#table t
where
priceName=(select min(priceName) from #table where name=t.name and id=t.id and price=t.price)
/*name id price priceName
-------------------- -------------------- -------------------- -----------
A B C 18
A B D 19
B B D 12(3 行受影响)*/
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
) goinsert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19
select name ,id,price ,max( priceName) priceName
from #table
group by name ,id,price
order by id , price , name descdrop table #table
/*
name id price priceName
-------------------- -------------------- -------------------- -----------
A B C 25
B B D 25
A B D 19(所影响的行数为 3 行)*/
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
)
truncate table #table
insert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19 select name,id,price ,max(priceName )
from #table
group by name,id,pricedrop table #table/*name id price
-------------------- -------------------- -------------------- -----------
A B C 25
A B D 19
B B D 25(3 行受影响)
*/
create table #table
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
) select * from #table
truncate table #table
insert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19 select
*
from
#table t
where
priceName=(select max(priceName) from #table where name=t.name and id=t.id and price=t.price)
order by 1drop table #table
/*name id price priceName
-------------------- -------------------- -------------------- -----------
A B D 19
A B C 25
B B D 25(3 行受影响)(3 行受影响)*/
select name,id,price,max(pricename) from #table group by name,id,price
select name,id,price,max(pricename) from #table group by name,id,price
order by priceName desc
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
) goinsert into #table select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19
select name ,id,price ,max( priceName) priceName
from #table
group by name ,id,price
order by id , price , name descdrop table #table
name id price priceName
-------------------- -------------------- -------------------- -----------
A B C 25
B B D 25
A B D 19(所影响的行数为 3 行)
drop table tb
gocreate table tb
(
name varchar(20) ,
id varchar(20),
price varchar(20),
priceName int
) insert into tb select 'A','B','C',25 union
select 'A','B','C',18 union
select 'B','B','D',25 union
select 'B','B','D',12 union
select 'A','B','D',19
select
*
from
tb t
where
priceName=(select max(priceName) from tb where name=t.name and id=t.id and price=t.price) order by priceName desc