日期:2014-05-18  浏览次数:20423 次

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;
go
create table ta(id int, name varchar(16));
create table tb(Aid int, LoginTime varchar(14));
go
insert 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
      ) a
group by id