SQL源代碼

--Select * from  trans_staff Where id_no ='' Or id_no Is Null
--Select id_no from  trans_staff  Order By id_no

--Select staff_name,id_no from  trans_staff   Where IsNumeric(id_no)=0 And Right(id_no,1)<>'x'

--Select Distinct A.staff_no,A.staff_name,A.province,A.sex,A.birthday from trans_staff A
--Inner Join trans_staff B
--On A.staff_name=B.staff_name And A.province=B.province And A.sex=B.sex And A.staff_no<>B.staff_no And

A.birthday=A.birthday Where A.Status=0 And B.Status=0
--Order By A.staff_name, A.staff_no

--Select staff_no,staff_name,province,sex,birthday,in_date from trans_staff Where DateDiff(dd,in_date,GetDate())<=30

Order By In_date

--Select staff_no,staff_name,province,sex,birthday,in_date from trans_staff Where DateDiff(yy,birthday,GetDate())< =18

Or DateDiff(yy,birthday,GetDate())> =35 Order By birthday desc

--Select * from trans_staff Where DateDiff(yy,birthday,GetDate())< =18

USe InviteStaff
select A.staff_name as 姓名,staff_sex as 性別,staff_native as 籍貫,B.out_date  from Staff_Register A
Inner Join [it-srv-db1].fc_person_data.dbo.trans_staff B
On A.staff_identitycard=B.id_no
where A.staff_identitycard in (select id_no from [it-srv-db1].fc_person_data.dbo.trans_staff where id_no  is null Or 

datediff(dd,out_date,getdate())<30)

--Select TOP 10 from [it-srv-db1].fc_person_data.dbo.trans_staff B
--Select TOP 10 staff_identitycard from [it-srv-db1].fc_person_data.dbo.trans_staff B

select A.staff_name as 姓名,staff_sex as 性別,staff_native as 籍貫,B.out_date  from Staff_Register A
Inner Join [it-srv-db1].fc_person_data.dbo.trans_staff B
On A.staff_identitycard=B.id_no
where A.staff_identitycard in (select id_no from [it-srv-db1].fc_person_data.dbo.trans_staff where id_no  is null Or 

datediff(dd,out_date,getdate())<30)

select A.staff_name as 姓名,staff_sex as 性別,staff_native as 籍貫,B.out_date  from Staff_Register A
Inner Join [it-srv-db1].fc_person_data.dbo.trans_staff B
On A.staff_identitycard=B.id_no
And  (B.id_no  is null Or  datediff(dd,B.out_date,getdate())<30)

select A.staff_name as 姓名,staff_sex as 性別,staff_native as 籍貫 from Staff_Register A
where A.staff_identitycard in (select id_no from [it-srv-db1].fc_person_data.dbo.trans_staff where id_no  is null Or 

datediff(dd,out_date,getdate())<30)

select A.staff_name as 姓名,staff_sex as 性別,staff_native as 籍貫 from Staff_Register A
where A.staff_identitycard in (select staff_identitycard from [it-srv-db1].fc_person_data.dbo.trans_staff where id_no  is

null Or  datediff(dd,out_date,getdate())<30)

select A.staff_name,B.staff_name As introducer_name ,B.department_no,B.introducer_remark,B.in_date  from

Staff_Register A, (
select  department_no,staff_name,introducer_remark,in_date  from [it-srv-db1].fc_person_data.dbo.trans_staff where

datediff(dd,in_date,getdate())<=30  ) B
Where A.introducer_name=B.staff_name

select A.staff_name,B.staff_name As introducer_name ,B.department_no,B.introducer_remark,B.in_date  from

Staff_Register A, (
select  department_no,staff_name,introducer_remark,in_date  from [it-srv-db1].fc_person_data.dbo.trans_staff) B
Where A.introducer_name=B.staff_name

select  * from staff_register where staff_identitycard in
 (select id_no from [it-srv-db1].fc_person_data.dbo.trans_staff)

