SQL 这个怎么查

Question

编码    销量    成本    零售价 
01      5      2.2      3 
01      3      2.5      3 
02      5      6        6.5 
02      2      5.5      6.5 
SQL语句得到汇总表 编码      数量    成本金额    平均成本      零售金额    利润 
01        8        18.5      2.3125            24        5.5 
02        7        41        5.8571            45.5      4.5 
 

Answer 1

select 编码,sum(销量) as 数量,
sum(销量*成本) as 成本金额,
sum(销量*成本)/sum(销量) as 平均成本,
sum(销量*零售价) as 零售金额,
sum(销量*零售价)-sum(销量*成本) as 利润
from table1
group by 编码

Answer 2

select 编码,
数量=Sum(销量),
成本金额=Sum(销量*成本),
平均成本=Sum(销量*成本)/Sum(销量),
零售金额=Sum(销量*零售价),
利润 =Sum(销量*零售价)-Sum(销量*成本)/Sum(销量)
from tb
group by 编码

Answer 3

select 编码,
数量=Sum(销量),
成本金额=Sum(销量*成本),
平均成本=Sum(销量*成本)/Sum(销量),
零售金额=Sum(销量*零售价),
利润 =Sum(销量*零售价)-Sum(销量*成本)
from tb
group by 编码

Answer 4

create table tb(编码 varchar(10),   销量  int,  成本 decimal(18,2),   零售价 decimal(18,1))
insert into tb values('01' ,     5  ,    2.2  ,    3 )
insert into tb values('01' ,     3  ,    2.5  ,    3 )
insert into tb values('02' ,     5  ,    6    ,    6.5) 
insert into tb values('02' ,     2  ,    5.5  ,    6.5)
goselect 编码,
       数量 = sum(销量),
       成本金额 = sum(销量 * 成本),
       平均成本 = sum(销量 * 成本)  / sum(销量),
       零售金额 = sum(销量 * 零售价),
       利润     = sum(销量 * (零售价 - 成本))
from tb
group by 编码
/*
编码         数量          成本金额                                     平均成本                                     零售金额                                     利润                                       
---------- ----------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
01         8           18.50                                    2.312500                                 24.0                                     5.50
02         7           41.00                                    5.857142                                 45.5                                     4.50（所影响的行数为 2 行）
*/select 编码,
       数量 = sum(销量),
       成本金额 = cast(sum(销量 * 成本) as decimal(18,1)),
       平均成本 = cast(sum(销量 * 成本)  / sum(销量) as decimal(18,4)),
       零售金额 = sum(销量 * 零售价),
       利润     = cast(sum(销量 * (零售价 - 成本)) as decimal(18,1))
from tb
group by 编码
/*
编码         数量          成本金额                 平均成本                 零售金额                                     利润                   
---------- ----------- -------------------- -------------------- ---------------------------------------- -------------------- 
01         8           18.5                 2.3125               24.0                                     5.5
02         7           41.0                 5.8571               45.5                                     4.5（所影响的行数为 2 行）
*/drop table tb

Answer 5

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
CREATE TABLE tb (编码 VARCHAR(40),销量 INT,成本 FLOAT,零售价 FLOAT)INSERT tb
SELECT '01',5,2.2,3 UNION ALL
SELECT '01',3,2.5,3 UNION ALL
SELECT '02',5,6,6.5 UNION ALL
SELECT '02',2,5.5,6.5 SELECT a.编码,[数量]=SUM(a.销量),[成本金额]=SUM(a.销量*a.成本),
[平均成本]=CAST(SUM(a.销量*a.成本)/SUM(a.销量) AS DECIMAL(20,4)),
[零售金额]=SUM(a.销量*a.零售价),
[利润]=SUM(a.销量*a.零售价)-SUM(a.销量*a.成本)
FROM tb a
GROUP BY a.编码
/*
 01 8 18.5 2.3125 24.0 5.5
 02 7 41.0 5.8571 45.5 4.5
*/