if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([车架号] varchar(17),[类型] varchar(3),[数量] float,[金额] float,[成本] float,[行数] int)
insert [tb]
select 'lvhre486185002534','kwy',1.00,108.00,85.00,2 union all
select 'lvhre486185002534','kwy',1.00,108.00,85.00,1 union all
select 'lvhfa162585041494','kwy',1.00,108.00,85.00,1 union all
select 'lvhre486185002534','yz',1.00,164.00,114.65,1 union all
select 'lvhfa164165016851','yz',1.00,123.00,87.75,1 union all
select 'lvhfa164165016851','yz',1.00,35.00,23.61,1 union all
select 'lvhrd576065010815','pj',1.00,157.00,125.59,1 union all
select 'lvhrd786165015004','pj',1.00,157.00,125.59,1 union all
select 'lvhrd777065010815','pj',1.00,98.00,78.40,1 union all
select 'lvhcm771065010815','pj',1.00,98.00,78.40,1 union all
select 'lvhcm777065010815','pj',1.00,98.00,78.40,1 union all
select 'lvhrd770645010815','pj',1.00,98.00,78.40,1 union all
select 'lvhrd570645010814','pj',1.00,98.00,78.40,1select * from [tb]select [年款]=case when substring([车架号],4,2) in ('FA','RE') then substring([车架号],4,2)
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD75','RD55','RD56','RD76') then 'RD5-6'
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD54','RD74') then 'RD4'
else '其他' end
,[类型],[数量]=sum([数量]),[金额]=sum([金额]),[成本]=sum([成本]),[行数]=sum([行数])
from tb
group by case when substring([车架号],4,2) in ('FA','RE') then substring([车架号],4,2)
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD75','RD55','RD56','RD76') then 'RD5-6'
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD54','RD74') then 'RD4'
else '其他' end
,[类型]
order by [年款]
--测试结果:
/*
年款 类型 数量 金额 成本 行数
----- ---- ---------------------- ---------------------- ---------------------- -----------
fa kwy 1 108 85 1
fa yz 2 158 111.36 2
RD4 pj 2 196 156.8 2
RD5-6 pj 3 412 329.58 3
re kwy 1 108 85 1
re yz 1 164 114.65 1
其他 pj 2 196 156.8 2
(7 行受影响)
这个是原来的表,有新的变化,在行数上的统计又有新的问题,
go
create table [tb]([车架号] varchar(17),[类型] varchar(3),[数量] float,[金额] float,[成本] float,[行数] int)
insert [tb]
select 'lvhre486185002534','kwy',1.00,108.00,85.00,2 union all
select 'lvhre486185002534','kwy',1.00,108.00,85.00,1 union all
select 'lvhfa162585041494','kwy',1.00,108.00,85.00,1 union all
select 'lvhre486185002534','yz',1.00,164.00,114.65,1 union all
select 'lvhfa164165016851','yz',1.00,123.00,87.75,1 union all
select 'lvhfa164165016851','yz',1.00,35.00,23.61,1 union all
select 'lvhrd576065010815','pj',1.00,157.00,125.59,1 union all
select 'lvhrd786165015004','pj',1.00,157.00,125.59,1 union all
select 'lvhrd777065010815','pj',1.00,98.00,78.40,1 union all
select 'lvhcm771065010815','pj',1.00,98.00,78.40,1 union all
select 'lvhcm777065010815','pj',1.00,98.00,78.40,1 union all
select 'lvhrd770645010815','pj',1.00,98.00,78.40,1 union all
select 'lvhrd570645010814','pj',1.00,98.00,78.40,1select * from [tb]select [年款]=case when substring([车架号],4,2) in ('FA','RE') then substring([车架号],4,2)
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD75','RD55','RD56','RD76') then 'RD5-6'
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD54','RD74') then 'RD4'
else '其他' end
,[类型],[数量]=sum([数量]),[金额]=sum([金额]),[成本]=sum([成本]),[行数]=sum([行数])
from tb
group by case when substring([车架号],4,2) in ('FA','RE') then substring([车架号],4,2)
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD75','RD55','RD56','RD76') then 'RD5-6'
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD54','RD74') then 'RD4'
else '其他' end
,[类型]
order by [年款]
--测试结果:
/*
年款 类型 数量 金额 成本 行数
----- ---- ---------------------- ---------------------- ---------------------- -----------
fa kwy 1 108 85 1
fa yz 2 158 111.36 2
RD4 pj 2 196 156.8 2
RD5-6 pj 3 412 329.58 3
re kwy 1 108 85 1
re yz 1 164 114.65 1
其他 pj 2 196 156.8 2
(7 行受影响)
这个是原来的表,有新的变化,在行数上的统计又有新的问题,
create table [tb]([配件] varchar(1),[出及退数量] int)
insert [tb]
select 'A',5 union all
select 'A',-2 union all
select 'B',1 union all
select 'B',-1select [行数]=sum(case when [总数]>0 then 1 else 0 end), [数量]=sum([总数])
from
(
select [配件], [总数]=sum([出及退数量])
from [tb] group by [配件]
) T/*
行数 数量
----------- -----------
1 3(1 行受影响)
*/drop table tb
想把这个加到行数中公式中
这个机器中没MSSQL,没法测试...
go
create table [tb]([车架号] varchar(17),[类型] varchar(3),[数量] float,[金额] float,[成本] float,[行数] int)
insert [tb]
select 'lvhre486185002534','kwy',1.00,108.00,85.00, 1 union all
select 'lvhre486185002534','kwy',1.00,108.00,85.00,-1 union all
select 'lvhfa162585041494','kwy',1.00,108.00,85.00, 1 union all
select 'lvhre486185002534','yz',1.00,164.00,114.65,1 union all
select 'lvhfa164165016851','yz',1.00,123.00,87.75,4 union all
select 'lvhfa164165016851','yz',1.00,123.00,87.75,-2go
车架号 类型 数量 价格 成本 行数
lvhre486185002534 kwy 1.0 108.0 85.0 1
lvhre486185002534 kwy 1.0 108.0 85.0 -1
lvhfa162585041494 kwy 1.0 108.0 85.0 1
lvhre486185002534 yz 1.0 164.0 114.65 1
lvhfa164165016851 yz 1.0 123.0 87.75 4
lvhfa164165016851 yz 1.0 123.0 87.75 -2select [年款]=case when substring([车架号],4,2) in ('FA','RE') then substring([车架号],4,2)
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD75','RD55','RD56','RD76') then 'RD5-6'
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD54','RD74') then 'RD4'
else '其他' end
,[类型],[数量]=sum([数量]),[金额]=sum([金额]),[成本]=sum([成本]),[行数]=sum([行数])
from tb
group by case when substring([车架号],4,2) in ('FA','RE') then substring([车架号],4,2)
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD75','RD55','RD56','RD76') then 'RD5-6'
when substring([车架号],4,2)+substring([车架号],6,1)+substring([车架号],10,1) in('RD54','RD74') then 'RD4'
else '其他' end
,[类型]
order by [年款]
结果如下,但不对在行数上
fa kwy 1.0 108.0 85.0 1
fa yz 2.0 246.0 175.5 2
re kwy 2.0 216.0 170.0 1
re yz 1.0 164.0 114.65 1
结果不是这个,
结果如下:fa kwy 1.0 108.0 85.0 1
fa yz 2.0 246.0 175.5 1
re kwy 2.0 216.0 170.0 0
re yz 1.0 164.0 114.65 1在有正负时数量相同就不计算,数量不相同则只计为1