SQL2014排序自动匹配表别名

Hello World, 2017-12-30 05:15:45
问题原由：
老系统升级数据库到2014后发现查询出错，原来的查询语句中排序字段的表别名与字段不匹配时SQL自动去匹配了查询结果列，比如
select a.code,b.name from a,b order by a.code,a.name

其实a表里面没有name列，但查询结果有name列，SQL自己去匹配到了，不会出错。但到了2014版本就不行了，很多地方都有问题，又没有源码，有没办法让SQL2014像SQL2008那样自动修正？
...全文
537 10 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
zjcxc 2018-01-02
  • 打赏
  • 举报
 回复
考虑版本降级吧
Hello World, 2018-01-02
  • 打赏
  • 举报
 回复
引用 8 楼 z10843087 的回复:
[quote=引用 6 楼 apollokk 的回复:] [quote=引用 4 楼 z10843087 的回复:] 你把兼容级别设置到2008的兼容级别试试
检查了原来的配置，兼容级别是2000的，但现在2014兼容级别最低是2008，会出错，不能兼容到2000[/quote] 你升级不应该这么升，你在2008的时候就应该把兼容级别设置成2008的兼容级别，然后修正里面的错误，修正不了的时候还可以暂时修改为2000的先用着。现在2014用不了 2000的兼容级别.只能想办法修改语句了[/quote] 是啊，没考虑周全。这程序是买的，只能找开发商解决了。
OwenZeng_DBA 2018-01-02
  • 打赏
  • 举报
 回复
引用 6 楼 apollokk 的回复:
[quote=引用 4 楼 z10843087 的回复:] 你把兼容级别设置到2008的兼容级别试试
检查了原来的配置，兼容级别是2000的，但现在2014兼容级别最低是2008，会出错，不能兼容到2000[/quote] 你升级不应该这么升，你在2008的时候就应该把兼容级别设置成2008的兼容级别，然后修正里面的错误，修正不了的时候还可以暂时修改为2000的先用着。现在2014用不了 2000的兼容级别.只能想办法修改语句了
Hello World, 2018-01-02
  • 打赏
  • 举报
 回复
引用 5 楼 yenange 的回复:
在 SQL Server2014 上运行没有任何问题： 
PRINT @@VERSION
/*
Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
	Jun 17 2016 19:14:09 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )
*/
---在 tempdb 中创建测试表
USE [tempdb]
GO
IF OBJECT_ID('[dbo].[Kq_result]') IS NOT NULL
	DROP TABLE [dbo].[Kq_result]
