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)