27,579
社区成员
发帖
与我相关
我的任务
分享
select m.id as '?号',
m.fuwunumber as '服??号',
c.name as '客?名称',
i.name as '合同名称',
i.itemId as '合同?号'
from ((MaintenanceInfo m
left join ProductInfo p on m.productId = p.id)
left join ItemsInfo i on m.itemsId = i.id)
left join ClientInfo c on m.clientId = c.id
order by m.maintenanceDate desc
select m.id as '编号',m.fuwunumber as '服务编号',c.name as '客户名称',i.name as '合同名称',i.itemId as '合同编号',p.equipmentName as '设备名称',p.number as '设备序列号',m.maintenanceEngineer as '维修工程师',m.maintenanceDate as '维修时间',m.gzhunbei as '客户对工程师准备情况',m.gwancheng as '客户对工程师工作完成情况',m.gfenxi as '客户对工程师分析解决问题评价',m.ghuanjing as '客户对工程师环境整理情况',m.gtaidu as '客户对工程师工作态度评价',m.gzuoxi as '客户对工程师遵守作息情况',m.clientyijian as '客户意见',m.serviceType as '服务类型',m.isComeDown as '服务费',m.conkOutDiscription as '工作内容',m.feeDiscription as '遗留问题',m.insteadAndFee as '已解决问题',m.isInstead as '是否更换配件',m.isHavaService as '是否需要软件服务' from MaintenanceInfo m,ProductInfo p,ItemsInfo i,ClientInfo c
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ClientInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ItemsInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ItemsInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ProductInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ProductInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MaintenanceInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MaintenanceInfo]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FenLei]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[FenLei]
GO
CREATE TABLE [dbo].[ClientInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[linkMan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[business] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[telephone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[phone] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[mail] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fax] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[qq] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[msn] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[fenLeiId] [int] NULL ,
[datetime] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemsInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[itemId] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[itemAmaldar] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[itemCompleteDate] [datetime] NULL ,
[ClientId] [int] NULL ,
[itemsType] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[clientMan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[clientLikeMan] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[clientLikeTel] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ProductInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[equipmentName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[productWorkOffDate] [datetime] NULL ,
[productTimeLimit] [int] NULL ,
[productCrossDate] [datetime] NULL ,
[clientApproving] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[clientAtticude] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[itemsId] [int] NULL ,
[productCount] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[clientId] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[MaintenanceInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[serviceType] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[maintenanceEngineer] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[conkOutDiscription] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[maintenanceDate] [datetime] NULL ,
[isComeDown] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[feeDiscription] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[isInstead] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[isHavaService] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[insteadAndFee] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[productId] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[fuwufei] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[clientyijian] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[gzhunbei] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[gwancheng] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[gfenxi] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[ghuanjing] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[gtaidu] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[gzuoxi] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[fuwunumber] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[clientId] [int] NULL ,
[itemsId] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FenLei] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[typeName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[parentId] [int] NULL
) ON [PRIMARY]
GO
create table ClientInfo(id int,[name] varchar(20),linkMan varchar(20),tel varchar(20))
insert into ClientInfo
select '1','创新工厂','李开复','13912345121'
union all select '2','网易','丁磊','13812345121'
create table ItemsInfo(id int,[name] varchar(40),date datetime,clientId int)
insert into ItemsInfo
select '1','创新工厂机床采购','2009-11-17','1'
union all select '2','网易一卡通代理发行','2009-11-17','2'
create table ProductInfo(id int,[name] varchar(40), number int,itemsId int,clientId int)
insert into ProductInfo
select '1','一个非常好的主意','123','1','1'
union all select '2','新发行3000点点卡','234','2','2'
union all select '3','不知名产品','345','0','1'
create table MaintenanceInfo(id int,[name] varchar(40),productId int,itemsId int,clientId int)
insert into MaintenanceInfo
select '1','维护机床','0','1','1'
union all select '2','制造主意','1','1','1'
union all select '3','帮李总装系统','0','0','1'
union all select '4','推销点卡','2','2','2'
union all select '5','test','0','3','1'
union all select '6','特色特','3','0','1'
select M.[name],C.[name],I.[name],P.[name]
from ((MaintenanceInfo M left join ProductInfo P on M.productId=P.id)
left join ItemsInfo I on M.itemsId=I.id),ClientInfo C
where M.clientId=C.id
--结果
------------------
--维护机床 创新工厂 创新工厂机床采购 NULL
--制造主意 创新工厂 创新工厂机床采购 一个非常好的主意
--帮李总装系统 创新工厂 NULL NULL
--推销点卡 网易 网易一卡通代理发行 新发行3000点点卡
--test 创新工厂 NULL NULL
--特色特 创新工厂 NULL 不知名产品