2表关联有关问题

2表关联问题
结构就弄简单点的

A:

id name 
526 aaa
527 bbb

B:
Aid LoginTime
526 20120404202903
526 20120406202933
527 20120406203006
...

但是我联表查询后,

SQL code

    select id,Count(id) as LoginT from A    inner join B on    id=aid    group by id

这样如果527在B表不存在时,查询结果也不会包含527

我是想如果B表不存在527时查询结果是
526 2
527 0
这样的,不知道该怎么关联好




[解决办法]
可以使用left join 与 isnull 函数实现
[解决办法]

SQL code

if OBJECT_ID('ta') is not null Drop table ta;if OBJECT_ID('tb') is not null Drop table tb;gocreate table ta(id int, name varchar(16));create table tb(Aid int, LoginTime varchar(14));goinsert into ta(id, name)select 526, 'aaa' union all select 527, 'bbb' union all select 528, 'bbb';insert into tb(Aid, LoginTime)select 526, '20120404202903' union all select 526, '20120406202933' union all select 527, '20120406203006'select id, sum(ct) as [count]from (        select ta.*, (case when aid is null then 0 else 1 end) as ct        from ta            left join tb             on id = aid      ) agroup by id 

 

查看更多 下一篇