常用开发语言小技巧(一)



常用开发语言小技巧(一)  

2007-10-08 15:46:22|  分类： 开发语言小技巧 |  标签： |举报 |字号大中小 订阅

       

用微信  “扫一扫”

将文章分享到朋友圈。

 

用易信  “扫一扫”

将文章分享到朋友圈。

 

下载LOFTER客户端

　　一些常用的小技巧，以免自己不用会忘记。当作字典好了！

　　里面包括一些SQL语句和PB语法。

1、返回最后插入的标识值

　　@@IDENTITY
　　返回最后插入的标识值。自增字段。

　　下面的示例向带有标识列的表中插入一行，并用 @@IDENTITY 显示在新行中使用的标识值。

　　INSERT INTO jobs (job_desc,min_lvl,max_lvl)
　　VALUES ('Accountant',12,125)
　　SELECT @@IDENTITY AS 'Identity'

2、下例用 xxx 替换 abcdefghi 中的字符串 cde。
　　SELECT REPLACE('abcdefghicde','cde','xxx')

3、查询分析器带参数运行存储过程
　　exec udt_class_ufo_ckwkp_1[2000-01-01],[2004-5-1],[ select iid from u_dj_ckds]--,[@new_select output]

4、下面的示例查找包含词"bottles"且价格为 $15.00 的所有产品。

　　USE Northwind
　　GO
　　SELECT ProductName
　　FROM Products
　　WHERE UnitPrice = 15.00
   　　　AND CONTAINS(QuantityPerUnit, 'bottles')

5、PB中日期变量用一种用法

　　if GetColumnName() = "csys_bz" then //用在enter事件中
　　//------------
　　" u_dj_ht.dht_date >= CONVERT(DATETIME, '"+string(dw_ht.object.dht_date1[dw_ht.getrow()])+"')" 
　　**注意MSSQLSERVER函数convert用法，转换为日期型变量

6、自动计算出出库单对应通知单数量
　　select u_dj_ckds.iid,u_dj_ckds.ihts_id,u_dj_ckds.nsl,u_dj_ckds.mje ,t.nsl,t.mje,u_dj_ckds.nsl - t.nsl from u_dj_ckds inner join
　　(select ickds_id,sum(nsl) as nsl,sum(mje) mje from u_tzd_xxkps group by ickds_id) t on u_dj_ckds.iid = t.ickds_id;

7、用atediff计算入库开票天数
　　SELECT u_dj_jxfp.iid as jxfpid, u_dj_jxfps.iid as jxfpsid, DATEDIFF(day, u_dj_rkd.drkd_date, u_dj_jxfp.dinput_date) as rkdays, u_dj_jxfps.mje as jxfpje  FROM u_dj_jxfp,u_dj_jxfps, u_dj_rkd, u_dj_rkds WHERE ( u_dj_rkds.ihead_id = u_dj_rkd.iid ) and ( u_dj_jxfps.ihead_id = u_dj_jxfp.iid ) and ( u_dj_jxfps.irkds_id = u_dj_rkds.iid )   

8、统计指定客户回款数
　　select u_dj_hks.mje as hksje ,u_dj_hks.ixxfps_id as xxfpsid ,u_dj_hks.iid as hksid from u_dj_hks where ixxfps_id in(select iid from u_dj_xxfps where ihead_id in(select iid from u_dj_xxfp where iclient_id in(select ihead_id from u_xt_client_jc where iid = 2 )))

9、根据jxfp_id查询到入库开票天数
　　select sum(s.rkdays *(s.jxfpsje / a.jxfpje)) from (select sum(mje) as jxfpje,ihead_id as jxfpid from u_dj_jxfps group by ihead_id) a,(SELECT u_dj_jxfp.iid as jxfpid, u_dj_jxfps.iid as jxfpsid, DATEDIFF(day, u_dj_rkd.drkd_date, u_dj_jxfp.dinput_date) as rkdays, u_dj_jxfps.mje as jxfpsje  FROM u_dj_jxfp,u_dj_jxfps, u_dj_rkd, u_dj_rkds WHERE ( u_dj_rkds.ihead_id = u_dj_rkd.iid ) and ( u_dj_jxfps.ihead_id = u_dj_jxfp.iid ) and ( u_dj_jxfps.irkds_id = u_dj_rkds.iid )    ) s where a.jxfpid = s.jxfpid

10、统计出超出多少时间的出库数量
　　SELECT u_dj_hts.iid as htsid, u_dj_ckds.iid as ckdsid, DATEDIFF(day, u_dj_ckd.dckd_date, CONVERT(DATETIME, '2004/04/10')) as ckdays, u_dj_ckds.mje as jxfpje  FROM u_dj_ht,u_dj_hts, u_dj_ckd, u_dj_ckds WHERE ( u_dj_ckds.ihead_id = u_dj_ckd.iid ) and ( u_dj_hts.ihead_id = u_dj_ht.iid ) and ( u_dj_ckds.ihts_id = u_dj_hts.iid )  and DATEDIFF(day, u_dj_ckd.dckd_date, CONVERT(DATETIME, '2004/04/10')) > u_dj_ht.ibest_arrearage_day and u_dj_ckd.idatalx = 0 and u_dj_ckd.idatalxs = 0 and u_dj_ckd.iclient_id in(select ihead_id from u_xt_client_jc where iid = 2 )

11、判断权限语法的一种用途
　　select iczy_id from u_xt_czy where substring(cczy_qx,31,1) = '1'

12、得到列的显示值
　　string msg
　　msg = dw_data.Describe("Evaluate('LookUpDisplay(izb_id)',"+String(il_row)+")")
　　messagebox("",msg)

13、match用法
　　Match(GetText() , "^[A-Z]+$")
　　Match(GetText() , "^[0-9\-]+$")      '电话号码只能为 0-9 的数字或 - 组成！'
　　Match(GetText() , "^[0-9]+$")        '邮编只能为 0-9 的数字组成！'

14、PB中快速删除多行记录

　　准确的说是移动行，同理函数还有ROWSCOPY

　　dw_fdbl.RowsMove(1, dw_fdbl.RowCount(), Primary!, dw_fdbl, 1, Delete!)