结构就弄简单点的
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