表1
人员编号 时间
1 2013-01-01
1 2013-01-02
2 2013-01-01
2 2013-01-02
2 2013-01-03
2 2013-01-04
3 2013-01-01
3 2013-01-02
3 2013-01-03
4 2013-01-01
4 2013-01-02
表2
人员编号 人员id 姓名
1 001 y
2 002 n
3 003 b
4 005 v
5 006 c
6 007 x
7 008 z
结果
人员ID 时间 id出现次数
001 2013-01-01 2
001 2013-01-02 2
002 2013-01-01 4
002 2013-01-02 4
002 2013-01-03 4
002 2013-01-04 4
003 2013-01-01 3
003 2013-01-02 3
003 2013-01-03 3
004 2013-01-01 2
004 2013-01-02 2请教各位前辈 语句怎么写 数据库是access
人员编号 时间
1 2013-01-01
1 2013-01-02
2 2013-01-01
2 2013-01-02
2 2013-01-03
2 2013-01-04
3 2013-01-01
3 2013-01-02
3 2013-01-03
4 2013-01-01
4 2013-01-02
表2
人员编号 人员id 姓名
1 001 y
2 002 n
3 003 b
4 005 v
5 006 c
6 007 x
7 008 z
结果
人员ID 时间 id出现次数
001 2013-01-01 2
001 2013-01-02 2
002 2013-01-01 4
002 2013-01-02 4
002 2013-01-03 4
002 2013-01-04 4
003 2013-01-01 3
003 2013-01-02 3
003 2013-01-03 3
004 2013-01-01 2
004 2013-01-02 2请教各位前辈 语句怎么写 数据库是access
select b.人员id,a.时间,count(a.人员编号)
from 表1 a,表2 b
where a.人员编号=b.人员编号
group by b.人员id,a.时间
GO-->生成表t1if object_id('t1') is not null
drop table t1
Go
Create table t1([人员编号] smallint,[时间] datetime)
Insert into t1
Select 1,'2013-01-01'
Union all Select 1,'2013-01-02'
Union all Select 2,'2013-01-01'
Union all Select 2,'2013-01-02'
Union all Select 2,'2013-01-03'
Union all Select 2,'2013-01-04'
Union all Select 3,'2013-01-01'
Union all Select 3,'2013-01-02'
Union all Select 3,'2013-01-03'
Union all Select 4,'2013-01-01'
Union all Select 4,'2013-01-02'
-->生成表t2if object_id('t2') is not null
drop table t2
Go
Create table t2([人员编号] smallint,[人员id] nvarchar(3),[姓名] nvarchar(1))
Insert into t2
Select 1,N'001',N'y'
Union all Select 2,N'002',N'n'
Union all Select 3,N'003',N'b'
Union all Select 4,N'005',N'v'
Union all Select 5,N'006',N'c'
Union all Select 6,N'007',N'x'
Union all Select 7,N'008',N'z'SELECT
t2.人员id
,t1.时间
,(SELECT COUNT(1) FROM t1 AS x WHERE x.人员编号=t1.人员编号) AS id出现次数
from t1,t2
WHERE t1.人员编号=t2.人员编号
GROUP BY t2.人员id,t1.时间,t1.人员编号
/*
人员id 时间 id出现次数
---- ----------------------- -----------
001 2013-01-01 00:00:00.000 2
001 2013-01-02 00:00:00.000 2
002 2013-01-01 00:00:00.000 4
002 2013-01-02 00:00:00.000 4
002 2013-01-03 00:00:00.000 4
002 2013-01-04 00:00:00.000 4
003 2013-01-01 00:00:00.000 3
003 2013-01-02 00:00:00.000 3
003 2013-01-03 00:00:00.000 3
005 2013-01-01 00:00:00.000 2
005 2013-01-02 00:00:00.000 2
*/