SQL语句 临时表查询

drop table b
create table B
(
 bid int primary key,
 name varchar(32) ,
 cardid varchar(32)
)
drop table a
create table A
(
 aid int references B(bid),
 loginTime varchar(32)
)

insert into A values(1,'2008-8-8')
insert into A values(2,'2008-8-9')
insert into A values(3,'2008-8-10')
insert into A values(2,'2008-8-11')
insert into A values(3,'2008-8-12')
insert into A values(2,'2008-8-14')
insert into A values(3,'2008-8-13')

insert into B values(1,'joni','111')
insert into B values(2,'tank','222')
insert into B values(3,'fiona','333')

select * from A
select * from b

--查询SQL

方法一：先把登录次数大于2的SELECT 进一张新表P

select aid as Id,count(aid) as times into p from A group by aid having count(aid)>2

select * from p

select b.name as 姓名,p.times as 登录次数 from b inner join p on b.bid=p.id

 

方法二：【推荐】一句话搞定

select b.name as 姓名,p.times as 登录次数 from b inner join (select aid as Id,count(aid) as times from A group by aid having count(aid)>2) p on b.bid=p.id