select BH,max(DanJia),min(DanJia) from TB group by BH
select BH,max(DanJia),min(DanJia),'变动率'=(max(DanJia)-min(DanJia)) from TB group by BH
select distinct a.编号,a.名称,b.最高价,b.最低价,b.最高价-b.最低价 as '变动量' from t a join ( select BH,max(DanJia)as '最高价' ,min(DanJia)as '最低价' from TB group by BH )b on a.BH=b.BH
select BH,max,min,(max-min) from ( select BH,max(DanJia) as max ,min(DanJia) as min from TB group by BH ) a
declare @tb table(bh int,name char(8),price money) insert @tb select 001,'baicai',2.5 union select 001,'baicai',2.6 union select 001,'baicai',3.0 select bh,name,max(price),min(price) from @tb group by bh,name
select BH,名称,max(DanJia),min(DanJia),'变动率'=case max(DanJia) when 0 then 0 else (max(DanJia)-min(DanJia))/max(DanJia) end from TB group by BH,名称
declare @tb table(bh int,name char(8),price money) insert @tb select 001,'baicai',2.5 union select 001,'baicai',2.6 union select 001,'baicai',3.0 union select 002,'caibai',0if not exists(select 1 from @tb having max(price)=0) begin select bh,name,max(price),min(price),rate=0 from @tb group by bh,name end else begin select bh,name,max(price),min(price),rate=(max(price)-min(price))/max(price) from @tb group by bh,name end
已经解决,用的是isnull(nullif(max,0),1),也算是一种方法了,呵呵
CREATE TABLE PRICE (DATETIEM DATETIME,BIANHAO NVARCHAR(10),WP_NAME NVARCHAR(50),WP_PRICE MONEY,WP_COUNT DECIMAL(8,3) ) GO INSERT INTO PRICE SELECT '2007-07-01','001','白菜',2.5,24.500 UNION ALL SELECT '2007-07-02','001','白菜',2.3,55.3210 UNION ALL SELECT '2007-07-03','003','萝卜',2.5,31.11111 UNION ALL SELECT '2007-07-04','004','青菜',2.1,44.2 UNION ALL SELECT '2007-07-05','004','青菜',2.9,66.4 GO SELECT BIANHAO AS 编号 , WP_NAME AS 名称 , MAX(WP_PRICE) AS 最大值 , MIN(WP_PRICE)AS 最小值 , CONVERT(NVARCHAR(10),MAX(WP_PRICE)-MIN(WP_PRICE)) AS 差价 FROM PRICE GROUP BY BIANHAO,WP_NAME
join
(
select BH,max(DanJia)as '最高价' ,min(DanJia)as '最低价' from TB group by BH
)b
on a.BH=b.BH
select BH,max,min,(max-min) from (
select BH,max(DanJia) as max ,min(DanJia) as min from TB group by BH
) a
insert @tb
select 001,'baicai',2.5
union select 001,'baicai',2.6
union select 001,'baicai',3.0
select bh,name,max(price),min(price) from @tb group by bh,name
insert @tb
select 001,'baicai',2.5
union select 001,'baicai',2.6
union select 001,'baicai',3.0
union select 002,'caibai',0if not exists(select 1 from @tb having max(price)=0)
begin
select bh,name,max(price),min(price),rate=0 from @tb group by bh,name
end
else
begin
select bh,name,max(price),min(price),rate=(max(price)-min(price))/max(price) from @tb group by bh,name
end
(DATETIEM DATETIME,BIANHAO NVARCHAR(10),WP_NAME NVARCHAR(50),WP_PRICE MONEY,WP_COUNT DECIMAL(8,3) )
GO
INSERT INTO PRICE
SELECT '2007-07-01','001','白菜',2.5,24.500 UNION ALL
SELECT '2007-07-02','001','白菜',2.3,55.3210 UNION ALL
SELECT '2007-07-03','003','萝卜',2.5,31.11111 UNION ALL
SELECT '2007-07-04','004','青菜',2.1,44.2 UNION ALL
SELECT '2007-07-05','004','青菜',2.9,66.4
GO
SELECT BIANHAO AS 编号 , WP_NAME AS 名称 , MAX(WP_PRICE) AS 最大值 , MIN(WP_PRICE)AS 最小值 , CONVERT(NVARCHAR(10),MAX(WP_PRICE)-MIN(WP_PRICE)) AS 差价
FROM PRICE
GROUP BY BIANHAO,WP_NAME