表1 AA
id class_id user_id addtime
1 20 100 2007-1-1
2 20 50 2007-1-2
3 20 50 2007-1-3
4 30 100 2007-1-4表2 BB
user_id name
50 aa
100 bb
结果
class_id user_Id addtime cont(统计) username
20 100 2007-1-1 1次 bb
20 50 2007-1-3 2次 aa
id class_id user_id addtime
1 20 100 2007-1-1
2 20 50 2007-1-2
3 20 50 2007-1-3
4 30 100 2007-1-4表2 BB
user_id name
50 aa
100 bb
结果
class_id user_Id addtime cont(统计) username
20 100 2007-1-1 1次 bb
20 50 2007-1-3 2次 aa
a.*,b.name as username
from
(select
class_id,
user_Id,
count(*) as cont,
max(addtime) as addtime
from
AA
group by
class_id,user_Id) a,
BB b
where
a.user_id=b.user_id
and
a.class_id=20
select class_id,user_Id,addtime,username,cont(user_id)
from aa a left
join bb b on a.user_id=b.user_id
group by class_id,user_Id,addtime,username
a.class_id,
a.user_Id,
count(a.user_Id) as cont,
max(a.addtime) as addtime,
b.name as username
from
AA a,BB b
where
a.user_id=b.user_id
and
a.class_id=20
group by
a.class_id,a.user_id,b.name
where class_id=20
group by class_id,user_id) as a ,t2 where a.user_id = t2.user_id
-- id class_id user_id addtime
-- 1 20 100 2007-1-1
-- 2 20 50 2007-1-2
-- 3 20 50 2007-1-3
-- 4 30 100 2007-1-4
--
-- 表2 BB
-- user_id name
-- 50 aa
-- 100 bb
--
--
-- 结果
-- class_id user_Id addtime cont(统计) username
-- 20 100 2007-1-1 1次 bb
-- 20 50 2007-1-3 2次 aa
-------create test datacreate table AA
([id] int, class_id int, [user_id] int, addtime datetime)insert into AA
select 1, 20, 100, '2007-1-1'
union
select 2, 20, 50, '2007-1-2'
union
select 3, 20, 50, '2007-1-3'
union
select 4, 30, 100, '2007-1-4'create table BB
([user_id] int, [name] varchar(20))
insert into BB
select 50, 'aa'
union
select 100, 'bb'-------SQL---select a.class_id, a.[user_id], [cont(统计)], addtime, [name]
from (
select class_id, AA.[user_id],count(*) as [cont(统计)]
, substring(convert(varchar, max(addtime), 120), 1, 10) as addtime
from AA, BB
where AA.[user_id] = BB.[user_id]group by class_id, AA.[user_id] ) as a, BB
where a.[user_id] = bb.[user_id]--delete test data
drop table AA, BB---Result(4 件処理されました)
(2 件処理されました)class_id user_id cont(统计) addtime name
----------- ----------- ----------- -------------------- --------------------
20 50 2 2007-01-03 aa
20 100 1 2007-01-01 bb
30 100 1 2007-01-04 bb(3 件処理されました)
([id] int, class_id int, [user_id] int, addtime datetime)insert into AA
select 1, 20, 100, '2007-1-1'
union
select 2, 20, 50, '2007-1-2'
union
select 3, 20, 50, '2007-1-3'
union
select 4, 30, 100, '2007-1-4'create table BB
([user_id] int, [name] varchar(20))
insert into BB
select 50, 'aa'
union
select 100, 'bb'select a.class_id, a.[user_id] as [user_Id], addtime, [cont(统计)], [name]
from (
select class_id, AA.[user_id], (convert(varchar, count(*)) + '次') as [cont(统计)]
, substring(convert(varchar, max(addtime), 120), 1, 10) as addtime
from AA, BB
where AA.[user_id] = BB.[user_id]group by class_id, AA.[user_id] ) as a, BB
where a.[user_id] = bb.[user_id]
drop table AA, BB----result(4 件処理されました)
(2 件処理されました)class_id user_Id addtime cont(统计) name
----------- ----------- -------------------- -------------------------------- --------------------
20 50 2007-01-03 2次 aa
20 100 2007-01-01 1次 bb
30 100 2007-01-04 1次 bb(3 件処理されました)