1、在查询结果中添加一行数据

    1. select * from TPRG_DemandFrom_ZD
    2. union all
    3. select '0','全部',1,'全部'

    2、left join 与查看空值转换；
    ISNULL 使用指定的替换值替换 NULL。
    语法：ISNULL ( check_expression , replacement_value ) 

    1. select A.id,A.ProgressName, ISNULL(B.ProgressCount,0) 
    2. from TPRG_DemandProgress_ZD A 
    3. left join 
    4. (select count(*) ProgressCount,Progress　
    5. from demand group by Progress) B 
    6. on A.id = B.Progress

    3、计算百分比
    select CONVERT(decimal(18, 1),除数 1.0 / 被除数) 100 a from 表名
    decimal(18, 1) 代表取一位小数

    1. declare @allcout char(50)
    2. set @allcout = (select count(*) from demand where state=1)
    3. select @allcout
    4. select count(*) fromBfb,B.fromName　from demand A,TPRG_DemandFrom_ZD B where A.demandFrom= B.id and A.state =1 group by fromName
    6. select  CONVERT(decimal(18, 1),count(*)  * 1.0 /@allcout) * 100  fromBfb,B.fromName　from demand A,TPRG_DemandFrom_ZD B where A.demandFrom= B.id and A.state =1 group by fromName

    4、数据趋势统计

    1. select COUNT(*),A.times from (
    2. select CONVERT(varchar(100),createTime, 23) times  from demand ) A
    3. group by A.times

    5、数据表结构备份与数据拷贝

    1. --表结构数据备份
    2. select * into StandingBooks_backup_20220609
    3. from  [dbo].[StandingBooks]
    5. --将一张表的数据插入另一张表
    6. insert into [dbo].[StandingBooks]
    7. select *,'' from StandingBooks_backup_20220609