[求助]求一条SQL语句

suntao163 发布于 2006-11-01 19:56， 821 次点击

我有两个表，这两个表是关联的，表1有serverid,servername两个字段，表2有id,serverid,shuliang3个字段，我想查servername,shuliang这两个字段，但是要求表1 中的servername全部显示，如果表2中没有相应的数据的话数量字段显示为0，应该怎么做啊？，望各位高手帮忙

10 回复

bygg2006-11-01 21:01

select a.servername,b.shuliang3 from table1 a left join table2 b on a.serverid=b.serverid

[此贴子已经被作者于2006-11-2 21:11:36编辑过]

小水滴2006-11-01 21:41

select servername,case when shuliang is null then 0 else shuling end
from table1 left join table2 on table1.serverid=table2.serverid

[此贴子已经被作者于2006-11-1 21:42:27编辑过]

LouisXIV2006-11-02 08:00

以下是引用小水滴在2006-11-1 21:41:51的发言：
select servername,case when shuliang is null then 0 else shuling end
from table1 left join table2 on table1.serverid=table2.serverid

有Isnull函数可以用, 不用写case

suntao1632006-11-02 19:26

如果我想对shuliang用sum函数应该怎么写啊，servername还是要都出来没有的话心事为0

LouisXIV2006-11-03 17:08

去看看group语句

lanselian8882006-11-03 19:36

select a.servername,b.shuliang3
from table1 as a
left outer join table2 as b
on a.serverid=b.serverid
go

CrazyWeed09072006-11-04 10:08

select a.servername,isnull(b.shuiliang3,0),sum(isnull(b.shuiliang,0)) from table1 a left join table2 b on a.serverid=b.serverid group by a.servername, b.shuiliang

right ?

小水滴2006-11-04 22:40

select servername,sum(isnull(shuliang,0))
from table1 left join table2 on table1.serverid=table2.serverid
group by servername

#10

allenduo2006-11-08 17:22

我觉得楼上的几位应该告诉楼主怎么建立视图.

#11

棉花糖ONE2006-11-09 18:34

create view viewname
as
select * from tablename