求一SQL 语句

[zsyok]

商品 价钱
A1 50
A2 60
A3 55
A1 90
A2 70

想用一句SQL 语句统计出来
把所有商口价钱相加起来 一共就 325

把商品汇总
商品 数量 价钱
A1 2 325-50+90 即为 185
A2 2 195
A3 1 270

怎样搞啊大牛们···

[tangxu12]

SELECT SUM(价钱*数量) GROUP BY 商品

[Tosp2012]

Select 商品,Count() as 數量,(Select Sum(价钱) as 總價錢 From 表1)-Sum(价钱) as 价钱 From 表1 Group By 商品

[唐诗三百首]

create table zsy
(商品 varchar(5), 价钱 int)

insert into zsy
select 'A1', 50 union all
select 'A2', 60 union all
select 'A3', 55 union all
select 'A1', 90 union all
select 'A2', 70


select 商品,
       count(1) '数量',
       (select sum(价钱) from zsy)-sum(价钱) '价钱'
 from zsy
 group by 商品

/*
商品   数量         价钱
----- ----------- -----------
A1    2           185
A2    2           195
A3    1           270

(3 row(s) affected)
*/

[唐诗三百首]

create table zsy
(商品 varchar(5), 价钱 int)

insert into zsy
select 'A1', 50 union all
select 'A2', 60 union all
select 'A3', 55 union all
select 'A1', 90 union all
select 'A2', 70


select 商品,
       count(1) '数量',
       (select sum(价钱) from zsy)-sum(价钱) '价钱'
 from zsy
 group by 商品

/*
商品   数量         价钱
----- ----------- -----------
A1    2           185
A2    2           195
A3    1           270

(3 row(s) affected)
*/

[jack15850798154]

IF OBJECT_ID('T_CSDN_ONE') IS NOT NULL
BEGIN
	DROP TABLE T_CSDN_ONE
END

CREATE TABLE T_CSDN_ONE
(
   SP VARCHAR(20),
   JQ DECIMAL(12,2)   	
)
INSERT INTO T_CSDN_ONE
(
	SP,
	JQ
)
SELECT 'A1',50
UNION ALL
SELECT 'A2',60
UNION ALL
SELECT 'A3',55
UNION ALL
SELECT 'A1',90
UNION ALL
SELECT 'A2',70



SELECT 
(SELECT SUM(JQ) FROM T_CSDN_ONE)-SUM(JQ)
FROM T_CSDN_ONE
GROUP BY SP