declare @t table
(id int,pid int,price int)insert into @t values (1,1,15)
insert into @t values (2, 1, 20)
insert into @t values (3, 1, 25)
insert into @t values (4, 2, 13)
insert into @t values (5, 2, 28)
insert into @t values (6, 2, 33)
insert into @t values (7, 3, 44)
insert into @t values (8, 3, 66)
insert into @t values (9, 3, 77)
select pid,max(price) as bigprice,min(price) as lowprice from @t group by pidpid bigprice lowprice
----------- ----------- -----------
1 25 15
2 33 13
3 77 44(所影响的行数为 3 行)
(id int,pid int,price int)insert into @t values (1,1,15)
insert into @t values (2, 1, 20)
insert into @t values (3, 1, 25)
insert into @t values (4, 2, 13)
insert into @t values (5, 2, 28)
insert into @t values (6, 2, 33)
insert into @t values (7, 3, 44)
insert into @t values (8, 3, 66)
insert into @t values (9, 3, 77)
select pid,max(price) as bigprice,min(price) as lowprice from @t group by pidpid bigprice lowprice
----------- ----------- -----------
1 25 15
2 33 13
3 77 44(所影响的行数为 3 行)
from 表a,
(select parentid,max(price) as bigprice,min(price) as lowprice from 表b group by parentid) b where a.parentid=b.parentid
insert into #c
select 1,1,15
union all
select 2,1,20
union all
select 3,1,25
union all
select 4,2,13
union all
select 5,2,54
union all
select 6,2,31
--select * from #c order by a ascselect distinct parentid ,max(price),min(price) from #c group by parentid
,isnull(B.bigprice,0) as 'bigprice'
,isnull(B.lowprice,0) as 'lowprice'
from A
left join (
select parentid
,max(price) as 'bigprice'
,min(price) as 'lowprice'
from B
group by parentid
)B on A.parentid =B.parentid
SET (bigprice,lowprice)=
(
SELECT MAX(price),MIN(price)
FROM B表 b
WHERE a.parentid=b.parentid
GROUP BY b.parentid
)
create view a as select pid,max(price) as bigprice,min(price) as lowprice from @t group by pid--查询
select * from a