有这样 四个表 请帮忙写出一个 sql语句

[chang1216]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BM_Person]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BM_Person]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BM_zhiwu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BM_zhiwu]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BM_yuangong]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BM_yuangong]
GO

CREATE TABLE [dbo].[BM_Person] (
[bm_id] [int] NULL ,
[person_id] [int] NULL ,
[zhiwu_id] [int] NULL
) ON [PRIMARY]
GO
输出结果 是 一个人员姓名等信息，和他所在部门 所在部门职务
注：一个人员可能对应两个部门 并且在不同的部门任不同的职务

CREATE TABLE [dbo].[BM] (
[BM_id] [int] IDENTITY (1, 1) NOT NULL ,
[BM_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[BM_miaoshu] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BM_zhiwu] (
[zhiwu_id] [int] IDENTITY (1, 1) NOT NULL ,
[zhiwu_name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[zhiwu_bm_id] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[BM_yuangong] (
[Person_id] [int] IDENTITY (1, 1) NOT NULL ,
[Person_zhuangtai] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_daima] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_xingming] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_xingbie] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_jiguan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_chusheng] [datetime] NULL ,
[Person_ruzhi] [datetime] NULL ,
[Person_xueli] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_xuexiao] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_zhuanye] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_dianhua] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_fenji] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_shouji] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_dizhi] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_youxiang] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_zhaopian] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_zw_id] [int] NULL ,
[Person_gongzuo] [varchar] (2000) COLLATE Chinese_PRC_CI_AS NULL ,
[Person_lizhi_date] [datetime] NULL ,
[Person_lizhi_yuanyin] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

[WangZWang]

--参考

Select distinct a.Person_xingming as 姓名,b.bm_name as 部门,zhiwu_name as 职务
from BM_yuangong as a ,bm as b,BM_zhiwu as c,BM_Person as d
where a.Person_id=d.Person_id and d.bm_id=b.bm_id and d.zhiwu_id=c.zhiwu_id

[chenzhuo]

select BM_yuangong.Personid,BM_yuangong.Person_xingming,BM.BM_name,BM_zhiwu.zhiwu_name
from BM_yuangong, BM_Pserson,BM, BM_zhiwu
where yg.Person_id=BM_Pserson.Person_id
and BM_Pserson.bm_id = BM.bm_id
and BM_Person.zhiwu_id=BM_zhiwu.zhiwu_id

[paoluo]

Select
A.Person_xingming As 姓名,
C.BM_name As 部门,
D.zhiwu_name As 职务
From
BM_yuangong A
Inner Join
BM_Person B
On A.Person_id = B.Person_id
Inner Join
BM C
On B.BM_id = C.BM_id
Inner Join
BM_zhiwu D
On B.zhiwu_id = D.zhiwu_id And C.BM_id = D.zhiwu_bm_id
Where A.Person_xingming = '查詢的姓名'

[chang1216]

没有配置 职务的也要显示 如何显示 没有配置职务的 职务哪里显示一个 空

[paoluo]

或者

Select
A.Person_xingming As 姓名,
C.BM_name As 部门,
D.zhiwu_name As 职务
From
BM_yuangong A, BM_Person B, BM C, BM_zhiwu D
Where A.Person_id = B.Person_id And B.BM_id = C.BM_id And B.zhiwu_id = D.zhiwu_id And C.BM_id = D.zhiwu_bm_id And A.Person_xingming = '查詢的姓名'

[chang1216]

我是以部门为条件 比如 一个bm_id=1 输出所有 部门id为1 的人 后面 带有 职务 如果暂时没有职务 就显示 空 这样如何 输出

[chenzhuo]

select BM_yuangong.Personid,BM_yuangong.Person_xingming,BM.BM_name,BM_zhiwu.zhiwu_name
from BM_yuangong, BM_Pserson,BM, BM_zhiwu
where yg.Person_id=BM_Pserson.Person_id
and BM_Pserson.bm_id = BM.bm_id
and BM_Person.zhiwu_id*=BM_zhiwu.zhiwu_id

