select sum([COUNT]),name from tablename group by name
select ID ,NAME SUM( COUNT) GROUP BY NAME 按照正常的 这样是没问题的 应该会计算总和 并且 不会有重复数据,但是我的还是现实重复数据
if exists(select *from sysobjects where name ='Stable' and xtype='u') drop table Stable create table Stable ([ID] varchar(20),[NAME] VARCHAR(30),[COUNT] int) insert into Stable select '100' , '张三' ,100 union all select '101' , '张三' ,101 union all select '103' , '李四' ,300 union all select '104' , '王五' ,400 union all select '105' , '赵六' ,700 SELECT [NAME],SUM([COUNT]) TCount FROM Stable GROUP BY [NAME] ORDER BY [NAME] /* NAME TCount ------------------------------ ----------- 李四 300 王五 400 张三 201 赵六 700(所影响的行数为 4 行)*/
Test Dataif exists(select *from sysobjects where name ='Stable' and xtype='u') drop table Stable create table Stable ([ID] varchar(20),[NAME] VARCHAR(30),[COUNT] int) insert into Stable select '100' , '张三' ,100 union all select '101' , '张三' ,101 union all select '103' , '李四' ,300 union all select '104' , '王五' ,400 union all select '105' , '王五' ,200 union all select '106' , '赵六' ,700 SELECT [NAME],SUM([COUNT]) TCount FROM Stable GROUP BY [NAME] ORDER BY [NAME] /* NAME TCount ------------------------------ ----------- 李四 300 王五 600 张三 201 赵六 700(所影响的行数为 4 行) */
select ETL_ID,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_ETACODE,ETL_CLASS,ETL_TYPE,sum(ETL_OILQTYVOLUME), sum(ETL_OILQTY) p0 from RM5EVTTRANSLINES p0 where 1=1 and p0.ETL_EQUIPCODE='HT055' and p0.ETL_TYPE = 'OILING' group by p0.ETL_OILCARRIERCODE, p0.ETL_EQUIPCODE,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_CLASS,P0.ETL_ID,p0.ETL_TYPE 我是这样写的 有什么问题吗?
按某个字段group by之后,那个字段值是不会重复出现的,你说的重复是?if exists(select *from sysobjects where name ='Stable' and xtype='u') drop table Stable create table Stable ([ID] varchar(20),[NAME] VARCHAR(30),[COUNT] INT,xxx VARCHAR(10),yyy VARCHAR(10),zzz VARCHAR(10),www VARCHAR(10)) insert into Stable select '100' , '张三' ,100,'a','b','c','d' union all select '101' , '张三' ,101,'a','b','c','d' union all select '103' , '李四' ,300,'a','b','c','d' union all select '104' , '王五' ,400,'a','b','c','d' union all select '105' , '王五' ,200,'a','b','c','d' union all select '106' , '赵六' ,700,'a','b','c','d' SELECT [NAME],SUM([COUNT]) TCount FROM Stable GROUP BY [NAME] ORDER BY [NAME] /* NAME TCount ------------------------------ ----------- 李四 300 王五 600 张三 201 赵六 700(所影响的行数为 4 行) */
select ETL_ID,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_ETACODE,ETL_CLASS,ETL_TYPE,sum(ETL_OILQTYVOLUME), sum(ETL_OILQTY) p0 from RM5EVTTRANSLINES p0 where 1=1 and p0.ETL_EQUIPCODE='HT055' and p0.ETL_TYPE = 'OILING' group by p0.ETL_OILCARRIERCODE, p0.ETL_EQUIPCODE,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_CLASS,P0.ETL_ID,p0.ETL_TYPE第一个红色部分,p0一样,字段重命名换一个,虽然不影响但是不好, 第二个,p0.ETL_OILCARRIERCODE, p0.ETL_EQUIPCODE,ETL_OILCARRIERCODE ,ETL_EQUIPCODE不都是同一个字段 删掉试试 你不就一个表吗?太乱了。改成这样不行吗?select ETL_ID,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_ETACODE,ETL_CLASS,ETL_TYPE,sum(ETL_OILQTYVOLUME), sum(ETL_OILQTY) ETL_OILQTY from RM5EVTTRANSLINES where 1=1 and ETL_EQUIPCODE='HT055' and ETL_TYPE = 'OILING' group by ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_CLASS,ETL_ID,ETL_TYPE
if exists(select *from sysobjects where name ='Stable' and xtype='u') drop table Stable
create table Stable ([ID] varchar(20),[NAME] VARCHAR(30),[COUNT] int)
insert into Stable
select '100' , '张三' ,100 union all
select '101' , '张三' ,101 union all
select '103' , '李四' ,300 union all
select '104' , '王五' ,400 union all
select '105' , '赵六' ,700 SELECT [NAME],SUM([COUNT]) TCount FROM Stable GROUP BY [NAME] ORDER BY [NAME]
/*
NAME TCount
------------------------------ -----------
李四 300
王五 400
张三 201
赵六 700(所影响的行数为 4 行)*/
drop table Stable
create table Stable ([ID] varchar(20),[NAME] VARCHAR(30),[COUNT] int)
insert into Stable
select '100' , '张三' ,100 union all
select '101' , '张三' ,101 union all
select '103' , '李四' ,300 union all
select '104' , '王五' ,400 union all
select '105' , '王五' ,200 union all
select '106' , '赵六' ,700 SELECT [NAME],SUM([COUNT]) TCount FROM Stable GROUP BY [NAME] ORDER BY [NAME]
/*
NAME TCount
------------------------------ -----------
李四 300
王五 600
张三 201
赵六 700(所影响的行数为 4 行)
*/
sum(ETL_OILQTY) p0 from RM5EVTTRANSLINES p0 where 1=1 and p0.ETL_EQUIPCODE='HT055'
and p0.ETL_TYPE = 'OILING'
group by p0.ETL_OILCARRIERCODE, p0.ETL_EQUIPCODE,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_CLASS,P0.ETL_ID,p0.ETL_TYPE
我是这样写的 有什么问题吗?
create table Stable ([ID] varchar(20),[NAME] VARCHAR(30),[COUNT] INT,xxx VARCHAR(10),yyy VARCHAR(10),zzz VARCHAR(10),www VARCHAR(10))
insert into Stable
select '100' , '张三' ,100,'a','b','c','d' union all
select '101' , '张三' ,101,'a','b','c','d' union all
select '103' , '李四' ,300,'a','b','c','d' union all
select '104' , '王五' ,400,'a','b','c','d' union all
select '105' , '王五' ,200,'a','b','c','d' union all
select '106' , '赵六' ,700,'a','b','c','d' SELECT [NAME],SUM([COUNT]) TCount FROM Stable GROUP BY [NAME] ORDER BY [NAME]
/*
NAME TCount
------------------------------ -----------
李四 300
王五 600
张三 201
赵六 700(所影响的行数为 4 行)
*/
sum(ETL_OILQTY) p0 from RM5EVTTRANSLINES p0 where 1=1 and p0.ETL_EQUIPCODE='HT055'
and p0.ETL_TYPE = 'OILING'
group by p0.ETL_OILCARRIERCODE, p0.ETL_EQUIPCODE,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_CLASS,P0.ETL_ID,p0.ETL_TYPE第一个红色部分,p0一样,字段重命名换一个,虽然不影响但是不好,
第二个,p0.ETL_OILCARRIERCODE, p0.ETL_EQUIPCODE,ETL_OILCARRIERCODE ,ETL_EQUIPCODE不都是同一个字段
删掉试试
你不就一个表吗?太乱了。改成这样不行吗?select ETL_ID,ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_ETACODE,ETL_CLASS,ETL_TYPE,sum(ETL_OILQTYVOLUME),
sum(ETL_OILQTY) ETL_OILQTY from RM5EVTTRANSLINES where 1=1 and ETL_EQUIPCODE='HT055'
and ETL_TYPE = 'OILING'
group by ETL_OILCARRIERCODE ,ETL_EQUIPCODE,ETL_CLASS,ETL_ID,ETL_TYPE
2 279914 FT0449 HT055 9315 OILING 690 0.5796
3 274822 FT0449 HT055 8293 OILING 720 0.6048
4 284559 FT0462 HT055 51 OILING 350 0.294
5 281232 FT0449 HT055 9551 OILING 515 0.4326
6 287888 FT0463 HT055 16192 OILING 499 0.41916本来第2条和第三条应该是合并的,第三条和第五条应该是合并的。 但是他们还能出现重复数据。