日常工作SQL积累

将一张表数据更新到另外一张表

• 场景说明：

update tbmk_gzp_detail
set tbmk_gzp_detail.f_bmbm = tbsys_bm.f_bmbm
from tbmk_gzp_detail
    left join tbsys_bm on tbsys_bm.f_bmmc = tbmk_gzp_detail.f_bmbm
where tbsys_bm.f_bmbm<>''

对查询结果的某列的值进行去重。

• 场景说明：

SELECT f_khmc, f_zdrq
FROM (
	SELECT ROW_NUMBER() OVER (PARTITION BY f_khmc ORDER BY f_zdrq DESC) AS id,*
	FROM tbmk_ddzb
) n
WHERE n.id = 1
	AND datediff(day, f_zdrq, getdate()) <= 20
ORDER BY f_zdrq DESC

字符串类型数字进行排序

• 场景说明：
• 方法一 加上一个数字，猜测是内部转换为数字。所以可以进行正常数字比较。

select 
  f_Sybm, f_Symc, f_zdrq, f_zdrbm, f_zdrmc, n.f_bmbm, 
  ISNULL(bm.f_bmmc, '') as f_bmmc 
from 
  Deltail_Sy n 
  left join tbsys_bm bm on bm.f_bmbm = n.f_bmbm 
order by 
  f_Sybm+0

• 方法二 先按照字符串长度排序，再按照字符串排序。

select 
  f_Sybm, f_Symc, f_zdrq, f_zdrbm, f_zdrmc, n.f_bmbm, 
  ISNULL(bm.f_bmmc, '') as f_bmmc 
from 
  Deltail_Sy n 
  left join tbsys_bm bm on bm.f_bmbm = n.f_bmbm 
order by 
  LEN(f_Sybm),f_Sybm

循环为一个员工授予权限

• 场景说明：

DECLARE @id INT
DECLARE @gzfl INT=0
SELECT @id = ISNULL( MAX(ISNULL(ID, 0)),0)+1 FROM tbsys_gzsqgl
WHILE @gzfl<10
BEGIN
IF exists(SELECT f_sqgzfl FROM tbsys_gzsqgl WHERE f_sqgzfl= @gzfl)
BEGIN 
DELETE FROM tbsys_gzsqgl WHERE f_sqgzfl=@gzfl AND f_ygbm='0000'
END
INSERT INTO tbsys_gzsqgl (
        ID,
        f_ygbm,
        f_ygmc,
        f_sqgzfl,
        f_View_Author,
        f_Edit_Author
    )
VALUES(@id,'0000','管理员',@gzfl,1,1)
SET @gzfl=@gzfl+1
SET @id=@id+1
END
SELECT * FROM tbsys_gzsqgl

在权限表查询 当前操作员是否任何一级的查看权限

• 场景说明：

	SELECT COUNT(ID) AS View_Author
	FROM tbsys_gzsqgl
	WHERE f_ygbm = '0000' AND f_View_Author=1

查询用户创建的表名

• 场景说明：

SELECT TABLE_NAME
FROM 数据库名称.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

ORDER BY 自定义排序

• 场景说明：在一个客户档案中，存有不同长度的客户编码，例如9位、10、7位，不管按照逆序还是正序都无法得到我们想要的结果，默认可能是优先排长度为10的或者长度为9的或者是7的，这样就无法得到我们想要结果。
• 这个时候我们为每种长度指定为数字的级别，以此依据来进行排序后就可以得到优先按照字符长度再按照想要的排序规则来进行排序。

SELECT * FROM tbda_kh
ORDER BY case
        LEN(f_khbm)
        when 9 then 1
        when 10 then 2
        when 7 then 3
        when 4 then 4
        when 11 then 5
        when 8 then 6
    END ASC,
    f_khbm

为某个字符串的时间加上一天

-- mssql
DATEADD(DAY,1, CONVERT(datetime,@f_jsrq,20)) +' 23:59:59'

TRUNCATE TABLE语句解析

与 DELETE 语句相比，TRUNCATE TABLE 具有以下优点：

所用的事务日志空间较少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一个项。 TRUNCATE TABLE 通过释放用于存储表数据的数据页删除数据，且仅在事务日志中记录页释放。

使用的锁通常较少。

当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。 TRUNCATE TABLE 始终锁定表（包含架构 (SCH-M) 锁）和页，而不是锁定各行。

如无例外，在表中不会留有任何页。

执行 DELETE 语句后，表仍会包含空页。 例如，必须至少使用一个排他 (LCK_M_X) 表锁，才能释放堆中的空表。 如果执行删除操作时没有使用表锁，表（堆）中将包含许多空页。 对于索引，删除操作会留下一些空页，尽管这些页会通过后台清除进程迅速释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。 若要删除表定义及其数据，请使用 DROP TABLE 语句。

如果表包含标识列，该列的计数器重置为该列定义的种子值。 如果未定义种子，则使用默认值 1。 若要保留标识计数器，请使用 DELETE。

• 以上内容摘自微软官网.微软官方描述

查询数据中某张表/全部表的列名

SELECT COLUMN_NAME FROM information_schema.COLUMNS
WHERE TABLE_CATALOG   = 'database_name' AND TABLE_NAME = 'table_name';