表qcc_flawInfo(id,lotno,gxno,chipno,FlawBNo)
1 ms1000 2 3 a
2 ms1000 2 3 b
3 ms1000 3 3 c
4 ms1001 2 1 a想要 的统计结果这样描述的:
同一批次(lotno),同一工序(gxno),同一片号(chipno)的记录做为一条记录
想要得到的结果如下:lotno gxno chipno resultNum
ms1000 2 3 1
ms1000 3 3 1
ms1001 2 1 1
各位达人帮忙,感谢
1 ms1000 2 3 a
2 ms1000 2 3 b
3 ms1000 3 3 c
4 ms1001 2 1 a想要 的统计结果这样描述的:
同一批次(lotno),同一工序(gxno),同一片号(chipno)的记录做为一条记录
想要得到的结果如下:lotno gxno chipno resultNum
ms1000 2 3 1
ms1000 3 3 1
ms1001 2 1 1
各位达人帮忙,感谢
(
id int identity(1,1) primary key,
lotno nvarchar(50),
gxno int,
chipno int,
FlawBNo nvarchar(30)
)
insert into #qcc_flawInfo select 'ms1000',2,3,'a'
insert into #qcc_flawInfo select 'ms1000',2,3,'b'
insert into #qcc_flawInfo select 'ms1000',3,3,'c'
insert into #qcc_flawInfo select 'ms1001',2,1,'a'select lotno,gxno,chipno,count(*) resultNum from #qcc_flawInfo
group by lotno,gxno,chipno
(
id int identity(1,1) primary key,
lotno nvarchar(50),
gxno int,
chipno int,
FlawBNo nvarchar(30)
)
insert into #qcc_flawInfo select 'ms1000',2,3,'a'
insert into #qcc_flawInfo select 'ms1000',2,3,'b'
insert into #qcc_flawInfo select 'ms1000',3,3,'c'
insert into #qcc_flawInfo select 'ms1001',2,1,'a'select lotno,gxno,chipno,count(*) resultNum from #qcc_flawInfo
group by lotno,gxno,chipno
lotno gxno chipno resultNum
-------------------------------------------------- ----------- ----------- -----------
ms1000 2 3 2
ms1000 3 3 1
ms1001 2 1 1
lotno,gxno,chipno,count(1) as resultNum
from
tb t
where
exists(select 1 from tb where lotno=t.lotno and gxno=t.gxno and chipno=t.chipno)
group by
lotno,gxno,chipno
from
(
select distinct lotno,gxno,chipno from #qcc_flawInfo
) tt
group by lotno,gxno,chipnolotno gxno chipno resultNum
-------------------------------------------------- ----------- ----------- -----------
ms1000 2 3 1
ms1000 3 3 1
ms1001 2 1 1
(
id int identity(1,1) primary key,
lotno nvarchar(50),
gxno int,
chipno int,
FlawBNo nvarchar(30)
)
insert into #qcc_flawInfo select 'ms1000',2,3,'a'
insert into #qcc_flawInfo select 'ms1000',2,3,'b'
insert into #qcc_flawInfo select 'ms1000',3,3,'c'
insert into #qcc_flawInfo select 'ms1001',2,1,'a'select lotno,gxno,chipno,count(*) resultNum
from
(
select distinct lotno,gxno,chipno from #qcc_flawInfo
) tt
group by lotno,gxno,chipno
lotno gxno chipno resultNum
-------------------------------------------------- ----------- ----------- -----------
ms1000 2 3 1
ms1000 3 3 1
ms1001 2 1 1
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-02-03 11:57:30
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[lotno] varchar(6),[gxno] int,[chipno] int,[FlawBNo] varchar(1))
insert [tb]
select 1,'ms1000',2,3,'a' union all
select 2,'ms1000',2,3,'b' union all
select 3,'ms1000',3,3,'c' union all
select 4,'ms1001',2,1,'a'
--------------开始查询--------------------------
select
lotno,gxno,chipno,count(1) as resultNum
from
tb t
where
not exists(select 1 from tb where lotno=t.lotno and gxno=t.gxno and chipno=t.chipno and FlawBNo>t.FlawBNo)
group by
lotno,gxno,chipno
----------------结果----------------------------
/* lotno gxno chipno resultNum
------ ----------- ----------- -----------
ms1000 2 3 1
ms1000 3 3 1
ms1001 2 1 1(3 行受影响)
*/