请高手指点一个sql语句的写法！
表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
[解决办法]
select
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
[解决办法]
--try
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
[解决办法]
select
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
[解决办法]
select a.*,t2.name from (select class_id,user_id,max(addtime)addtime,count(*)as [count(统计)] from t1
where class_id=20
group by class_id,user_id) as a ,t2 where a.user_id = t2.user_id
[解决办法]
-- 表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
-------create test data
create 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 件処理されました)
[解决办法]
---再像一点儿的话
create 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 '
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 件処理されました)
