select a.GoodsId, b.price as 最低价格,b.hospitalname as 医院名称, a.price as 最高价格,a.hospitalname as 医院名称 from (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price) a jion (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price) b on a.goodsid=b.goodsid
改正一下 select a.GoodsId, b.price as 最低价格,b.hospitalname as 医院名称, a.price as 最高价格,a.hospitalname as 医院名称 from (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) a join (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) b on a.goodsid=b.goodsid
select * from (select GoodsId, max(price)[maxprice], 医院名称 from tb group by GoodsId,医院名称)a, (select GoodsId, min(price)[minprice], 医院名称 from tb group by GoodsId,医院名称)b where a.GoodsId=b.GoodsId
晕,还是错了,再改一下 select a.GoodsId, b.price as 最低价格,b.hospitalname as 医院名称, a.price as 最高价格,a.hospitalname as 医院名称 from (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) a join (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) b on a.goodsid=b.goodsid
select max(price) price,GoodsId,HospitalName from 表名称 group by GoodsId,HospitalName union all select min(price) price,GoodsId,HospitalName from 表名称 group by GoodsId,HospitalName
select a.GoodsId, b.price as 最低价格,b.hospitalname as 医院名称, a.price as 最高价格,a.hospitalname as 医院名称 from (Select GoodsID,HospitalName,Price,Row_number() over(partition by GoodsID order by price asc) as num1 from TB_HospitalLoginPrice) a join (Select GoodsID,HospitalName,Price,Row_number() over(partition by GoodsID order by price desc) as num1 from TB_HospitalLoginPrice) b on a.goodsid=b.goodsid and a.num1=1 and b.num1=1
测试一下啊 select a.GoodsId, b.price as 最低价格,b.hospitalname as 医院名称, a.price as 最高价格,a.hospitalname as 医院名称 from (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) a join (select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price<t.price)) b on a.goodsid=b.goodsid
if exists(select * from sysobjects where [name]='Hospital') drop table Hospital go create table hospital ( GoodsId varchar(10), HospitalID varchar(10), HospitalName varchar(50), AreaID varchar(10), Price decimal ) go insert into Hospital(GoodsId,HospitalID,HospitalName,AreaID,Price) ( select '91602','H0005','aa','150100',1.00 union select '91602','H0001','bb','150101',14.00 union select '91602','H0002','cc','150104',7.00 union select '91603','H0004','dd','150102',11.00 union select '91603','H0003','ee','150103',18.00 union select '91604','H0002','ff','150100',2.00 union select '91603','H0004','gg','150104',15.00 union select '91603','H0001','hh','150103',8.00 union select '91603','H0005','ii','150102',12.00 union select '91602','H0006','jj','150121',15.00 union select '91604','H0008','kk','150122',3.00 union select '91604','H0009','ll','150105',10.00 union select '91604','H0007','mm','150106',9.00 ) go select hoster.GoodsId,hoster.HospitalName,hoster.Price1,Hospital.HospitalName,hoster.Price2 from(select Hospital.HospitalName,hs.GoodsId,hs.Price1,hs.Price2 from(select hs1.GoodsId,price1,price2 from (select GoodsId,max(Price) Price1 from Hospital group by GoodsId) as hs, (select GoodsId,min(Price) Price2 from Hospital group by GoodsId) as hs1 where hs.GoodsId=hs1.GoodsId) as hs,Hospital where hs.GoodsId=Hospital.GoodsId and hs.Price1=Hospital.Price) as hoster,Hospital where hoster.GoodsId=Hospital.GoodsId and hoster.Price2=Hospital.Price go
select a.GoodsId,b.macprice as 最高价格,b.医院名称,c.minprice as 最低价格,c.医院名称 from (select distinct GoodsId from tb) a left join (select GoodsId, max(price) as maxprice, 医院名称 from tb group by GoodsId,医院名称) b on a.GoodsId=b.GoodsId left join (select GoodsId, min(price) as minprice, 医院名称 from tb group by GoodsId,医院名称) c on a.GoodsId=c.GoodsId这个样子应该可以的!
b.price as 最低价格,b.hospitalname as 医院名称,
a.price as 最高价格,a.hospitalname as 医院名称
from
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price) a
jion
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price) b
on a.goodsid=b.goodsid
select a.GoodsId,
b.price as 最低价格,b.hospitalname as 医院名称,
a.price as 最高价格,a.hospitalname as 医院名称
from
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) a
join
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) b
on a.goodsid=b.goodsid
from
(select GoodsId,
max(price)[maxprice],
医院名称
from tb group by GoodsId,医院名称)a,
(select GoodsId,
min(price)[minprice],
医院名称
from tb group by GoodsId,医院名称)b
where a.GoodsId=b.GoodsId
select a.GoodsId,
b.price as 最低价格,b.hospitalname as 医院名称,
a.price as 最高价格,a.hospitalname as 医院名称
from
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) a
join
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) b
on a.goodsid=b.goodsid
union all
select min(price) price,GoodsId,HospitalName from 表名称 group by GoodsId,HospitalName
谢谢 GOODlivelife 的回复,我先试下强调一下,需求按 GoodsId 分组统计,不需要给 HospitalName 分组 但统计结果中需要 HospitalName 字段,谢谢!
b.price as 最低价格,b.hospitalname as 医院名称,
a.price as 最高价格,a.hospitalname as 医院名称
from
(Select GoodsID,HospitalName,Price,Row_number() over(partition by GoodsID order by price asc) as num1 from TB_HospitalLoginPrice) a
join
(Select GoodsID,HospitalName,Price,Row_number() over(partition by GoodsID order by price desc) as num1 from TB_HospitalLoginPrice) b
on a.goodsid=b.goodsid and a.num1=1 and b.num1=1
测试一下啊
select a.GoodsId,
b.price as 最低价格,b.hospitalname as 医院名称,
a.price as 最高价格,a.hospitalname as 医院名称
from
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price>t.price)) a
join
(select * from tb t where not exists(select 1 from tb where goodsid=t.goodsid and price<t.price)) b
on a.goodsid=b.goodsid
To:GOODlivelife,测试通过 只是最低价格和最高价格一样时候会有两条同样的记录,貌似 10 楼的方法可以过滤重复的
drop table Hospital
go
create table hospital
(
GoodsId varchar(10),
HospitalID varchar(10),
HospitalName varchar(50),
AreaID varchar(10),
Price decimal
)
go
insert into Hospital(GoodsId,HospitalID,HospitalName,AreaID,Price)
(
select '91602','H0005','aa','150100',1.00 union
select '91602','H0001','bb','150101',14.00 union
select '91602','H0002','cc','150104',7.00 union
select '91603','H0004','dd','150102',11.00 union
select '91603','H0003','ee','150103',18.00 union
select '91604','H0002','ff','150100',2.00 union
select '91603','H0004','gg','150104',15.00 union
select '91603','H0001','hh','150103',8.00 union
select '91603','H0005','ii','150102',12.00 union
select '91602','H0006','jj','150121',15.00 union
select '91604','H0008','kk','150122',3.00 union
select '91604','H0009','ll','150105',10.00 union
select '91604','H0007','mm','150106',9.00
)
go
select hoster.GoodsId,hoster.HospitalName,hoster.Price1,Hospital.HospitalName,hoster.Price2 from(select Hospital.HospitalName,hs.GoodsId,hs.Price1,hs.Price2 from(select hs1.GoodsId,price1,price2 from (select GoodsId,max(Price) Price1 from Hospital group by GoodsId) as hs,
(select GoodsId,min(Price) Price2 from Hospital group by GoodsId) as hs1
where hs.GoodsId=hs1.GoodsId) as hs,Hospital where hs.GoodsId=Hospital.GoodsId
and hs.Price1=Hospital.Price) as hoster,Hospital where hoster.GoodsId=Hospital.GoodsId
and hoster.Price2=Hospital.Price
go
from
(select distinct GoodsId from tb) a
left join
(select GoodsId,
max(price) as maxprice,
医院名称
from tb group by GoodsId,医院名称) b on a.GoodsId=b.GoodsId
left join
(select GoodsId,
min(price) as minprice,
医院名称
from tb group by GoodsId,医院名称) c on a.GoodsId=c.GoodsId这个样子应该可以的!