求一SQL 语句
商品 价钱
A1 50
A260
A355
A190
A270
想用一句SQL 语句统计出来
把所有商口价钱相加起来 一共就 325
把商品汇总
商品数量 价钱
A12 325-50+90 即为 185
A2 2 195
A31 270
怎样搞啊大牛们···
[解决办法]
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)
*/
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
