sql 查询 会的速度帮忙看看 在线等

[qq574444572]

a表 有字段 devid name dept code
1 dd pp 0001
1 dd pp 0002
1 dd pp 0003
2 aa bb 0001
2 aa bb 0002
2 aa bb 0003

b表 有字段 devid realmoney code
1 55 0001
2 77 0002

devid 相当于对于整体的标识
code 是对于小部件的标识
联合a b表 查出 各个部件的 realmoney 当a表中code 没在b表中时 让其realmoney 为0

[孤独剑_LPZ]

select a.devid,a.code,ISNULL(b.realmoney,0) realmoney from a left join b
on (a.devid=b.devid and a.code=b.code)
group by a.devid,a.code

[cd731107]

update a set realmoney=0 
where a.code not in (select code from b where b.devid=a.devid)

[ff284996867]

select a.devid,a.code,ISNULL(b.realmoney,0) realmoney 
from a left join b  on a.devid=b.devid and a.code=b.code

[孤独剑_LPZ]

[Quote=引用 1 楼 asftrhgjhkjlkttttttt 的回复:]
select a.devid,a.code,ISNULL(b.realmoney,0) realmoney from a left join b
on (a.devid=b.devid and a.code=b.code)
group by a.devid,a.code
[/Quote]
手误,将group by 这个删除即可

[chuanzhang5687]

select a.devid,isnull(realmoney,0),a.code from a left join b on a.code=b.code

[chuanzhang5687]

create table a
(
	devid	int,
	name	varchar(20),
	dept	varchar(20),
	code	int
)
create table b
(
	devid	int,
	realmoney	int,
	code	int
)

insert into a values(1 ,'dd', 'pp', 0001)
insert into a values(1 ,'dd', 'pp', 0002)
insert into a values(1 ,'dd', 'pp', 0003)
insert into a values(2 ,'aa', 'bb', 0001)
insert into a values(2 ,'aa', 'bb', 0002)
insert into a values(2 ,'aa', 'bb', 0003)
insert into b values(1,55,0001)
insert into b values(2,77,0002)

select a.devid,isnull(realmoney,0),a.code from a left join b on a.code=b.code
drop  table a
drop  table b

devid,,code
1,55,1
1,77,2
1,0,3
2,55,1
2,77,2
2,0,3

[chuanzhang5687]

select a.devid,isnull(realmoney,0)as realmoney,a.code from a left join b on a.code=b.code

devid,realmoney,code
1,55,1
1,77,2
1,0,3
2,55,1
2,77,2
2,0,3

(6 行受影响)

[qq574444572]

解决了 谢谢大家帮忙 分就给大家了

[yes20090314]

select a.devid,a.code,ISNULL(b.realmoney,0) realmoney from a left join b
on (a.devid=b.devid and a.code=b.code)
group by a.devid,a.code