SQL相关语句

SQL查询指定某月，变2021 与4 即可

select * from 表名 where DATEPART(YEAR,字段名) = 2021 and MONTH(字段名) = 4

eg:

select * from LR_DJ_PurchaseRequisition where DATEPART(YEAR,fcreatedate) = 2021 and MONTH(fcreatedate) = 4

 

--跨服务器SQL
（1）查询表示例
select * from openrowset ( 'SQLOLEDB' , '（服务器地址或者服务器名称）1.1.1.1' ; '（用户名）user' ; '（数据库密码）123' ,数据库名称.dbo.表名)
（2）插入示例
insert openrowset( 'SQLOLEDB' , '1.1.1.1' ; 'user' ; '123' ,MAKUKU_FAMILY_IND_LIVE.dbo.LR_Form_Scheme)
select * from LR_Form_Scheme
where F_SchemeInfoId in (
select F_Id from LR_Form_SchemeInfo where F_Name in ('Tax Expense Payment','TAX PPN Payment','SPF(PIB)','purchase invoice','purchase receipt')
)

 

--SQL语句分组获取记录的第一条数据的方法
按fid分组 按fid排序 取第一个

select FID,FNUMBER,FNAME from

(select a.FID,
a.FNUMBER,
b.FNAME,
ROW_NUMBER() over(partition by a.FID ORDER BY a.FID) as new_index
from T_BD_PaymentCondition a
left join T_BD_PAYMENTCONDITION_L b on a.FID=b.FID where FDOCUMENTSTATUS='C' and FFORBIDSTATUS='A' )a
where a.new_index=1