---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-09-12 15:17:19 -- Version: -- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) -- Jun 10 2013 20:09:10 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([字段A] int,[字段B] int) insert [huang] select 1,1 union all select 2,2 union all select 2,5 union all select 1,2 union all select 4,4 --------------开始查询--------------------------select 字段A,sum(字段B)字段B from [huang] group by 字段A ----------------结果---------------------------- /* 字段A 字段B ----------- ----------- 1 3 2 7 4 4 */
;with tb(字段A,字段B) as ( select 1,1 union all select 2,2 union all select 2,5 union all select 1,2 union all select 4,4 )select 字段A,SUM(字段B) as 字段B from tb group by 字段A/* 字段A 字段B 1 3 2 7 4 4 */
create table wj (字段A int, 字段B int)insert into wj select 1, 1 union all select 2, 2 union all select 2, 5 union all select 1, 2 union all select 4, 4 select distinct a.字段A, (select sum(b.字段B) from wj b where b.字段A=a.字段A) '字段B' from wj a/* 字段A 字段B ----------- ----------- 1 3 2 7 4 4(3 row(s) affected) */
select periodseq,sum(gdqty )gdqty ,sum(ngqty)ngqty from tb group by periodseq
try this,select periodseq, sum(gdqty) 'gdqty', sum(ngqty) 'ngqty' from [表名] group by periodseq
try this,select periodseq, sum(gdqty) 'gdqty', sum(ngqty) 'ngqty' from [表名] group by periodseq 大神,按照您的办法,表中只保留了periodseq 还有另外两个列我想要求全部的列,跟图片的中的一样的,只是做了累加了 我想筛查出来的表包含所有的字段。但是经过累加了
select [segment] ,sum(gdqty) 总良品数,SUM (ngqty ) 不良品数,(SUM(gdqty)/(SUM (gdqty )+SUM (ngqty )))*100 良率 FROM [pics].[dbo].[picsqsy] where periodseq=1 and wrkstation ='M2' and process ='FCT' and model='PHILIPS' and sftdate =20130912 group by segment
这样可以这样就不行了 select [segment] ,[process] ,[line] ,[wrkstation] ,[model] ,[item] ,[sord] ,[sftdate],sum(gdqty) 总良品数,SUM (ngqty ) 不良品数,(SUM(gdqty)/(SUM (gdqty )+SUM (ngqty )))*100 良率 FROM [pics].[dbo].[picsqsy] where periodseq=1 and wrkstation ='M2' and process ='FCT' and model='PHILIPS' and sftdate =20130912 group by segment
错误:消息 8120,级别 16,状态 1,第 2 行 Column 'pics.dbo.picsqsy.process' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
try this,select segment,process,line,wrkstation,model,item,sord,sftdate,sftseq,shift,periodseq,period, sum(gdqty) 'gdqty', sum(ngqty) 'ngqty' from [表名] group by segment,process,line,wrkstation,model,item,sord,sftdate,sftseq,shift,periodseq,period
select segment,process,line wrkstation,model,item,sord,sitdate,sitseq,shift,periodseq,period,sum(gdqty),sum(ngqty) from tb group by segment,process,line wrkstation,model,item,sord,sitdate,sitseq,shift,periodseq,period列名不知道有没有拼错,你自己调一下
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-12 15:17:19
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([字段A] int,[字段B] int)
insert [huang]
select 1,1 union all
select 2,2 union all
select 2,5 union all
select 1,2 union all
select 4,4
--------------开始查询--------------------------select 字段A,sum(字段B)字段B
from [huang] group by 字段A
----------------结果----------------------------
/*
字段A 字段B
----------- -----------
1 3
2 7
4 4
*/
;with tb(字段A,字段B) as
(
select 1,1
union all select 2,2
union all select 2,5
union all select 1,2
union all select 4,4
)select 字段A,SUM(字段B) as 字段B
from tb
group by 字段A/*
字段A 字段B
1 3
2 7
4 4
*/
select [字段A],sum([字段B])
from [表名]
group by [字段A]
可以吗??
版主,帮人帮到底。不懂SQL,我想按照periodseq 进而将不同时段的选出来要求gdqty 和ngqty如果periodseq相同,是累加的。
可以吗??
create table wj
(字段A int, 字段B int)insert into wj
select 1, 1 union all
select 2, 2 union all
select 2, 5 union all
select 1, 2 union all
select 4, 4
select distinct a.字段A,
(select sum(b.字段B) from wj b
where b.字段A=a.字段A) '字段B'
from wj a/*
字段A 字段B
----------- -----------
1 3
2 7
4 4(3 row(s) affected)
*/
from tb
group by periodseq
sum(gdqty) 'gdqty',
sum(ngqty) 'ngqty'
from [表名]
group by periodseq
大神,按照您的办法,表中只保留了periodseq 还有另外两个列我想要求全部的列,跟图片的中的一样的,只是做了累加了
我想筛查出来的表包含所有的字段。但是经过累加了
sum(gdqty) 'gdqty',
sum(ngqty) 'ngqty'
from [表名]
group by periodseq
大神,按照您的办法,表中只保留了periodseq 还有另外两个列我想要求全部的列,跟图片的中的一样的,只是做了累加了
我想筛查出来的表包含所有的字段。但是经过累加了
,sum(gdqty) 总良品数,SUM (ngqty ) 不良品数,(SUM(gdqty)/(SUM (gdqty )+SUM (ngqty )))*100 良率 FROM [pics].[dbo].[picsqsy] where periodseq=1 and wrkstation ='M2' and process ='FCT' and model='PHILIPS' and sftdate =20130912 group by segment
这样可以这样就不行了
select [segment]
,[process]
,[line]
,[wrkstation]
,[model]
,[item]
,[sord]
,[sftdate],sum(gdqty) 总良品数,SUM (ngqty ) 不良品数,(SUM(gdqty)/(SUM (gdqty )+SUM (ngqty )))*100 良率 FROM [pics].[dbo].[picsqsy] where periodseq=1 and wrkstation ='M2' and process ='FCT' and model='PHILIPS' and sftdate =20130912 group by segment
错误:消息 8120,级别 16,状态 1,第 2 行
Column 'pics.dbo.picsqsy.process' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
sum(gdqty) 'gdqty',
sum(ngqty) 'ngqty'
from [表名]
group by segment,process,line,wrkstation,model,item,sord,sftdate,sftseq,shift,periodseq,period
from tb
group by segment,process,line wrkstation,model,item,sord,sitdate,sitseq,shift,periodseq,period列名不知道有没有拼错,你自己调一下