提取一个月里面每个进货物品的进货价格,变动量,变动率,select BH,max(DanJia),min(DanJia) from TB取出来一个物品有几条记录,如何才能在一条记录里面显示上面的值呢?
------解决方案--------------------
没看明白
------解决方案--------------------
select BH,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
------解决方案--------------------
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,名称
------解决方案--------------------
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