create table tb(id int,name varchar(10),price int) insert into tb values(1,'a',100) insert into tb values(2,'a',200) insert into tb values(3,'a',300) go select name, max(case when px = 1 then price else ' ' end) 'price1 ', max(case when px = 2 then price else ' ' end) 'price2 ', max(case when px = 3 then price else ' ' end) 'price3 ' from ( select px=(select count(1) from tb where name=a.name and price <a.price)+1,* from tb a ) t group by name order by count(id) desc --删除数据 go drop table tb /* name price1 price2 price3 ---------- ----------- ----------- ----------- a 100 200 300(1 row(s) affected) */
select name ,price1=isnull(sum(case id when 1 then price end),0), price2=isnull(sum(case id when 2 then price end),0), price3=isnull(sum(case id when 3 then price end),0) from table group by name 这样试试看!
declare @a table(id int,name varchar(10),price int) insert @a select 1,'a',100 union all select 2,'a',200 union all select 3,'a',300select name,price1=max(case price when price then 100 else 0 end), price2=max(case price when price then 200 else 0 end), price3=max(case price when price then 300 else 0 end) from @a a group by name /* name price1 price2 price3 ---------- ----------- ----------- ----------- a 100 200 300(1 row(s) affected) */
select distinct name, price1=(select price from tb where id=1), price2=(select price from tb where id=2), price3=(select price from tb where id=3) from tb
create table tb(id int,name varchar(10),price int)
insert into tb values(1,'a',100)
insert into tb values(2,'a',200)
insert into tb values(3,'a',300)
go
select name,
max(case when px = 1 then price else ' ' end) 'price1 ',
max(case when px = 2 then price else ' ' end) 'price2 ',
max(case when px = 3 then price else ' ' end) 'price3 '
from
(
select px=(select count(1) from tb where name=a.name and price <a.price)+1,* from tb a
) t
group by name
order by count(id) desc
--删除数据
go
drop table tb
/*
name price1 price2 price3
---------- ----------- ----------- -----------
a 100 200 300(1 row(s) affected)
*/
price2=isnull(sum(case id when 2 then price end),0),
price3=isnull(sum(case id when 3 then price end),0)
from table group by name
这样试试看!
insert @a select 1,'a',100
union all select 2,'a',200
union all select 3,'a',300select name,price1=max(case price when price then 100 else 0 end),
price2=max(case price when price then 200 else 0 end),
price3=max(case price when price then 300 else 0 end)
from @a a
group by name
/*
name price1 price2 price3
---------- ----------- ----------- -----------
a 100 200 300(1 row(s) affected)
*/
select distinct name,
price1=(select price from tb where id=1),
price2=(select price from tb where id=2),
price3=(select price from tb where id=3)
from tb