SQL Tips --收集常用的SQL

--从人事库中查出在职的,薪卡号重复的记录

select FGongKaHao from bas_Employee

   where  FZaiZhi=0

   group by FGongKaHao having count(FGongKaHao)>1

--找出进仓从表的出仓数量和出仓记录中的出仓数量总合不一样的记录

SELECT * FROM

(

    select

       ISNULL(

         (

            --计算出仓记录里的出仓数量总合

            SELECT SUM(FShuLiang)

               FROM sto_ChuCangJL f_ccjl

               WHERE f_d.FKey=f_ccjl.FJinCangKey

            )

           ,0) AS FChuCangSLEX, f_d.FChuCangSL AS FChuCangSL2, f_d.*

    FROM

      (

      select f_m.FFormDate, f_m.FBianHao,

             f_d.*

          from sto_inStore_d f_d

          INNER JOIN sto_InStore_m f_m on f_m.FFormKey = f_d.FFormKey

      )f_d

)__v

where FChuCangSL<>FChuCangSLEX

order by FBianHao

--进仓从表的出仓数量和出仓记录中的出仓数量总合不一样的记录,更新一至

UPDATE f_d

  SET f_d.FChuCangSL=__v.FChuCangSLEX

  FROM sto_inStore_d f_d

  INNER JOIN

    (

        select

           ISNULL(

             (

                --计算出仓记录里的出仓数量总合

                SELECT SUM(FShuLiang)

                   FROM sto_ChuCangJL f_ccjl

                   WHERE f_d.FKey=f_ccjl.FJinCangKey

                )

               ,0) AS FChuCangSLEX, f_d.FChuCangSL AS FChuCangSL2, f_d.*

        FROM

          (

          select f_m.FFormDate, f_m.FBianHao,

                 f_d.*

              from sto_inStore_d f_d

              INNER JOIN sto_InStore_m f_m on f_m.FFormKey = f_d.FFormKey

          )f_d

    )__v ON f_d.FKey=__v.FKey

   where __v.FChuCangSL<>__v.FChuCangSLEX

--使用联连的方式来更新一个表

--更新禁止使用的模块

UPDATE #tmpModules SET IsEnable=0

  FROM #tmpModules f_tb INNER JOIN sys_cm_ModuleFuncs f_slv ON f_tb.FModuleFuncIndex=f_slv.FIndex

  WHERE f_slv.FDisable=1

--更新班次

SELECT @ExeStr=

'UPDATE f_tb

   SET FBanCiBH=

        (SELECT ['+@SDay+'] FROM hrm_EmpBanCi

           WHERE FNianYue='''+@NianYue+''' AND FZhiYuanKey=f_tb.FZhiYuanKey)

   FROM hrm_EmpKaoQin f_tb

   WHERE  f_tb.FApproved<>1

          AND f_tb.FKaoQinRQ='''+cast(@mm_Date as varchar(24))+''''

EXEC (@ExeStr)