and (select  )
On A.staff_name=B.staff_name And A.province=B.province And A.sex=B.sex And A.staff_no<>B.staff_no And

A.birthday=A.birthday Where A.Status=0 And B.Status=0

--Update staff_register Set introducer_name='黃萍'  Where staff_identitycard='100111111111111111'
Select * from staff_register  Where staff_identitycard='100111111111111111'

黃萍

Select TOP 1 * from [it-srv-db1].fc_person_data.dbo.trans_staff
select staff_name,staff_sex, staff_identitycard  from Staff_Register where staff_identitycard in
 (select id_no from [it-srv-db1].fc_person_data.dbo.trans_staff where status>0)

select staff_name, staff_identitycard  from Staff_Register where staff_identitycard in (select id_no from [it-srv-

db1].fc_person_data.dbo.trans_staff where status>0)

 ( select  * from staff_register where staff_identitycard) select department_no as 介紹人部門,staff_name as 介紹人姓

名,introducer_remark as 介紹人關係 from [it-srv-db1].fc_person_data.dbo.trans_staff

select A.staff_name  from Staff_Register , (
select  department_no,staff_name,introducer_remark,in_date  from [it-srv-db1].fc_person_data.dbo.trans_staff where

datediff(dd,in_date,getdate())<=30  ) A

Select Name from SysColumns Where ID=OBJECT_ID('Staff_Register')

introducer_name

select *  from Staff_Register
A

 

 

--合格者
select * from Staff_Register where staff_identitycard

not in
(select staff_identitycard from Staff_Register where staff_identitycard in (select id_no from [it-srv-

db1].fc_person_data.dbo.trans_staff where status>0))

and  staff_identitycard not in
(select staff_identitycard from  [it-srv-db1].fc_person_data.dbo.trans_staff Where out_date Is Not Null  And out_date<>''

Or datediff(dd,in_date,getdate())<30)

and staff_identitycard not in
(select staff_identitycard
 from Staff_Register A, (select  department_no,staff_name,introducer_remark,in_date
  from [it-srv-db1].fc_person_data.dbo.trans_staff where datediff(dd,in_date,getdate())<30  ) B
 Where A.introducer_name=B.staff_name)

and staff_identitycard not in
(select staff_identitycard from Staff_Register where datediff(yy,staff_birthday,getdate())<18 Or datediff

(yy,staff_birthday,getdate())>35)

Use NorthWind
Select * from dbo.[Order Details]
Select * from dbo.Orders Where EmployeeID=1
Select * from pubs.dbo.authors
Select * from pubs..authors
Select A.au_id,A.contract,B.EmployeeID from pubs..authors A Inner Join Orders B On A.contract=B.EmployeeID

Select A.OrderID,ProductID from  dbo.[Order Details] A , dbo.Orders B Where A.OrderID=B.OrderID
Select A.OrderID,ProductID,A.Quantity,B.EmployeeID from  dbo.[Order Details]  A , dbo.Orders B Where

A.Quantity=B.EmployeeID
--Select A.au_id,A.contract,B.EmployeeID from pubs..authors  A , dbo.Orders B Where A.contract=B.EmployeeID
--Select A.OrderID,ProductID,A.Quantity,B.EmployeeID from  dbo.[Order Details]  A Inner Join dbo.Orders B On

A.Quantity=B.EmployeeID
--Select  dbo.[Order Details].OrderID,ProductID,A.Quantity,B.EmployeeID from  dbo.[Order Details]  A , dbo.Orders B

Where A.Quantity=B.EmployeeID

Select dbo.[Order Details].OrderID,Orders.ProductID,dbo.[Order Details].Quantity,Orders.EmployeeID from  dbo.[Order

Details] , dbo.Orders Where dbo.[Order Details].Quantity=Orders.EmployeeID

select  staff_name,staff_sex,staff_native,staff_birthday from staff_register where staff_identitycard in
 (select staff_name,sex,province,staff_birthday from [it-srv-db1].fc_person_data.dbo.trans_staff where

staff_identitycard=null)