飞讯常用SQL语句

git拉取代码并覆盖

git fetch --all
git reset --hard master 
git pull

发送邮件

EXEC ba_sendnotice 'ADA202003110001','[keyvalue]','[userno]'

查看用户

SELECT * FROM oa_vi_erpUser

修改表所属模块

SELECT * FROM dbo.xt_selftable WHERE tablename='bs_message'
UPDATE xt_selftable SET mkno='60' WHERE tablename='bs_message'

默认日期(本月)

SELECT CONVERT(DATETIME,CONVERT(CHAR(8),GETDATE(),23)+'1')
SELECT DATEADD(d,-DAY(GETDATE()),DATEADD(m,1,GETDATE()))

审核信息

ba_billchkinfo

SELECT A.opeid, A.billno, A.useflg, A.sendbatch, A.chkorder, A.sender,
       (A4.empname) AS sendername, A.senddate, A.accepter,
       (A3.empname) AS acceptername, A.chkdate, A.chkstatus, A.chkcontext,
       A.isproxy, A.proxyno, (A2.empname) AS proxyname, A.lastchkflg,
       A.oachkflg, A.unchktxt, A.unchkdt, A.unchkempno,
       (A6.empname) AS unchkempname, B.chkitmmemo, B.chktype
FROM ba_ChkInfo A WITH(NOLOCK)
LEFT OUTER JOIN ba_employeeinfo A2 WITH(NOLOCK)ON A2.empno=A.proxyno
LEFT OUTER JOIN ba_employeeinfo A3 WITH(NOLOCK)ON A3.empno=A.accepter
LEFT OUTER JOIN ba_employeeinfo A4 WITH(NOLOCK)ON A4.empno=A.sender
LEFT OUTER JOIN xt_SysModules A5 WITH(NOLOCK)ON A5.opeid=A.opeid
LEFT OUTER JOIN ba_employeeinfo A6 WITH(NOLOCK)ON A6.empno=A.unchkempno
LEFT JOIN dbo.ba_ChkFlowItms B ON B.opeid=A.opeid AND B.iautoid=A.chkitmid
WHERE A.opeid='3000034' AND A.billno='[keyvalue]'

用户下的业务显示界面设置记录

SELECT * FROM user_customerColumnMsg
WHERE opeid=''

查询数据表名

select * from xt_selftable
WHERE tablecaption LIKE '%%'

操作业务后调用存储过程
审核后调用存储过程 oa_pr_ppssetchkstates
送审后发送消息、邮件 SPT OA_GETEMIAL
去除小数点

ClearZero()

序号自增

ROW_NUMBER() OVER (ORDER BY a.iid)

获取中文拼音首字
fn_GetSpell
临时供应商数据

SELECT  CASE A.gyszt
          WHEN 2 THEN '合格供应商'
          WHEN 3 THEN '优选供应商'
          WHEN 4 THEN '战略供应商'
        END AS gyszt, A.custno, A.custname, A.address, B.linkphone, B.mobile, B.email
FROM    dbo.sm_t_jbxx A
LEFT JOIN dbo.ss_t_users B ON B.custno = A.custno
WHERE   A.gyszt = 2

网格字段自定义颜色

<font style="color:red;font-weight:bold;">议价说明</font>

创建事务、获取新单据号、跳转页面链接

DECLARE @error INT
DECLARE @newno VARCHAR(50)
DECLARE @errflg INT
SET @error = 0;
BEGIN TRANSACTION;
--事务开始
SET @error = @error + @@ERROR;
--提交事务
IF @error > 0 BEGIN
    EXEC fn_pr_getNewcode 1, 3000062, 0, '', @newbillno OUTPUT, @errflg OUTPUT
    SELECT  '../Mygrid/FormEditNew?opeid=3000062&billno=' + @newno + '&stype=1' 
    ROLLBACK;
END;
ELSE
BEGIN
    COMMIT;
END;

SQL 查看死锁，关闭死锁

--查询死锁
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'
--杀死死锁
kill 62

创建临时表

IF (OBJECT_ID('tempdb.dbo.#table') IS NOT NULL)
    DROP TABLE #table
CREATE TABLE #table
    (channelid VARCHAR(50),
     pmoney DECIMAL(18, 2),
     ptype TINYINT,
     inday VARCHAR(10),
     scale VARCHAR(10),
     comScale VARCHAR(10))
--释放临时表
IF (OBJECT_ID('tempdb.dbo.#table') IS NOT NULL)
    DROP TABLE #table

获取表结构

SELECT  COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = 'pu_beliverybarcode'

查询数据库操作日志

SELECT  loginname, loginsid, spid, hostname, applicationname,
               servername, databasename, objectname, e.category_id,
               cat.name AS [CategoryName], textdata, starttime, eventclass,
               eventsubclass, --0表示begin，1表示commit
               e.name AS EventName
FROM::fn_trace_gettable('E:\SQLDATA\MSSQL\LOG\log_25.trc', 0)
INNER JOIN sys.trace_events e ON eventclass=trace_event_id
INNER JOIN sys.trace_categories AS cat ON e.category_id=cat.category_id
WHERE e.name='Object:Deleted' AND objectname LIKE '%3000016%'
ORDER BY starttime DESC

飞讯常用SQL语句

推荐阅读更多精彩内容