GO
CREATE TABLE [dbo].[Kq_result](
	[FDate] [datetime] NOT NULL,
	[EmpID] [int] NOT NULL,
	[BanZhi] [char](4) NOT NULL,
	[ChiDaoSj] [int] NULL,
	[ChiDaoCs] [int] NULL,
	[ZaoTuiSj] [int] NULL,
	[ZaoTuiCs] [int] NULL,
	[KgSj] [int] NULL,
	[KgCs] [int] NULL,
	[Rcjb1] [int] NULL,
	[Rcjb2] [int] NULL,
	[RcjbOver0] [int] NULL,
	[RcjbAll] [int] NULL,
	[YcqSj] [int] NULL,
	[SjcqSj] [int] NULL,
	[B1SbTime] [int] NULL,
	[B1XbTime] [int] NULL,
	[B2SbTime] [int] NULL,
	[B2XbTime] [int] NULL,
	[B3SbTime] [int] NULL,
	[B3XbTime] [int] NULL,
	[B4SbTime] [int] NULL,
	[B4XbTime] [int] NULL,
	[FjbcName11] [varchar](16) NULL,
	[YB11SbTime] [int] NULL,
	[YB11XbTime] [int] NULL,
	[B11SbTime] [int] NULL,
	[B11XbTime] [int] NULL,
	[FjbcName12] [varchar](16) NULL,
	[YB12SbTime] [int] NULL,
	[YB12XbTime] [int] NULL,
	[B12SbTime] [int] NULL,
	[B12XbTime] [int] NULL,
	[KgSj_cd] [int] NULL,
	[KgCs_cd] [int] NULL,
	[KgSj_zt] [int] NULL,
	[KgCs_zt] [int] NULL,
	[JbKgSj] [int] NULL,
	[QkID] [int] NULL,
	[IfLock] [bit] NULL,
	[NotTqDK] [int] NULL,
	[NotThDK] [int] NULL,
	[B1ChiDaoSj] [int] NULL,
	[B2ChiDaoSj] [int] NULL,
	[B3ChiDaoSj] [int] NULL,
	[B4ChiDaoSj] [int] NULL,
	[B1ZaoTuiSj] [int] NULL,
	[B2ZaoTuiSj] [int] NULL,
	[B3ZaoTuiSj] [int] NULL,
	[B4ZaoTuiSj] [int] NULL,
	[Tiaoxiu] [bit] NULL,
	[Dttype] [varchar](20) NULL,
	[G_gs] [numeric](12, 2) NULL,
	[G_gx] [numeric](12, 2) NULL,
	[G_sj] [numeric](12, 2) NULL,
	[G_wxfj] [numeric](12, 2) NULL,
	[G_sfzmhb] [numeric](12, 0) NULL,
	[G_sfhb] [numeric](12, 0) NULL,
	[G_srjbxs] [numeric](12, 2) NULL,
	[G_bzqj] [numeric](12, 1) NULL,
	[G_ckkk] [numeric](12, 0) NULL,
	[G_cqsj] [numeric](12, 2) NULL,
	[G_cqts] [numeric](12, 2) NULL,
	[G_cqxs] [numeric](12, 2) NULL,
	[G_czks] [numeric](12, 2) NULL,
	[G_gsxs] [numeric](12, 2) NULL,
	[G_gxxs] [numeric](12, 2) NULL,
	[G_ifyb] [numeric](12, 0) NULL,
	[G_jrjbxs] [numeric](12, 2) NULL,
	[G_kgts] [numeric](12, 2) NULL,
	[G_kgxs] [numeric](12, 2) NULL,
	[G_psjbxs] [numeric](12, 2) NULL,
	[G_qjxs] [numeric](12, 2) NULL,
	[G_qkcs] [numeric](12, 0) NULL,
	[G_sjxs] [numeric](12, 2) NULL,
	[G_txbt] [numeric](12, 1) NULL,
	[G_type] [numeric](12, 0) NULL,
	[G_wqqj] [numeric](12, 0) NULL,
	[G_wxfjxs] [numeric](12, 2) NULL,
	[G_ycqsj] [numeric](12, 2) NULL,
	[G_ycqxs] [numeric](12, 2) NULL,
	[G_zmjbxs] [numeric](12, 2) NULL,
	[G_zmwjb] [numeric](12, 2) NULL,
	[G_nianj] [numeric](12, 2) NULL,
	[G_baiban] [numeric](12, 2) NULL,
	[G_yeban] [numeric](12, 2) NULL,
	[G_baban2] [numeric](12, 2) NULL,
	[G_yeban2] [numeric](12, 2) NULL,
	[G_baban3] [numeric](12, 2) NULL,
	[G_yeban3] [numeric](12, 2) NULL,
	[G_bzbxs] [numeric](12, 2) NULL,
	[G_yzbxs] [numeric](12, 2) NULL,
	[G_kq] [numeric](12, 0) NULL,
	[G_over0] [numeric](12, 2) NULL,
	[G_jrjb1] [numeric](12, 2) NULL,
	[G_jrjb2] [numeric](12, 2) NULL,
	[G_jrzbxs] [numeric](12, 2) NULL,
	[G_yxjss] [numeric](18, 2) NULL,
	[G_ybbz] [numeric](18, 2) NULL,
	[G_ycqts] [numeric](18, 2) NULL,
	[G_fangj] [numeric](12, 2) NULL,
	[G_njss] [numeric](12, 2) NULL,
	[G_yxfdj] [numeric](12, 2) NULL,
	[G_hunj] [numeric](12, 0) NULL,
	[G_chanj] [numeric](12, 0) NULL,
	[G_bjb] [numeric](12, 2) NULL,
	[Chidaosj0] [int] NULL,
	[Chidaosj1] [int] NULL,
	[Zaotuisj0] [int] NULL,
	[Zaotuisj1] [int] NULL,
	[G_cjxs] [numeric](12, 0) NULL,
	[G_yxfj] [numeric](12, 0) NULL,
	[G_hjxs] [numeric](12, 2) NULL,
	[G_zero0] [numeric](12, 2) NULL,
	[G_bj] [numeric](12, 0) NULL,
	[G_bjxs] [numeric](12, 0) NULL,
	[G_pchanj] [numeric](12, 0) NULL,
	[G_pcjxs] [numeric](12, 0) NULL,
	[G_sangj] [numeric](12, 0) NULL,
	[G_sanjxs] [numeric](12, 2) NULL,
	[G_kg] [numeric](12, 0) NULL,
	[code] [nchar](10) NULL,
 CONSTRAINT [PK_Kq_result] PRIMARY KEY CLUSTERED 
(
	[FDate] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[ZlEmployee]') IS NOT NULL
	DROP TABLE [dbo].[ZlEmployee]
GO
CREATE TABLE [dbo].[ZlEmployee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Dept] [varchar](24) NOT NULL,
	[Code] [char](12) NOT NULL,
	[CardNo] [char](12) NOT NULL,
	[Name] [char](20) NOT NULL,
	[EName] [varchar](40) NULL,
	[Sfz] [char](18) NOT NULL,
	[BornDate] [datetime] NOT NULL,
	[Sex] [bit] NOT NULL,
	[PyDate] [datetime] NOT NULL,
	[SyMonths] [int] NULL,
	[ZzDate] [datetime] NULL,
	[RoomBed] [varchar](12) NULL,
	[IfDaKa] [bit] NOT NULL,
	[State] [tinyint] NOT NULL,
	[LzDate] [datetime] NULL,
	[LzCause] [char](2) NULL,
	[Memo] [varchar](60) NULL,
	[Photo] [image] NULL,
	[ZhiJi] [char](6) NULL,
	[ZhiWu] [char](6) NULL,
	[XueLi] [char](6) NULL,
	[HunYin] [char](6) NULL,
	[JiGuan] [varchar](6) NULL,
	[Nation] [char](6) NULL,
	[Jxfs] [char](6) NULL,
	[LzTc] [int] NULL,
	[GongZhong] [char](6) NULL,
	[DeBz] [char](4) NULL,
	[cy] [bit] NOT NULL,
	[Mark] [bit] NULL,
	[oldroombed] [varchar](10) NULL,
	[TcDate] [datetime] NULL,
	[Rzdate] [datetime] NULL,
	[emppassword] [varchar](10) NULL,
	[CostCenter] [varchar](24) NULL,
	[G_htjj] [numeric](12, 2) NULL,
	[G_bxf] [numeric](12, 2) NULL,
	[G_ifdc] [numeric](12, 2) NULL,
	[G_xxts] [numeric](12, 0) NULL,
	[G_yljj] [numeric](12, 0) NULL,
	[G_bory] [varchar](2) NULL,
	[address] [varchar](100) NULL,
	[sfzdate0] [datetime] NULL,
	[sfzdate1] [datetime] NULL,
	[G_htsx] [numeric](12, 2) NULL,
	[G_htxx] [numeric](12, 2) NULL,
	[G_hts] [numeric](12, 2) NULL,
	[G_notb] [numeric](12, 0) NULL,
	[lzreason] [varchar](20) NULL,
	[G_jtzz] [varchar](80) NULL,
	[G_notd] [numeric](12, 2) NULL,
	[G_date0] [datetime] NULL,
	[G_date1] [datetime] NULL,
	[G_sfzd0] [datetime] NULL,
	[G_sfzd1] [datetime] NULL,
	[G_sfzdate0] [datetime] NULL,
	[G_sfzdate1] [datetime] NULL,
 CONSTRAINT [PK_ZlEmployee] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[ZlDept]') IS NOT NULL
	DROP TABLE [dbo].[ZlDept]
GO
CREATE TABLE [dbo].[ZlDept](
	[Code] [varchar](24) NOT NULL,
	[Name] [varchar](40) NOT NULL,
	[TreeLevel] [tinyint] NOT NULL,
	[CurCode] [varchar](20) NULL,
	[UpCode] [varchar](20) NULL,
	[LongName] [varchar](70) NULL,
	[Manager] [varchar](10) NULL,
	[Tel] [varchar](18) NULL,
	[Note] [varchar](40) NULL,
	[BianZhiRs] [int] NULL,
	[hSq] [int] NULL,
	[ifyx] [bit] NULL,
	[deptdesc] [varchar](18) NULL,
 CONSTRAINT [PK_ZlDept] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[Kq_BanZhi]') IS NOT NULL
	DROP TABLE [dbo].[Kq_BanZhi]
GO
CREATE TABLE [dbo].[Kq_BanZhi](
	[Code] [char](4) NOT NULL,
	[Name] [varchar](16) NOT NULL,
	[Zsj] [int] NULL,
	[BanCiCount] [tinyint] NULL,
	[IfFdJcsj] [bit] NOT NULL,
	[IfFdjb] [bit] NULL,
	[CanZhi] [char](5) NULL,
	[IfDLJCDZT] [bit] NULL,
	[Note] [char](60) NULL,
	[IfBz] [bit] NULL,
	[Mark] [varchar](10) NULL,
 CONSTRAINT [PK_Kq_BanZhi] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[E_ZhiWu]') IS NOT NULL
	DROP TABLE [dbo].[E_ZhiWu]
GO
CREATE TABLE [dbo].[E_ZhiWu](
	[Code] [char](10) NOT NULL,
	[Name] [char](80) NULL,
	[ZhiwuJt] [numeric](12, 2) NULL,
	[Note] [varchar](255) NULL
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[E_ZhiJi]') IS NOT NULL
	DROP TABLE [dbo].[E_ZhiJi]
GO
CREATE TABLE [dbo].[E_ZhiJi](
	[Code] [char](6) NOT NULL,
	[Name] [varchar](80) NOT NULL,
	[Note] [varchar](255) NULL,
 CONSTRAINT [PK_ZhiJi__10] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--------- 下面是查询语句 -----------------
SELECT sLock=(Case when isnull(Q.ifLock,0)=1 then '*' else '' end),
     Q.*,  Code=E.Code,Name=E.Name,EName=E.EName,CardNo=E.CardNo,Dept=E.Dept
    ,ZhiWu=E.ZhiWu,sZhiWu=ZW.Name,ZhiJi=E.ZhiJi,sZhiJi=Zj.Name
    ,PyDate=E.PyDate,sSex =(CASE Sex WHEN 0 THEN '男' ELSE '女' END)
    ,DeptName =D.LongName,BanZhiName =B.Name
FROM Kq_Result Q INNER JOIN ZlEmployee E ON Q.EmpID = E.ID
  INNER JOIN ZlDept D ON E.Dept = D.Code
  LEFT JOIN Kq_BanZhi B ON Q.BanZhi = B.Code
  LEFT JOIN E_ZhiWu ZW ON E.ZhiWu=ZW.Code
  LEFT JOIN E_ZhiJi Zj ON E.ZhiJi = Zj.Code
WHERE (Q.FDate BETWEEN '2017-12-01' AND '2017-12-30') AND (Dept LIKE '%') AND Q.EmpID =14418 
ORDER BY Q.FDate,Q.Code
抱歉，kq_result里面的code是后面测试时加上去的，原来没有。 原系统是2008兼容模式是2000的，现在用2014，最低兼容2008，没有2000，所以还是出错了
Hello World, 2018-01-02
  • 打赏
  • 举报
 回复
引用 4 楼 z10843087 的回复:
你把兼容级别设置到2008的兼容级别试试
检查了原来的配置，兼容级别是2000的，但现在2014兼容级别最低是2008，会出错，不能兼容到2000
吉普赛的歌 2017-12-30
  • 打赏
  • 举报
 回复
在 SQL Server2014 上运行没有任何问题： 
PRINT @@VERSION
/*
Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 
	Jun 17 2016 19:14:09 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: )
*/
---在 tempdb 中创建测试表
USE [tempdb]
GO
IF OBJECT_ID('[dbo].[Kq_result]') IS NOT NULL
	DROP TABLE [dbo].[Kq_result]
GO
CREATE TABLE [dbo].[Kq_result](
	[FDate] [datetime] NOT NULL,
	[EmpID] [int] NOT NULL,
	[BanZhi] [char](4) NOT NULL,
	[ChiDaoSj] [int] NULL,
	[ChiDaoCs] [int] NULL,
	[ZaoTuiSj] [int] NULL,
	[ZaoTuiCs] [int] NULL,
	[KgSj] [int] NULL,
	[KgCs] [int] NULL,
	[Rcjb1] [int] NULL,
	[Rcjb2] [int] NULL,
	[RcjbOver0] [int] NULL,
	[RcjbAll] [int] NULL,
	[YcqSj] [int] NULL,
	[SjcqSj] [int] NULL,
	[B1SbTime] [int] NULL,
	[B1XbTime] [int] NULL,
	[B2SbTime] [int] NULL,
	[B2XbTime] [int] NULL,
	[B3SbTime] [int] NULL,
	[B3XbTime] [int] NULL,
	[B4SbTime] [int] NULL,
	[B4XbTime] [int] NULL,
	[FjbcName11] [varchar](16) NULL,
	[YB11SbTime] [int] NULL,
	[YB11XbTime] [int] NULL,
	[B11SbTime] [int] NULL,
	[B11XbTime] [int] NULL,
	[FjbcName12] [varchar](16) NULL,
	[YB12SbTime] [int] NULL,
	[YB12XbTime] [int] NULL,
	[B12SbTime] [int] NULL,
	[B12XbTime] [int] NULL,
	[KgSj_cd] [int] NULL,
	[KgCs_cd] [int] NULL,
	[KgSj_zt] [int] NULL,
	[KgCs_zt] [int] NULL,
	[JbKgSj] [int] NULL,
	[QkID] [int] NULL,
	[IfLock] [bit] NULL,
	[NotTqDK] [int] NULL,
	[NotThDK] [int] NULL,
	[B1ChiDaoSj] [int] NULL,
	[B2ChiDaoSj] [int] NULL,
	[B3ChiDaoSj] [int] NULL,
	[B4ChiDaoSj] [int] NULL,
	[B1ZaoTuiSj] [int] NULL,
	[B2ZaoTuiSj] [int] NULL,
	[B3ZaoTuiSj] [int] NULL,
	[B4ZaoTuiSj] [int] NULL,
	[Tiaoxiu] [bit] NULL,
	[Dttype] [varchar](20) NULL,
	[G_gs] [numeric](12, 2) NULL,
	[G_gx] [numeric](12, 2) NULL,
	[G_sj] [numeric](12, 2) NULL,
	[G_wxfj] [numeric](12, 2) NULL,
	[G_sfzmhb] [numeric](12, 0) NULL,
	[G_sfhb] [numeric](12, 0) NULL,
	[G_srjbxs] [numeric](12, 2) NULL,
	[G_bzqj] [numeric](12, 1) NULL,
	[G_ckkk] [numeric](12, 0) NULL,
	[G_cqsj] [numeric](12, 2) NULL,
	[G_cqts] [numeric](12, 2) NULL,
	[G_cqxs] [numeric](12, 2) NULL,
	[G_czks] [numeric](12, 2) NULL,
	[G_gsxs] [numeric](12, 2) NULL,
	[G_gxxs] [numeric](12, 2) NULL,
	[G_ifyb] [numeric](12, 0) NULL,
	[G_jrjbxs] [numeric](12, 2) NULL,
	[G_kgts] [numeric](12, 2) NULL,
	[G_kgxs] [numeric](12, 2) NULL,
	[G_psjbxs] [numeric](12, 2) NULL,
	[G_qjxs] [numeric](12, 2) NULL,
	[G_qkcs] [numeric](12, 0) NULL,
	[G_sjxs] [numeric](12, 2) NULL,
	[G_txbt] [numeric](12, 1) NULL,
	[G_type] [numeric](12, 0) NULL,
	[G_wqqj] [numeric](12, 0) NULL,
	[G_wxfjxs] [numeric](12, 2) NULL,
	[G_ycqsj] [numeric](12, 2) NULL,
	[G_ycqxs] [numeric](12, 2) NULL,
	[G_zmjbxs] [numeric](12, 2) NULL,
	[G_zmwjb] [numeric](12, 2) NULL,
	[G_nianj] [numeric](12, 2) NULL,
	[G_baiban] [numeric](12, 2) NULL,
	[G_yeban] [numeric](12, 2) NULL,
	[G_baban2] [numeric](12, 2) NULL,
	[G_yeban2] [numeric](12, 2) NULL,
	[G_baban3] [numeric](12, 2) NULL,
	[G_yeban3] [numeric](12, 2) NULL,
	[G_bzbxs] [numeric](12, 2) NULL,
	[G_yzbxs] [numeric](12, 2) NULL,
	[G_kq] [numeric](12, 0) NULL,
	[G_over0] [numeric](12, 2) NULL,
	[G_jrjb1] [numeric](12, 2) NULL,
	[G_jrjb2] [numeric](12, 2) NULL,
	[G_jrzbxs] [numeric](12, 2) NULL,
	[G_yxjss] [numeric](18, 2) NULL,
	[G_ybbz] [numeric](18, 2) NULL,
	[G_ycqts] [numeric](18, 2) NULL,
	[G_fangj] [numeric](12, 2) NULL,
	[G_njss] [numeric](12, 2) NULL,
	[G_yxfdj] [numeric](12, 2) NULL,
	[G_hunj] [numeric](12, 0) NULL,
	[G_chanj] [numeric](12, 0) NULL,
	[G_bjb] [numeric](12, 2) NULL,
	[Chidaosj0] [int] NULL,
	[Chidaosj1] [int] NULL,
	[Zaotuisj0] [int] NULL,
	[Zaotuisj1] [int] NULL,
	[G_cjxs] [numeric](12, 0) NULL,
	[G_yxfj] [numeric](12, 0) NULL,
	[G_hjxs] [numeric](12, 2) NULL,
	[G_zero0] [numeric](12, 2) NULL,
	[G_bj] [numeric](12, 0) NULL,
	[G_bjxs] [numeric](12, 0) NULL,
	[G_pchanj] [numeric](12, 0) NULL,
	[G_pcjxs] [numeric](12, 0) NULL,
	[G_sangj] [numeric](12, 0) NULL,
	[G_sanjxs] [numeric](12, 2) NULL,
	[G_kg] [numeric](12, 0) NULL,
	[code] [nchar](10) NULL,
 CONSTRAINT [PK_Kq_result] PRIMARY KEY CLUSTERED 
(
	[FDate] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[ZlEmployee]') IS NOT NULL
	DROP TABLE [dbo].[ZlEmployee]
GO
CREATE TABLE [dbo].[ZlEmployee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Dept] [varchar](24) NOT NULL,
	[Code] [char](12) NOT NULL,
	[CardNo] [char](12) NOT NULL,
	[Name] [char](20) NOT NULL,
	[EName] [varchar](40) NULL,
	[Sfz] [char](18) NOT NULL,
	[BornDate] [datetime] NOT NULL,
	[Sex] [bit] NOT NULL,
	[PyDate] [datetime] NOT NULL,
	[SyMonths] [int] NULL,
	[ZzDate] [datetime] NULL,
	[RoomBed] [varchar](12) NULL,
	[IfDaKa] [bit] NOT NULL,
	[State] [tinyint] NOT NULL,
	[LzDate] [datetime] NULL,
	[LzCause] [char](2) NULL,
	[Memo] [varchar](60) NULL,
	[Photo] [image] NULL,
	[ZhiJi] [char](6) NULL,
	[ZhiWu] [char](6) NULL,
	[XueLi] [char](6) NULL,
	[HunYin] [char](6) NULL,
	[JiGuan] [varchar](6) NULL,
	[Nation] [char](6) NULL,
	[Jxfs] [char](6) NULL,
	[LzTc] [int] NULL,
	[GongZhong] [char](6) NULL,
	[DeBz] [char](4) NULL,
	[cy] [bit] NOT NULL,
	[Mark] [bit] NULL,
	[oldroombed] [varchar](10) NULL,
	[TcDate] [datetime] NULL,
	[Rzdate] [datetime] NULL,
	[emppassword] [varchar](10) NULL,
	[CostCenter] [varchar](24) NULL,
	[G_htjj] [numeric](12, 2) NULL,
	[G_bxf] [numeric](12, 2) NULL,
	[G_ifdc] [numeric](12, 2) NULL,
	[G_xxts] [numeric](12, 0) NULL,
	[G_yljj] [numeric](12, 0) NULL,
	[G_bory] [varchar](2) NULL,
	[address] [varchar](100) NULL,
	[sfzdate0] [datetime] NULL,
	[sfzdate1] [datetime] NULL,
	[G_htsx] [numeric](12, 2) NULL,
	[G_htxx] [numeric](12, 2) NULL,
	[G_hts] [numeric](12, 2) NULL,
	[G_notb] [numeric](12, 0) NULL,
	[lzreason] [varchar](20) NULL,
	[G_jtzz] [varchar](80) NULL,
	[G_notd] [numeric](12, 2) NULL,
	[G_date0] [datetime] NULL,
	[G_date1] [datetime] NULL,
	[G_sfzd0] [datetime] NULL,
	[G_sfzd1] [datetime] NULL,
	[G_sfzdate0] [datetime] NULL,
	[G_sfzdate1] [datetime] NULL,
 CONSTRAINT [PK_ZlEmployee] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[ZlDept]') IS NOT NULL
	DROP TABLE [dbo].[ZlDept]
GO
CREATE TABLE [dbo].[ZlDept](
	[Code] [varchar](24) NOT NULL,
	[Name] [varchar](40) NOT NULL,
	[TreeLevel] [tinyint] NOT NULL,
	[CurCode] [varchar](20) NULL,
	[UpCode] [varchar](20) NULL,
	[LongName] [varchar](70) NULL,
	[Manager] [varchar](10) NULL,
	[Tel] [varchar](18) NULL,
	[Note] [varchar](40) NULL,
	[BianZhiRs] [int] NULL,
	[hSq] [int] NULL,
	[ifyx] [bit] NULL,
	[deptdesc] [varchar](18) NULL,
 CONSTRAINT [PK_ZlDept] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[Kq_BanZhi]') IS NOT NULL
	DROP TABLE [dbo].[Kq_BanZhi]
GO
CREATE TABLE [dbo].[Kq_BanZhi](
	[Code] [char](4) NOT NULL,
	[Name] [varchar](16) NOT NULL,
	[Zsj] [int] NULL,
	[BanCiCount] [tinyint] NULL,
	[IfFdJcsj] [bit] NOT NULL,
	[IfFdjb] [bit] NULL,
	[CanZhi] [char](5) NULL,
	[IfDLJCDZT] [bit] NULL,
	[Note] [char](60) NULL,
	[IfBz] [bit] NULL,
	[Mark] [varchar](10) NULL,
 CONSTRAINT [PK_Kq_BanZhi] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[E_ZhiWu]') IS NOT NULL
	DROP TABLE [dbo].[E_ZhiWu]
GO
CREATE TABLE [dbo].[E_ZhiWu](
	[Code] [char](10) NOT NULL,
	[Name] [char](80) NULL,
	[ZhiwuJt] [numeric](12, 2) NULL,
	[Note] [varchar](255) NULL
) ON [PRIMARY]
GO
IF OBJECT_ID('[dbo].[E_ZhiJi]') IS NOT NULL
	DROP TABLE [dbo].[E_ZhiJi]
GO
CREATE TABLE [dbo].[E_ZhiJi](
	[Code] [char](6) NOT NULL,
	[Name] [varchar](80) NOT NULL,
	[Note] [varchar](255) NULL,
 CONSTRAINT [PK_ZhiJi__10] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--------- 下面是查询语句 -----------------
SELECT sLock=(Case when isnull(Q.ifLock,0)=1 then '*' else '' end),
     Q.*,  Code=E.Code,Name=E.Name,EName=E.EName,CardNo=E.CardNo,Dept=E.Dept
    ,ZhiWu=E.ZhiWu,sZhiWu=ZW.Name,ZhiJi=E.ZhiJi,sZhiJi=Zj.Name
    ,PyDate=E.PyDate,sSex =(CASE Sex WHEN 0 THEN '男' ELSE '女' END)
    ,DeptName =D.LongName,BanZhiName =B.Name
FROM Kq_Result Q INNER JOIN ZlEmployee E ON Q.EmpID = E.ID
  INNER JOIN ZlDept D ON E.Dept = D.Code
  LEFT JOIN Kq_BanZhi B ON Q.BanZhi = B.Code
  LEFT JOIN E_ZhiWu ZW ON E.ZhiWu=ZW.Code
  LEFT JOIN E_ZhiJi Zj ON E.ZhiJi = Zj.Code
WHERE (Q.FDate BETWEEN '2017-12-01' AND '2017-12-30') AND (Dept LIKE '%') AND Q.EmpID =14418 
ORDER BY Q.FDate,Q.Code
OwenZeng_DBA 2017-12-30
  • 打赏
  • 举报
 回复
你把兼容级别设置到2008的兼容级别试试
Hello World, 2017-12-30
  • 打赏
  • 举报
 回复
引用 1 楼 yenange 的回复:
你还是没有说清楚， 给出一个能实际在SQL Server2008运行, 而SQL Server2014中不能运行的脚本出来再说吧 注意，可以简洁， 但得是完整脚本，有表结构有数据， 不要随便取一段出来。
抱歉，上面那个kq_result的没有code,是后面加上去的原来的表结构是： 
USE [HR]
GO

/****** Object:  Table [dbo].[Kq_result]    Script Date: 2017-12-30 17:53:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Kq_result](
	[FDate] [datetime] NOT NULL,
	[EmpID] [int] NOT NULL,
	[BanZhi] [char](4) NOT NULL,
	[ChiDaoSj] [int] NULL,
	[ChiDaoCs] [int] NULL,
	[ZaoTuiSj] [int] NULL,
	[ZaoTuiCs] [int] NULL,
	[KgSj] [int] NULL,
	[KgCs] [int] NULL,
	[Rcjb1] [int] NULL,
	[Rcjb2] [int] NULL,
	[RcjbOver0] [int] NULL,
	[RcjbAll] [int] NULL,
	[YcqSj] [int] NULL,
	[SjcqSj] [int] NULL,
	[B1SbTime] [int] NULL,
	[B1XbTime] [int] NULL,
	[B2SbTime] [int] NULL,
	[B2XbTime] [int] NULL,
	[B3SbTime] [int] NULL,
	[B3XbTime] [int] NULL,
	[B4SbTime] [int] NULL,
	[B4XbTime] [int] NULL,
	[FjbcName11] [varchar](16) NULL,
	[YB11SbTime] [int] NULL,
	[YB11XbTime] [int] NULL,
	[B11SbTime] [int] NULL,
	[B11XbTime] [int] NULL,
	[FjbcName12] [varchar](16) NULL,
	[YB12SbTime] [int] NULL,
	[YB12XbTime] [int] NULL,
	[B12SbTime] [int] NULL,
	[B12XbTime] [int] NULL,
	[KgSj_cd] [int] NULL,
	[KgCs_cd] [int] NULL,
	[KgSj_zt] [int] NULL,
	[KgCs_zt] [int] NULL,
	[JbKgSj] [int] NULL,
	[QkID] [int] NULL,
	[IfLock] [bit] NULL,
	[NotTqDK] [int] NULL,
	[NotThDK] [int] NULL,
	[B1ChiDaoSj] [int] NULL,
	[B2ChiDaoSj] [int] NULL,
	[B3ChiDaoSj] [int] NULL,
	[B4ChiDaoSj] [int] NULL,
	[B1ZaoTuiSj] [int] NULL,
	[B2ZaoTuiSj] [int] NULL,
	[B3ZaoTuiSj] [int] NULL,
	[B4ZaoTuiSj] [int] NULL,
	[Tiaoxiu] [bit] NULL,
	[Dttype] [varchar](20) NULL,
	[G_gs] [numeric](12, 2) NULL,
	[G_gx] [numeric](12, 2) NULL,
	[G_sj] [numeric](12, 2) NULL,
	[G_wxfj] [numeric](12, 2) NULL,
	[G_sfzmhb] [numeric](12, 0) NULL,
	[G_sfhb] [numeric](12, 0) NULL,
	[G_srjbxs] [numeric](12, 2) NULL,
	[G_bzqj] [numeric](12, 1) NULL,
	[G_ckkk] [numeric](12, 0) NULL,
	[G_cqsj] [numeric](12, 2) NULL,
	[G_cqts] [numeric](12, 2) NULL,
	[G_cqxs] [numeric](12, 2) NULL,
	[G_czks] [numeric](12, 2) NULL,
	[G_gsxs] [numeric](12, 2) NULL,
	[G_gxxs] [numeric](12, 2) NULL,
	[G_ifyb] [numeric](12, 0) NULL,
	[G_jrjbxs] [numeric](12, 2) NULL,
	[G_kgts] [numeric](12, 2) NULL,
	[G_kgxs] [numeric](12, 2) NULL,
	[G_psjbxs] [numeric](12, 2) NULL,
	[G_qjxs] [numeric](12, 2) NULL,
	[G_qkcs] [numeric](12, 0) NULL,
	[G_sjxs] [numeric](12, 2) NULL,
	[G_txbt] [numeric](12, 1) NULL,
	[G_type] [numeric](12, 0) NULL,
	[G_wqqj] [numeric](12, 0) NULL,
	[G_wxfjxs] [numeric](12, 2) NULL,
	[G_ycqsj] [numeric](12, 2) NULL,
	[G_ycqxs] [numeric](12, 2) NULL,
	[G_zmjbxs] [numeric](12, 2) NULL,
	[G_zmwjb] [numeric](12, 2) NULL,
	[G_nianj] [numeric](12, 2) NULL,
	[G_baiban] [numeric](12, 2) NULL,
	[G_yeban] [numeric](12, 2) NULL,
	[G_baban2] [numeric](12, 2) NULL,
	[G_yeban2] [numeric](12, 2) NULL,
	[G_baban3] [numeric](12, 2) NULL,
	[G_yeban3] [numeric](12, 2) NULL,
	[G_bzbxs] [numeric](12, 2) NULL,
	[G_yzbxs] [numeric](12, 2) NULL,
	[G_kq] [numeric](12, 0) NULL,
	[G_over0] [numeric](12, 2) NULL,
	[G_jrjb1] [numeric](12, 2) NULL,
	[G_jrjb2] [numeric](12, 2) NULL,
	[G_jrzbxs] [numeric](12, 2) NULL,
	[G_yxjss] [numeric](18, 2) NULL,
	[G_ybbz] [numeric](18, 2) NULL,
	[G_ycqts] [numeric](18, 2) NULL,
	[G_fangj] [numeric](12, 2) NULL,
	[G_njss] [numeric](12, 2) NULL,
	[G_yxfdj] [numeric](12, 2) NULL,
	[G_hunj] [numeric](12, 0) NULL,
	[G_chanj] [numeric](12, 0) NULL,
	[G_bjb] [numeric](12, 2) NULL,
	[Chidaosj0] [int] NULL,
	[Chidaosj1] [int] NULL,
	[Zaotuisj0] [int] NULL,
	[Zaotuisj1] [int] NULL,
	[G_cjxs] [numeric](12, 0) NULL,
	[G_yxfj] [numeric](12, 0) NULL,
	[G_hjxs] [numeric](12, 2) NULL,
	[G_zero0] [numeric](12, 2) NULL,
	[G_bj] [numeric](12, 0) NULL,
	[G_bjxs] [numeric](12, 0) NULL,
	[G_pchanj] [numeric](12, 0) NULL,
	[G_pcjxs] [numeric](12, 0) NULL,
	[G_sangj] [numeric](12, 0) NULL,
	[G_sanjxs] [numeric](12, 2) NULL,
	[G_kg] [numeric](12, 0) NULL,
 CONSTRAINT [PK_Kq_result] PRIMARY KEY CLUSTERED 
(
	[FDate] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
Hello World, 2017-12-30
  • 打赏
  • 举报
 回复
引用 1 楼 yenange 的回复:
你还是没有说清楚， 给出一个能实际在SQL Server2008运行, 而SQL Server2014中不能运行的脚本出来再说吧 注意，可以简洁， 但得是完整脚本，有表结构有数据， 不要随便取一段出来。
查询语句： 
SELECT sLock=(Case when isnull(Q.ifLock,0)=1 then '*' else '' end),
     Q.*,  Code=E.Code,Name=E.Name,EName=E.EName,CardNo=E.CardNo,Dept=E.Dept
    ,ZhiWu=E.ZhiWu,sZhiWu=ZW.Name,ZhiJi=E.ZhiJi,sZhiJi=Zj.Name
    ,PyDate=E.PyDate,sSex =(CASE Sex WHEN 0 THEN '男' ELSE '女' END)
    ,DeptName =D.LongName,BanZhiName =B.Name
FROM Kq_Result Q INNER JOIN ZlEmployee E ON Q.EmpID = E.ID
  INNER JOIN ZlDept D ON E.Dept = D.Code
  LEFT JOIN Kq_BanZhi B ON Q.BanZhi = B.Code
  LEFT JOIN E_ZhiWu ZW ON E.ZhiWu=ZW.Code
  LEFT JOIN E_ZhiJi Zj ON E.ZhiJi = Zj.Code
WHERE (Q.FDate BETWEEN '2017-12-01' AND '2017-12-30') AND (Dept LIKE '%') AND Q.EmpID =14418 
ORDER BY Q.FDate,Q.Code
表结构： 
USE [HR]
GO

/****** Object:  Table [dbo].[Kq_result]    Script Date: 2017-12-30 17:46:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Kq_result](
	[FDate] [datetime] NOT NULL,
	[EmpID] [int] NOT NULL,
	[BanZhi] [char](4) NOT NULL,
	[ChiDaoSj] [int] NULL,
	[ChiDaoCs] [int] NULL,
	[ZaoTuiSj] [int] NULL,
	[ZaoTuiCs] [int] NULL,
	[KgSj] [int] NULL,
	[KgCs] [int] NULL,
	[Rcjb1] [int] NULL,
	[Rcjb2] [int] NULL,
	[RcjbOver0] [int] NULL,
	[RcjbAll] [int] NULL,
	[YcqSj] [int] NULL,
	[SjcqSj] [int] NULL,
	[B1SbTime] [int] NULL,
	[B1XbTime] [int] NULL,
	[B2SbTime] [int] NULL,
	[B2XbTime] [int] NULL,
	[B3SbTime] [int] NULL,
	[B3XbTime] [int] NULL,
	[B4SbTime] [int] NULL,
	[B4XbTime] [int] NULL,
	[FjbcName11] [varchar](16) NULL,
	[YB11SbTime] [int] NULL,
	[YB11XbTime] [int] NULL,
	[B11SbTime] [int] NULL,
	[B11XbTime] [int] NULL,
	[FjbcName12] [varchar](16) NULL,
	[YB12SbTime] [int] NULL,
	[YB12XbTime] [int] NULL,
	[B12SbTime] [int] NULL,
	[B12XbTime] [int] NULL,
	[KgSj_cd] [int] NULL,
	[KgCs_cd] [int] NULL,
	[KgSj_zt] [int] NULL,
	[KgCs_zt] [int] NULL,
	[JbKgSj] [int] NULL,
	[QkID] [int] NULL,
	[IfLock] [bit] NULL,
	[NotTqDK] [int] NULL,
	[NotThDK] [int] NULL,
	[B1ChiDaoSj] [int] NULL,
	[B2ChiDaoSj] [int] NULL,
	[B3ChiDaoSj] [int] NULL,
	[B4ChiDaoSj] [int] NULL,
	[B1ZaoTuiSj] [int] NULL,
	[B2ZaoTuiSj] [int] NULL,
	[B3ZaoTuiSj] [int] NULL,
	[B4ZaoTuiSj] [int] NULL,
	[Tiaoxiu] [bit] NULL,
	[Dttype] [varchar](20) NULL,
	[G_gs] [numeric](12, 2) NULL,
	[G_gx] [numeric](12, 2) NULL,
	[G_sj] [numeric](12, 2) NULL,
	[G_wxfj] [numeric](12, 2) NULL,
	[G_sfzmhb] [numeric](12, 0) NULL,
	[G_sfhb] [numeric](12, 0) NULL,
	[G_srjbxs] [numeric](12, 2) NULL,
	[G_bzqj] [numeric](12, 1) NULL,
	[G_ckkk] [numeric](12, 0) NULL,
	[G_cqsj] [numeric](12, 2) NULL,
	[G_cqts] [numeric](12, 2) NULL,
	[G_cqxs] [numeric](12, 2) NULL,
	[G_czks] [numeric](12, 2) NULL,
	[G_gsxs] [numeric](12, 2) NULL,
	[G_gxxs] [numeric](12, 2) NULL,
	[G_ifyb] [numeric](12, 0) NULL,
	[G_jrjbxs] [numeric](12, 2) NULL,
	[G_kgts] [numeric](12, 2) NULL,
	[G_kgxs] [numeric](12, 2) NULL,
	[G_psjbxs] [numeric](12, 2) NULL,
	[G_qjxs] [numeric](12, 2) NULL,
	[G_qkcs] [numeric](12, 0) NULL,
	[G_sjxs] [numeric](12, 2) NULL,
	[G_txbt] [numeric](12, 1) NULL,
	[G_type] [numeric](12, 0) NULL,
	[G_wqqj] [numeric](12, 0) NULL,
	[G_wxfjxs] [numeric](12, 2) NULL,
	[G_ycqsj] [numeric](12, 2) NULL,
	[G_ycqxs] [numeric](12, 2) NULL,
	[G_zmjbxs] [numeric](12, 2) NULL,
	[G_zmwjb] [numeric](12, 2) NULL,
	[G_nianj] [numeric](12, 2) NULL,
	[G_baiban] [numeric](12, 2) NULL,
	[G_yeban] [numeric](12, 2) NULL,
	[G_baban2] [numeric](12, 2) NULL,
	[G_yeban2] [numeric](12, 2) NULL,
	[G_baban3] [numeric](12, 2) NULL,
	[G_yeban3] [numeric](12, 2) NULL,
	[G_bzbxs] [numeric](12, 2) NULL,
	[G_yzbxs] [numeric](12, 2) NULL,
	[G_kq] [numeric](12, 0) NULL,
	[G_over0] [numeric](12, 2) NULL,
	[G_jrjb1] [numeric](12, 2) NULL,
	[G_jrjb2] [numeric](12, 2) NULL,
	[G_jrzbxs] [numeric](12, 2) NULL,
	[G_yxjss] [numeric](18, 2) NULL,
	[G_ybbz] [numeric](18, 2) NULL,
	[G_ycqts] [numeric](18, 2) NULL,
	[G_fangj] [numeric](12, 2) NULL,
	[G_njss] [numeric](12, 2) NULL,
	[G_yxfdj] [numeric](12, 2) NULL,
	[G_hunj] [numeric](12, 0) NULL,
	[G_chanj] [numeric](12, 0) NULL,
	[G_bjb] [numeric](12, 2) NULL,
	[Chidaosj0] [int] NULL,
	[Chidaosj1] [int] NULL,
	[Zaotuisj0] [int] NULL,
	[Zaotuisj1] [int] NULL,
	[G_cjxs] [numeric](12, 0) NULL,
	[G_yxfj] [numeric](12, 0) NULL,
	[G_hjxs] [numeric](12, 2) NULL,
	[G_zero0] [numeric](12, 2) NULL,
	[G_bj] [numeric](12, 0) NULL,
	[G_bjxs] [numeric](12, 0) NULL,
	[G_pchanj] [numeric](12, 0) NULL,
	[G_pcjxs] [numeric](12, 0) NULL,
	[G_sangj] [numeric](12, 0) NULL,
	[G_sanjxs] [numeric](12, 2) NULL,
	[G_kg] [numeric](12, 0) NULL,
	[code] [nchar](10) NULL,
 CONSTRAINT [PK_Kq_result] PRIMARY KEY CLUSTERED 
(
	[FDate] ASC,
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [HR]
GO

/****** Object:  Table [dbo].[ZlEmployee]    Script Date: 2017-12-30 17:47:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ZlEmployee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Dept] [varchar](24) NOT NULL,
	[Code] [char](12) NOT NULL,
	[CardNo] [char](12) NOT NULL,
	[Name] [char](20) NOT NULL,
	[EName] [varchar](40) NULL,
	[Sfz] [char](18) NOT NULL,
	[BornDate] [datetime] NOT NULL,
	[Sex] [bit] NOT NULL,
	[PyDate] [datetime] NOT NULL,
	[SyMonths] [int] NULL,
	[ZzDate] [datetime] NULL,
	[RoomBed] [varchar](12) NULL,
	[IfDaKa] [bit] NOT NULL,
	[State] [tinyint] NOT NULL,
	[LzDate] [datetime] NULL,
	[LzCause] [char](2) NULL,
	[Memo] [varchar](60) NULL,
	[Photo] [image] NULL,
	[ZhiJi] [char](6) NULL,
	[ZhiWu] [char](6) NULL,
	[XueLi] [char](6) NULL,
	[HunYin] [char](6) NULL,
	[JiGuan] [varchar](6) NULL,
	[Nation] [char](6) NULL,
	[Jxfs] [char](6) NULL,
	[LzTc] [int] NULL,
	[GongZhong] [char](6) NULL,
	[DeBz] [char](4) NULL,
	[cy] [bit] NOT NULL,
	[Mark] [bit] NULL,
	[oldroombed] [varchar](10) NULL,
	[TcDate] [datetime] NULL,
	[Rzdate] [datetime] NULL,
	[emppassword] [varchar](10) NULL,
	[CostCenter] [varchar](24) NULL,
	[G_htjj] [numeric](12, 2) NULL,
	[G_bxf] [numeric](12, 2) NULL,
	[G_ifdc] [numeric](12, 2) NULL,
	[G_xxts] [numeric](12, 0) NULL,
	[G_yljj] [numeric](12, 0) NULL,
	[G_bory] [varchar](2) NULL,
	[address] [varchar](100) NULL,
	[sfzdate0] [datetime] NULL,
	[sfzdate1] [datetime] NULL,
	[G_htsx] [numeric](12, 2) NULL,
	[G_htxx] [numeric](12, 2) NULL,
	[G_hts] [numeric](12, 2) NULL,
	[G_notb] [numeric](12, 0) NULL,
	[lzreason] [varchar](20) NULL,
	[G_jtzz] [varchar](80) NULL,
	[G_notd] [numeric](12, 2) NULL,
	[G_date0] [datetime] NULL,
	[G_date1] [datetime] NULL,
	[G_sfzd0] [datetime] NULL,
	[G_sfzd1] [datetime] NULL,
	[G_sfzdate0] [datetime] NULL,
	[G_sfzdate1] [datetime] NULL,
 CONSTRAINT [PK_ZlEmployee] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

USE [HR]
GO

/****** Object:  Table [dbo].[ZlDept]    Script Date: 2017-12-30 17:47:30 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ZlDept](
	[Code] [varchar](24) NOT NULL,
	[Name] [varchar](40) NOT NULL,
	[TreeLevel] [tinyint] NOT NULL,
	[CurCode] [varchar](20) NULL,
	[UpCode] [varchar](20) NULL,
	[LongName] [varchar](70) NULL,
	[Manager] [varchar](10) NULL,
	[Tel] [varchar](18) NULL,
	[Note] [varchar](40) NULL,
	[BianZhiRs] [int] NULL,
	[hSq] [int] NULL,
	[ifyx] [bit] NULL,
	[deptdesc] [varchar](18) NULL,
 CONSTRAINT [PK_ZlDept] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [HR]
GO

/****** Object:  Table [dbo].[Kq_BanZhi]    Script Date: 2017-12-30 17:47:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Kq_BanZhi](
	[Code] [char](4) NOT NULL,
	[Name] [varchar](16) NOT NULL,
	[Zsj] [int] NULL,
	[BanCiCount] [tinyint] NULL,
	[IfFdJcsj] [bit] NOT NULL,
	[IfFdjb] [bit] NULL,
	[CanZhi] [char](5) NULL,
	[IfDLJCDZT] [bit] NULL,
	[Note] [char](60) NULL,
	[IfBz] [bit] NULL,
	[Mark] [varchar](10) NULL,
 CONSTRAINT [PK_Kq_BanZhi] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [HR]
GO

/****** Object:  Table [dbo].[E_ZhiWu]    Script Date: 2017-12-30 17:48:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[E_ZhiWu](
	[Code] [char](10) NOT NULL,
	[Name] [char](80) NULL,
	[ZhiwuJt] [numeric](12, 2) NULL,
	[Note] [varchar](255) NULL
) ON [PRIMARY]

GO

USE [HR]
GO

/****** Object:  Table [dbo].[E_ZhiJi]    Script Date: 2017-12-30 17:48:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[E_ZhiJi](
	[Code] [char](6) NOT NULL,
	[Name] [varchar](80) NOT NULL,
	[Note] [varchar](255) NULL,
 CONSTRAINT [PK_ZhiJi__10] PRIMARY KEY CLUSTERED 
(
	[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
吉普赛的歌 2017-12-30
  • 打赏
  • 举报
 回复
你还是没有说清楚， 给出一个能实际在SQL Server2008运行, 而SQL Server2014中不能运行的脚本出来再说吧 注意，可以简洁， 但得是完整脚本，有表结构有数据， 不要随便取一段出来。
高阶SQL语句（排序，分组，限制，别名，通配符，子查询，视图，null值）
使用 select 语句可以将需要的数据从 MySQL 数据库中查询出来，如果对查询的结果进行排序，可以使用 order by 语句来完成排序，并最终将排序后的结果返回给用户。这个语句的排序不光可以针对某一个字段，也可以针对...
MySQl排序，别名
ASC：是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC方式进行排序 DESC：是按降序方式进 行排列。当然 ORDER BY 前面也可以使用 WHERE 子句对...
SQL数据查询——单表查询和排序
从选定的表中取出一个元组，取出该元组在选定的属性上的值，形成一个新的元组作为输出。两个本来并不完全相同的元组，投影到指定的某些列上之后，可能变成相同的行了，可以通过DISTINCT来取消重复的行。用户也可以...
sql中的排序分页与多表查询
详细介绍sql中的排序与分页和多表查询的各种情况方式
sql server语法——多表查询(连接查询)
一、含义 当查询中涉及到了多个表的字段，需要使用多表查询 select 字段1，字段2，…… from 表1，表2，…… 二、分类 按年代分 sql192： 等值连接 非等值连接 ...sql199【推荐使用】 ...from 表1 别名，表2 别名 w
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章