关于加 乘汇总

Question

C表：
con_no        coustomer      type          amount
s001            A            EE3            5
s001            A            EE3            5
S001            A            EE5            4
s001            A            EC5            3
S001            A            EC3            3
S002            B            EE3            5
S002            B            EE3            3
S002            B            EC4            4
根据以表C形成一下样式表con_no        coustomer      type        amount_1          total
s001            A            EE3            10              30
s001            A            EE5             4              20
S001            A            EC5             3              15
s002            B            EE3             8              24
S002            B            EC4             4              16
          total
           30
           20
           15
           24
           16
total 的数据是用  “type 中的数字”   乘以 “amount 中type中型号相同的 amount 相加”

Answer 1

try
select 
  con_no,
  coustomer,
  type,
  amount_1=sum(amount),
  total=sum(amount)*cast(right(type,len(type)-2) as int)
from
  c
group by
  con_no,
  coustomer,
  type  

Answer 2

--> 生成测试数据表:CIf not object_id('[C]') is null
Drop table [C]
Go
Create table [C]([con_no] nvarchar(4),[coustomer] nvarchar(1),[type] nvarchar(3),[amount] int)
Insert C
Select 's001','A','EE3',5 union all
Select 's001','A','EE3',5 union all
Select 'S001','A','EE5',4 union all
Select 's001','A','EC5',3 union all
Select 'S001','A','EC3',3 union all
Select 'S002','B','EE3',5 union all
Select 'S002','B','EE3',3 union all
Select 'S002','B','EC4',4
Go
--Select * from C-->SQL查询如下:
select [con_no],[coustomer],[type],amount_1 =sum(amount) ,
sum(right([type],patindex('%[^0-9]%',reverse([type]))-1)*[amount]) as total 
from c
group by [con_no],[coustomer],[type]
order by [con_no],coustomer,total desc,[type]
/*
con_no coustomer type amount_1    total
------ --------- ---- ----------- -----------
s001   A         EE3  10          30
S001   A         EE5  4           20
s001   A         EC5  3           15
S001   A         EC3  3           9
S002   B         EE3  8           24
S002   B         EC4  4           16(6 行受影响)
*/

Answer 3

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-26 16:32:26
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([con_no] varchar(4),[coustomer] varchar(1),[type] varchar(3),[amount] int)
insert [tb]
select 's001','A','EE3',5 union all
select 's001','A','EE3',5 union all
select 'S001','A','EE5',4 union all
select 's001','A','EC5',3 union all
select 'S001','A','EC3',3 union all
select 'S002','B','EE3',5 union all
select 'S002','B','EE3',3 union all
select 'S002','B','EC4',4
--------------开始查询--------------------------select 
  con_no,
  coustomer,
  [type],
  amount_1=sum(amount),
  total=sum(amount)*cast(right(type,len(type)-2) as int)
from
  tb
group by
  con_no,coustomer,type
order by 
  [con_no],coustomer,total desc,[type]
----------------结果----------------------------
/*con_no coustomer type amount_1    total
------ --------- ---- ----------- -----------
s001   A         EE3  10          30
S001   A         EE5  4           20
s001   A         EC5  3           15
S001   A         EC3  3           9
S002   B         EE3  8           24
S002   B         EC4  4           16(6 行受影响)*/