[WangZWang]

--如:
Select distinct a.Person_xingming as 姓名,b.bm_name as 部门,zhiwu_name as 职务
from BM_yuangong as a inner join BM_Person as d on a.Person_id=d.Person_id
inner join bm as b on d.bm_id=b.bm_id
left join BM_zhiwu as c on d.zhiwu_id=c.zhiwu_id
where b.bm_id=?

[chang1216]

不错 问题 解决 谢谢 各位兄弟 我想请问一下 我的表 设计有没有 问题

[paoluo]

chang1216(又有人要结婚了) ( ) 信誉：100 Blog 加为好友 2007-04-19 09:47:48 得分: 0


没有配置 职务的也要显示 如何显示 没有配置职务的 职务哪里显示一个 空


---------
關聯BM_zhiwu 表的時候，用Left Join即可

[chang1216]

先来 大哥 已经解决 这个问题 谢谢大家

[chang1216]

那么 我还想问 一下 我在 查看某一个 人的时候 要显示 他的的 职务和部门 还有 其它个人信息 如何来写 这个sql 语句 谢谢

[paoluo]

Select
A.Person_xingming As 姓名,
A.Person_xingbie As 性別, --在這裡加入你要查詢出來的字段
C.BM_name As 部门,
D.zhiwu_name As 职务
From
BM_yuangong A
Inner Join
BM_Person B
On A.Person_id = B.Person_id
Inner Join
BM C
On B.BM_id = C.BM_id
Left Join
BM_zhiwu D
On B.zhiwu_id = D.zhiwu_id And C.BM_id = D.zhiwu_bm_id
Where A.Person_xingming = '查詢的姓名'

[WangZWang]

Select distinct a.Person_xingming as 姓名,b.bm_name as 部门,zhiwu_name as 职务，
a.* --列举出需要的人员信息列
from BM_yuangong as a inner join BM_Person as d on a.Person_id=d.Person_id
inner join bm as b on d.bm_id=b.bm_id
left join BM_zhiwu as c on d.zhiwu_id=c.zhiwu_id
where a.Person_id=人员编码

[chang1216]

CREATE PROCEDURE lanchina_person_show
@person_id int
as
Select a.* , b.bm_name as bm_name,c.zhiwu_name as zhiwu_name,
case a.Person_zhuangtai
WHEN 0 THEN '离职'
WHEN 1 THEN '试用在职'
WHEN 2 THEN '正式在职'
end as Person_zhuangtai1
from BM_yuangong as a inner join BM_Person as d on a.Person_id=d.Person_id
inner join bm as b on d.bm_id=b.bm_id
left join BM_zhiwu as c on d.zhiwu_id=c.zhiwu_id
where a.Person_id=@person_id
GO
整个存储过程 是这样写的 可是 没有查询到 结果阿

[WangZWang]

首先确认 @person_id 的人员代码在表中是否有，
在看一下 BM_yuangong ， BM_Person 已经BM表之间有何对应关系，

[paoluo]

--或者都改用left join試試

CREATE PROCEDURE lanchina_person_show
@person_id int
as
Select a.* , b.bm_name as bm_name,c.zhiwu_name as zhiwu_name,
case a.Person_zhuangtai
WHEN 0 THEN '离职'
WHEN 1 THEN '试用在职'
WHEN 2 THEN '正式在职'
end as Person_zhuangtai1
from BM_yuangong as a left join BM_Person as d on a.Person_id=d.Person_id
left join bm as b on d.bm_id=b.bm_id
left join BM_zhiwu as c on d.zhiwu_id=c.zhiwu_id
where a.Person_id=@person_id
GO

[chang1216]

厉害 真的 佩服啊

[chang1216]

接分了 兄弟们

[chang1216]

不知道 怎么加分 请谅解 以后 多多给分