如何写一条SQL语句从下面原表得到统计表?原表
id SN 工单 机种 当前站
1 aa 113300 A F1
2 aa 113300 A F2
3 bb 113300 A F1
4 cc 113300 A F4
5 cc 113300 A F5
6 dd 113300 A F3
7 ee 113300 A F1
8 ff 113300 A F2
统计表
工单 机种 当前站 当前站数量
113300 A F1 2
113300 A F2 2
113300 A F3 1
113300 A F5 1
id SN 工单 机种 当前站
1 aa 113300 A F1
2 aa 113300 A F2
3 bb 113300 A F1
4 cc 113300 A F4
5 cc 113300 A F5
6 dd 113300 A F3
7 ee 113300 A F1
8 ff 113300 A F2
统计表
工单 机种 当前站 当前站数量
113300 A F1 2
113300 A F2 2
113300 A F3 1
113300 A F5 1
group by 工单,机种,当前站
go
insert a
select 1 ,'aa','113300','A','F1' union all
select 2 ,'aa','113300','A','F2' union all
select 3 ,'bb','113300','A','F1' union all
select 4 ,'cc','113300','A','F4' union all
select 5 ,'cc','113300','A','F5' union all
select 6 ,'dd','113300','A','F3' union all
select 7 ,'ee','113300','A','F1' union all
select 8 ,'ff','113300','A','F2'
select [工单],[机种],[当前站],COUNT([当前站])当前站数量 from a group by [工单],[机种],[当前站]/*
工单 机种 当前站 当前站数量
113300 A F1 3
113300 A F2 2
113300 A F3 1
113300 A F4 1
113300 A F5 1
*/
go
Create table tb(id int, SN varchar(10), 工单 varchar(10), 机种 varchar(10), 当前站 varchar(10))
go
insert into tb
select 1, 'aa', '113300', 'A', 'F1' Union all
select 2, 'aa', '113300', 'A', 'F2' Union all
select 3, 'bb', '113300', 'A', 'F1' Union all
select 4, 'cc', '113300', 'A', 'F4' Union all
select 5, 'cc', '113300', 'A', 'F5' Union all
select 6, 'dd', '113300', 'A', 'F3' Union all
select 7, 'ee', '113300', 'A', 'F1' Union all
select 8, 'ff', '113300', 'A', 'F2';select 工单, 机种, 当前站, count(SN) as 当前站数量
from tb
group by 工单, 机种, 当前站
/*工单 机种 当前站 当前站数量
---------- ---------- ---------- -----------
113300 A F1 3
113300 A F2 2
113300 A F3 1
113300 A F4 1
113300 A F5 1
*/
--> 测试数据:[原表]
if object_id('[原表]') is not null drop table [原表]
create table [原表]([id] int,[SN] varchar(2),[工单] int,[机种] varchar(1),[当前站] varchar(2))
insert [原表]
select 1,'aa',113300,'A','F1' union all
select 2,'aa',113300,'A','F2' union all
select 3,'bb',113300,'A','F1' union all
select 4,'cc',113300,'A','F4' union all
select 5,'cc',113300,'A','F5' union all
select 6,'dd',113300,'A','F3' union all
select 7,'ee',113300,'A','F1' union all
select 8,'ff',113300,'A','F2'
select
[工单],[机种],[当前站],
COUNT([当前站]) as 当前站数量
from
[原表]
group by
[工单],[机种],[当前站]
/*
工单 机种 当前站 当前站数量
113300 A F1 3
113300 A F2 2
113300 A F3 1
113300 A F4 1
113300 A F5 1
*/貌似楼主结果有误??
F1 bb,ee 2
F2 aa,ff 2
F3 dd 1
F5 cc 1
select A.工单,A.机种,A.当前站, COUNT(*) as 当前站数量 from A
inner join
(
select 工单,机种,SN,MAX(ID) ID from A
group by 工单,机种,SN
) B on A.工单=B.工单 and A.机种=B.机种 and A.SN=B.SN and A.ID=B.ID
group by A.工单,A.机种,A.当前站
drop table t1
Go
Create table t1([id] smallint,[SN] nvarchar(2),[工单] int,[机种] nvarchar(1),[当前站] nvarchar(2))
Insert into t1
Select 1,N'aa',113300,N'A',N'F1'
Union all Select 2,N'aa',113300,N'A',N'F2'
Union all Select 3,N'bb',113300,N'A',N'F1'
Union all Select 4,N'cc',113300,N'A',N'F4'
Union all Select 5,N'cc',113300,N'A',N'F5'
Union all Select 6,N'dd',113300,N'A',N'F3'
Union all Select 7,N'ee',113300,N'A',N'F1'
Union all Select 8,N'ff',113300,N'A',N'F2'
--统计表
--工单 机种 当前站 当前站数量
--113300 A F1 2
--113300 A F2 2
--113300 A F3 1
--113300 A F5 1SELECT 工单,机种,当前站,COUNT(SN) AS 当前站数量 FROM t1 AS a
WHERE NOT EXISTS(SELECT 1 FROM t1 AS x WHERE x.SN=a.SN AND x.id>a.id)
GROUP BY 工单,机种,当前站
insert [原表]
select 1,'aa',113300,'A','F1' union all
select 2,'aa',113300,'A','F2' union all
select 3,'bb',113300,'A','F1' union all
select 4,'cc',113300,'A','F4' union all
select 5,'cc',113300,'A','F5' union all
select 6,'dd',113300,'A','F3' union all
select 7,'ee',113300,'A','F1' union all
select 8,'ff',113300,'A','F2'select 工单,机种,当前站,count(当前站) as 当前站数量 from(
select *,rank()over(partition by sn order by id desc ) as row from 原表 )a
where a.row=1 group by 工单,机种,当前站------结果
工单,机种,当前站, 当前站数量
113300 A F1 2
113300 A F2 2
113300 A F3 1
113300 A F5 1