关于表中的汇总和加减

Question

C表：
ID_NO       cable_no       len_cable生产长度        len_cable_core(使用）
CT001A       CT001              2.0                   1.5
CT001B       CT001              2.0                   0.5
CT002A       CT002              3.5                   1.5
CT002B       CT002              3.5                   1.5
ST001A       ST001              2.5                   2.5
SR001A       SR001              4.5                   4.0
CP001A       CP001              5.0                   3.0
CP001B       CP001              5.0                   2.0
CR001A       CR001              6.0                   2.0
CR001B       CR001              6.0                   1.5
用SQL语言
cable_no            len_cable            len_cable_core(汇总长度）        结余长度
CT001                 2.0                       2.0                          0
CT002                 3.5                       3.0                          0.5
ST001                 2.5                       2.5                          0
SR001                 4.5                       4.0                          0.5
CP001                 5.0                       5.0                          0
CR001                 6.0                       4.0                          2.0 

Answer 1

SELECT CABLE_NO,AVG(LEN_CABLE),SUM(len_cable_core)
FROM C
GROUP BY CABLE_NO

Answer 2

SELECT CABLE_NO,AVG(LEN_CABLE),SUM(len_cable_core),AVG(LEN_CABLE)-SUM(len_cable_core)
FROM C
GROUP BY CABLE_NO
试试这个

Answer 3

select cable_no,len_cable,[len_cable_core(汇总长度)]=sum(len_cable_core),
[结余长度]= len_cable-sum(len_cable_core)
from tb
group cable_no,len_cable

Answer 4

select 
cable_no,
len_cable,
len_cable_core=SUM(len_cable_core),
结余长度=len_cable-SUM(len_cable_core)
from c
group by cable_no,
len_cable

Answer 5

少了一个byselect cable_no,len_cable,[len_cable_core(汇总长度)]=sum(len_cable_core),
[结余长度]= len_cable-sum(len_cable_core)
from tb
group by cable_no,len_cable

Answer 6

-->==============================================
-->Title:生成测试数据
-->Author:wufeng4552【水族杰纶】
-->Environment: MSSQL2005
-->Date:2009-08-25
-->==============================================
 
declare @t table([ID_NO] nvarchar(6),[cable_no] nvarchar(5),
[len_cable] decimal(18,1),[len_cable_core] decimal(18,1))
Insert @t
select N'CT001A',N'CT001',2.0,1.5 union all
select N'CT001B',N'CT001',2.0,0.5 union all
select N'CT002A',N'CT002',3.5,1.5 union all
select N'CT002B',N'CT002',3.5,1.5 union all
select N'ST001A',N'ST001',2.5,2.5 union all
select N'SR001A',N'SR001',4.5,4.0 union all
select N'CP001A',N'CP001',5.0,3.0 union all
select N'CP001B',N'CP001',5.0,2.0 union all
select N'CR001A',N'CR001',6.0,2.0 union all
select N'CR001B',N'CR001',6.0,1.5
select [cable_no],
       sum([len_cable])[len_cable],
       sum([len_cable_core])[len_cable_core],
       sum([len_cable])-sum([len_cable_core])结余长度
from @t group by [cable_no]
/*
cable_no len_cable                               len_cable_core                          结余长度
-------- --------------------------------------- --------------------------------------- ---------------------------------------
CP001    10.0                                    5.0                                     5.0
CR001    12.0                                    3.5                                     8.5
CT001    4.0                                     2.0                                     2.0
CT002    7.0                                     3.0                                     4.0
SR001    4.5                                     4.0                                     0.5
ST001    2.5                                     2.5                                     0.0(6 行受影响)*/

Answer 7

select 
  cable_no,len_cable,
  len_cable_core=
select 
   len_cable_core  
from
  (select 
  lfet(ID_NO,4),sum(len_cable_core) as len_cable_core 
from 
  c 
group by 
  left(ID_NO,4))t,
  结余长度=len_cable-t.len_cable_core
from
  c

Answer 8

create table c ([ID_NO] nvarchar(6),[cable_no] nvarchar(5),
[len_cable] decimal(18,1),[len_cable_core] decimal(18,1))
Insert c
select N'CT001A',N'CT001',2.0,1.5 union all
select N'CT001B',N'CT001',2.0,0.5 union all
select N'CT002A',N'CT002',3.5,1.5 union all
select N'CT002B',N'CT002',3.5,1.5 union all
select N'ST001A',N'ST001',2.5,2.5 union all
select N'SR001A',N'SR001',4.5,4.0 union all
select N'CP001A',N'CP001',5.0,3.0 union all
select N'CP001B',N'CP001',5.0,2.0 union all
select N'CR001A',N'CR001',6.0,2.0 union all
select N'CR001B',N'CR001',6.0,1.5 select 
cable_no,
len_cable,
len_cable_core=SUM(len_cable_core),
结余长度=len_cable-SUM(len_cable_core)
from c
group by cable_no,
len_cable
/*(10 行受影响)
cable_no len_cable                               len_cable_core                          结余长度
-------- --------------------------------------- --------------------------------------- ---------------------------------------
CT001    2.0                                     2.0                                     0.0
ST001    2.5                                     2.5                                     0.0
CT002    3.5                                     3.0                                     0.5
SR001    4.5                                     4.0                                     0.5
CP001    5.0                                     5.0                                     0.0
CR001    6.0                                     3.5                                     2.5(6 行受影响)
*/

Answer 9

汗 我没有看见第2个字段 ..........
改正:
select 
    [cable_no],
    sum([len_cable])[len_cable],
    sum([len_cable_core])[len_cable_core],
    sum([len_cable])-sum([len_cable_core])结余长度
from 
    c 
group by 
    [cable_no]

Answer 10

为什么
CR001A      CR001              6.0                  2.0
CR001B      CR001              6.0                  1.5
而
CR001       6.0             4.0          2.0
不是
CR001       6.0             3.5         1.5
吗

Answer 11

--更正
-->==============================================
-->Title:生成测试数据
-->Author:wufeng4552【水族杰纶】
-->Environment: MSSQL2005
-->Date:2009-08-25
-->==============================================
 
declare @t table([ID_NO] nvarchar(6),[cable_no] nvarchar(5),
[len_cable] decimal(18,1),[len_cable_core] decimal(18,1))
Insert @t
select N'CT001A',N'CT001',2.0,1.5 union all
select N'CT001B',N'CT001',2.0,0.5 union all
select N'CT002A',N'CT002',3.5,1.5 union all
select N'CT002B',N'CT002',3.5,1.5 union all
select N'ST001A',N'ST001',2.5,2.5 union all
select N'SR001A',N'SR001',4.5,4.0 union all
select N'CP001A',N'CP001',5.0,3.0 union all
select N'CP001B',N'CP001',5.0,2.0 union all
select N'CR001A',N'CR001',6.0,2.0 union all
select N'CR001B',N'CR001',6.0,1.5
select [cable_no],
       min([len_cable])[len_cable],
       sum([len_cable_core])[len_cable_core],
       [len_cable]-sum([len_cable_core])结余长度
from @t group by [cable_no],[len_cable]/*
cable_no len_cable                               len_cable_core                          结余长度
-------- --------------------------------------- --------------------------------------- ---------------------------------------
CT001    2.0                                     2.0                                     0.0
ST001    2.5                                     2.5                                     0.0
CT002    3.5                                     3.0                                     0.5
SR001    4.5                                     4.0                                     0.5
CP001    5.0                                     5.0                                     0.0
CR001    6.0                                     3.5                                     2.5(6 行受影响)*/

Answer 12

select 
    [cable_no],
    sum([len_cable])[len_cable],
    sum([len_cable_core])[len_cable_core],
    [len_cable]-sum([len_cable_core])结余长度
from 
    c 
group by 
    [cable_no]
order by
   case cable_no when 'CT001' then 1
                 when 'CT002' then 2
                 when 'ST001' then 3
                 when 'SR001' then 4
                 when 'CP001' then 5
                 when 'CR001' then 6
end

Answer 13

---小麦的数据
create table c ([ID_NO] nvarchar(6),[cable_no] nvarchar(5),
[len_cable] decimal(18,1),[len_cable_core] decimal(18,1))
Insert c
select N'CT001A',N'CT001',2.0,1.5 union all
select N'CT001B',N'CT001',2.0,0.5 union all
select N'CT002A',N'CT002',3.5,1.5 union all
select N'CT002B',N'CT002',3.5,1.5 union all
select N'ST001A',N'ST001',2.5,2.5 union all
select N'SR001A',N'SR001',4.5,4.0 union all
select N'CP001A',N'CP001',5.0,3.0 union all
select N'CP001B',N'CP001',5.0,2.0 union all
select N'CR001A',N'CR001',6.0,2.0 union all
select N'CR001B',N'CR001',6.0,1.5 
select 
    [cable_no],
    sum([len_cable])[len_cable],
    sum([len_cable_core])[len_cable_core],
    [len_cable]-sum([len_cable_core])结余长度
from 
    c 
group by 
    cable_no,len_cable
order by
   case cable_no when 'CT001' then 1
                 when 'CT002' then 2
                 when 'ST001' then 3
                 when 'SR001' then 4
                 when 'CP001' then 5
                 when 'CR001' then 6
enddrop table c
/*cable_no len_cable                                len_cable_core                           结余长度                                     
-------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
CT001    4.0                                      2.0                                      .0
CT002    7.0                                      3.0                                      .5
ST001    2.5                                      2.5                                      .0
SR001    4.5                                      4.0                                      .5
CP001    10.0                                     5.0                                      .0
CR001    12.0                                     3.5                                      2.5（所影响的行数为 6 行）
*/

Answer 14

--借水哥数据
--楼主的结果数据有问题
--CR001                6.0                      4.0                          2.0 declare @t table([ID_NO] nvarchar(6),[cable_no] nvarchar(5),
[len_cable] decimal(18,1),[len_cable_core] decimal(18,1))
Insert @t
select N'CT001A',N'CT001',2.0,1.5 union all
select N'CT001B',N'CT001',2.0,0.5 union all
select N'CT002A',N'CT002',3.5,1.5 union all
select N'CT002B',N'CT002',3.5,1.5 union all
select N'ST001A',N'ST001',2.5,2.5 union all
select N'SR001A',N'SR001',4.5,4.0 union all
select N'CP001A',N'CP001',5.0,3.0 union all
select N'CP001B',N'CP001',5.0,2.0 union all
select N'CR001A',N'CR001',6.0,2.0 union all
select N'CR001B',N'CR001',6.0,1.5select cable_no,len_cable,[len_cable_core(汇总长度)]=sum(len_cable_core),
[结余长度]= len_cable-sum(len_cable_core)
from @t
group by cable_no,len_cable
order by cable_nocable_no len_cable                               len_cable_core(汇总长度)                    结余长度
-------- --------------------------------------- --------------------------------------- ---------------------------------------
CP001    5.0                                     5.0                                     0.0
CR001    6.0                                     3.5                                     2.5
CT001    2.0                                     2.0                                     0.0
CT002    3.5                                     3.0                                     0.5
SR001    4.5                                     4.0                                     0.5
ST001    2.5                                     2.5                                     0.0(6 行受影响)