表A结构如下:
userid username ......
aa 王
bb 李
cc 张
.........表B结构如下
autoid userid recordid
1 aa 5
2 bb 5
3 aa 6
4 cc 7想得到如下结果:
recordid userid result
5 aa 1
5 bb 1
5 cc 0
6 aa 1
6 bb 0
6 cc 0
7 aa 0
7 bb 0
7 cc 1谢谢,谢谢!
userid username ......
aa 王
bb 李
cc 张
.........表B结构如下
autoid userid recordid
1 aa 5
2 bb 5
3 aa 6
4 cc 7想得到如下结果:
recordid userid result
5 aa 1
5 bb 1
5 cc 0
6 aa 1
6 bb 0
6 cc 0
7 aa 0
7 bb 0
7 cc 1谢谢,谢谢!
from a
left join b on a.userid=b.userid
group by recordid,a.userid
from a
left join b on a.userid=b.userid
group by recordid,a.userid
insert into @t select 'aa','王'
union all select 'bb','李'
union all select 'cc','张'declare @a table(autoid int,userid varchar(10),recordid int)
insert into @a select 1,'aa',5
union all select 2,'bb',5
union all select 3,'aa',6
union all select 4,'cc',7select x.recordid,
x.userid,
result=case when exists(select 1 from @a where x.userid=y.userid and x.recordid=y.recordid) then 1 else 0 end
from
(select *
from
(select distinct userid from @t)a,
(select distinct recordid from @a)b
)x,@a y
where
x.userid*=y.userid and
x.recordid*=y.recordid
order by x.recordid
x.userid*=y.userid and
x.recordid*=y.recordid星号起什么作用啊?还没见过。。
from (select distinct b.recordid, a.userid from a,b ) c
left join b
on c.recordid=b.recordid and c.userid=b.userid
group by c.recordid,c.userid
order by c.recordid,c.userid