27,579
社区成员
发帖
与我相关
我的任务
分享SQL2000 按班级分类，统计人数，未考勤人数
SQL2000 统计分析
有如下表
表1
班级 姓名编号 姓名
一班 1001 李三
一班 1002 张明
一班 1003 阿四
二班 1004 小明
二班 1005 阿小
三班 1006 小何
考勤表
姓名编号 班级 时间
1001 一班 08:30
1002 一班 08:30
1004 二班 08:30
1005 二班 08:30
1006 三班 08:30
统计格式如下
班级 人数 未考勤人数
一班 3 1
二班 2 0
三班 1 0
按班级分类，统计人fi果级人员在考勤表没有记录时。显示未考勤人数，有记录就不显示。
有如下表
表1
班级 姓名编号 姓名
一班 1001 李三
一班 1002 张明
一班 1003 阿四
二班 1004 小明
二班 1005 阿小
三班 1006 小何
考勤表
姓名编号 班级 时间
1001 一班 08:30
1002 一班 08:30
1004 二班 08:30
1005 二班 08:30
1006 三班 08:30
统计格式如下
班级 人数 未考勤人数
一班 3 1
二班 2 0
三班 1 0
按班级分类，统计人fi果级人员在考勤表没有记录时。显示未考勤人数，有记录就不显示。
...全文
请发表友善的回复…
发表回复
Dear SQL(燊) 2019-03-13
- 打赏
- 举报
count(distinct a.姓名编号)
select a.班级,人数=count(distinct a.姓名编号),未考勤人数=sum(case when b.时间 is null then 1 else 0 end )
from #t1 a
left join #t2 b on a.姓名编号=b.姓名编号
group by a.班级Dear SQL(燊) 2019-03-13
- 打赏
- 举报
如果考勤表插入多条数据统计就不对了.
Create table #考勤表([姓名编号] int,[班级] nvarchar(22),[时间] nvarchar(22))
Insert #考勤表
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30'[/quote]
if object_id('tempdb..#t1') is not null drop table #t1;
if object_id('tempdb..#t2') is not null drop table #t2;
select 班级='一班',姓名编号=N'1001',姓名='李三' into #t1 union all
select 班级='一班',姓名编号=N'1002',姓名='张明' union all
select 班级='一班',姓名编号=N'1003',姓名='阿四' union all
select 班级='二班',姓名编号=N'1004',姓名='小明' union all
select 班级='二班',姓名编号=N'1005',姓名='阿小' union all
select 班级='三班',姓名编号=N'1006',姓名='小何'
select 姓名编号=N'1001',班级='一班',时间='08:30' into #t2 union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30'
select a.班级,人数=count(distinct b.姓名编号),未考勤人数=sum(case when b.时间 is null then 1 else 0 end )
from #t1 a
left join #t2 b on a.姓名编号=b.姓名编号
group by a.班级
/*
班级 人数 未考勤人数
---- ----------- -----------
二班 2 0
三班 1 0
一班 2 1
*/
好奇都是要学的 2019-03-13
- 打赏
- 举报
select #t1.班级,COUNT(#t2.姓名编号) 人数,COUNT(case when #t2.姓名编号 is null then 1 else null end) 未考勤人数 from #t1
left join #t2 on #t1.姓名编号=#t2.姓名编号
group by #t1.班级
和
select #t1.班级,COUNT(isnull(#t2.姓名编号,'')) 人数,COUNT(isnull(#t2.姓名编号,''))-COUNT(#t2.姓名编号) 未考勤人数 from #t1
left join #t2 on #t1.姓名编号=#t2.姓名编号
group by #t1.班级
二个都行。
woniugongzuoshi 2019-03-12
- 打赏
- 举报
select 班级,count(distinct 姓名编号) as 人数,count(distinct 姓名编号)-isnull((select count(distinct 姓名编号) from 考勤表 where 班级=表1.班级),0) as 未考勤人数 from 表1
group by 班级
zjbeva025 2019-03-12
- 打赏
- 举报
如果考勤表插入多条数据统计就不对了.
Create table #考勤表([姓名编号] int,[班级] nvarchar(22),[时间] nvarchar(22))
Insert #考勤表
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30'
二月十六 2019-03-12
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#表1') is null
drop table #表1
Go
Create table #表1([班级] nvarchar(22),[姓名编号] int,[姓名] nvarchar(22))
Insert #表1
select N'一班',1001,N'李三' union all
select N'一班',1002,N'张明' union all
select N'一班',1003,N'阿四' union all
select N'二班',1004,N'小明' union all
select N'二班',1005,N'阿小' union all
select N'三班',1006,N'小何'
GO
if not object_id(N'Tempdb..#考勤表') is null
drop table #考勤表
Go
Create table #考勤表([姓名编号] int,[班级] nvarchar(22),[时间] Date)
Insert #考勤表
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30'
Go
--测试数据结束
SELECT #表1.班级,
COUNT(1) AS 人数,
SUM( CASE
WHEN #考勤表.姓名编号 IS NULL THEN
1
ELSE
0
END
) 未考勤人数
FROM #表1
LEFT JOIN #考勤表
ON #考勤表.姓名编号 = #表1.姓名编号
GROUP BY #表1.班级;
Dear SQL(燊) 2019-03-12
- 打赏
- 举报
if object_id('tempdb..#t1') is not null drop table #t1;
if object_id('tempdb..#t2') is not null drop table #t2;
select 班级='一班',姓名编号=N'1001',姓名='李三' into #t1 union all
select 班级='一班',姓名编号=N'1002',姓名='张明' union all
select 班级='一班',姓名编号=N'1003',姓名='阿四' union all
select 班级='二班',姓名编号=N'1004',姓名='小明' union all
select 班级='二班',姓名编号=N'1005',姓名='阿小' union all
select 班级='三班',姓名编号=N'1006',姓名='小何'
select 姓名编号=N'1001',班级='一班',时间='08:30' into #t2 union all
select 姓名编号=N'1002',班级='一班',时间='08:30' union all
select 姓名编号=N'1004',班级='二班',时间='08:30' union all
select 姓名编号=N'1005',班级='二班',时间='08:30' union all
select 姓名编号=N'1006',班级='三班',时间='08:30'
select a.班级,人数=count(*),未考勤人数=sum(case when b.时间 is null then 1 else 0 end )
from #t1 a
left join #t2 b on a.姓名编号=b.姓名编号
group by a.班级
Dear SQL(燊) 2019-03-12
- 打赏
- 举报
;
with t1 as(
select 班级='一班',姓名编号=N'1001',姓名='李三' union all
select 班级='一班',姓名编号=N'1002',姓名='张明' union all
select 班级='一班',姓名编号=N'1003',姓名='阿四' union all
select 班级='二班',姓名编号=N'1004',姓名='小明' union all
select 班级='二班',姓名编号=N'1005',姓名='阿小' union all
select 班级='三班',姓名编号=N'1006',姓名='小何'
),t2 as(
select 姓名编号=N'1001',班级='一班',时间='08:30' union all
select 姓名编号=N'1002',班级='一班',时间='08:30' union all
select 姓名编号=N'1004',班级='二班',时间='08:30' union all
select 姓名编号=N'1005',班级='二班',时间='08:30' union all
select 姓名编号=N'1006',班级='三班',时间='08:30'
)
select a.班级,人数=count(*),未考勤人数=sum(case when b.时间 is null then 1 else 0 end )
from t1 a
left join t2 b on a.姓名编号=b.姓名编号
group by a.班级
/*
班级 人数 未考勤人数
---- ----------- -----------
二班 2 0
三班 1 0
一班 3 1
(3 行受影响)
*/
zjbeva025 2019-03-12
- 打赏
- 举报
如果考勤表插入多条数据统计就不对了.
Create table #考勤表([姓名编号] int,[班级] nvarchar(22),[时间] nvarchar(22))
Insert #考勤表
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30' union all
select 1001,N'一班','08:30' union all
select 1002,N'一班','08:30' union all
select 1004,N'二班','08:30' union all
select 1005,N'二班','08:30' union all
select 1006,N'三班','08:30'
