A表 userInfo
字段 id username typeid
1 zhangsan 1
2 lisi 2
B表 userType
字段 id usertype
1 管理员
2 普通人员想要的查询结果是
id username typeid
1 zhangsan 管理员
2 李四 普通人员请问怎么写查询语句?
字段 id username typeid
1 zhangsan 1
2 lisi 2
B表 userType
字段 id usertype
1 管理员
2 普通人员想要的查询结果是
id username typeid
1 zhangsan 管理员
2 李四 普通人员请问怎么写查询语句?
from userInfo a,userType b
where a.typeid=b.id
select a.id,a.username,b.usertype
from userinfo as a join usertype as b
on a.typeid = b.id
drop table userInfo
Go
Create table userInfo([id] int,[username] nvarchar(8),[typeid] int)
Insert userInfo
select 1,N'zhangsan',1 union all
select 2,N'lisi',2
Go
if not object_id('userType') is null
drop table userType
Go
Create table userType([id] int,[usertype] nvarchar(4))
Insert userType
select 1,N'管理员' union all
select 2,N'普通人员'
Go
select a.ID,
a.[username],
b.[usertype]
from userInfo a left join userType b
on a.[typeid]=b.id
/*
ID username usertype
----------- -------- --------
1 zhangsan 管理员
2 lisi 普通人员
*/
Create table userInfo([id] int,[username] nvarchar(8),[typeid] int)
Insert userInfo
select 1,N'zhangsan',1 union all
select 2,N'lisi',2Create table userType([id] int,[usertype] nvarchar(4))
Insert userType
select 1,N'管理员' union all
select 2,N'普通人员'
select A.id, A.username, B.UserType as typerid
from userInfo A, userType B
where A.typeid=B.id
1 zhangsan 管理员
2 lisi 普通人员
select A.id, A.username, B.UserType as typerid
from userInfo A, userType B
where A.typeid=B.idselect a.id,a.username,b.usertype
from userinfo as a join usertype as b
on a.typeid = b.id
推荐用第2种,
附上一些多表查询的教程吧
http://wzms.wz51z.com/u/144/archives/2007/2764.htm