hy_CustomItem表存放某种类型会员自己定义的主页上面显示的栏目,addBy是会员id,memberType是会员类型,可能值为"lawyer"\ "office"\ "public"等,当memberType="lawyer"时与hy_lawyer表联合查询获取lawyerName,为office时与hy_office查询取得officeName....hy_lawyer表存放律师会员记录
hy_office表存放律所会员记录如果我只是想获得lawyerName的话可以通过
select itemname,lawyerName from hy_CustomItem,hy_lawyer where memberType='lawyer' and addBy=lawyerid
但是现在我想根据memberType选择与哪一个表进行联合查询并取得lawyerName 或者officename,不知道应该如何操作?
以下为建表脚本CREATE TABLE [Hy_Lawyer] (
[lawyerID] [int] IDENTITY (1, 1) NOT NULL ,
[lawyerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [Hy_office] (
[office_ID] [int] IDENTITY (1, 1) NOT NULL ,
[officeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [hy_CustomItem] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[itemName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[addBy] [int] NULL ,
[memberType] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_hy_CustomItem] PRIMARY KEY CLUSTERED
(
[itemID]
) ON [PRIMARY]
) ON [PRIMARY]
GOinsert into hy_lawyer values('王强')
insert into hy_office values('上海新锐律师事务所')insert into hy_customItem values('成功案例',1,'lawyer')
insert into hy_customItem values('成功案例',1,'office')
hy_office表存放律所会员记录如果我只是想获得lawyerName的话可以通过
select itemname,lawyerName from hy_CustomItem,hy_lawyer where memberType='lawyer' and addBy=lawyerid
但是现在我想根据memberType选择与哪一个表进行联合查询并取得lawyerName 或者officename,不知道应该如何操作?
以下为建表脚本CREATE TABLE [Hy_Lawyer] (
[lawyerID] [int] IDENTITY (1, 1) NOT NULL ,
[lawyerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [Hy_office] (
[office_ID] [int] IDENTITY (1, 1) NOT NULL ,
[officeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [hy_CustomItem] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[itemName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[addBy] [int] NULL ,
[memberType] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_hy_CustomItem] PRIMARY KEY CLUSTERED
(
[itemID]
) ON [PRIMARY]
) ON [PRIMARY]
GOinsert into hy_lawyer values('王强')
insert into hy_office values('上海新锐律师事务所')insert into hy_customItem values('成功案例',1,'lawyer')
insert into hy_customItem values('成功案例',1,'office')
union all
select itemname,officeNamefrom hy_CustomItem,hy_office where memberType='office' and addBy=office_ID
比如现在我想取得itemid=1 的添加者该如何获得
select lawyerID as addBy,lawyerName,'lawyer' memberType from hy_lawyer
union all
select office_ID as addBy ,officeName ,'office' memberType from Hy_office
) B
on a.addBy = b.addby and a.memberType = b.memberTypewhere a.itemid =1
[lawyerID] [int] IDENTITY (1, 1) NOT NULL ,
[lawyerName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_Lawyer] PRIMARY KEY NONCLUSTERED
(
[lawyerID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [Hy_office] (
[office_ID] [int] IDENTITY (1, 1) NOT NULL ,
[officeName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Hy_office] PRIMARY KEY NONCLUSTERED
(
[office_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GOCREATE TABLE [hy_CustomItem] (
[itemID] [int] IDENTITY (1, 1) NOT NULL ,
[itemName] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[addBy] [int] NULL ,
[memberType] [char] (6) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_hy_CustomItem] PRIMARY KEY CLUSTERED
(
[itemID]
) ON [PRIMARY]
) ON [PRIMARY]
GOinsert into hy_lawyer values('王强')
insert into hy_office values('上海新锐律师事务所')insert into hy_customItem values('成功案例',1,'lawyer')
insert into hy_customItem values('成功案例',1,'office')select * from hy_customItem
select * from hy_customItem A inner join (
select lawyerID as addBy,lawyerName,'lawyer' memberType from hy_lawyer
union all
select office_ID as addBy ,officeName ,'office' memberType from Hy_office
) B
on a.addBy = b.addby and a.memberType = b.memberTypewhere a.itemid =1