数据库SQL及相关

//1、生成随机数
ROUND(((89 - 71 -1) * RAND() + 71), 0)
DECLARE @Result INT
DECLARE @Upper INT
DECLARE @Lower INT 
SET @Lower = 1 
SET @Upper = 10
SELECT @Result = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Result

//2、字段为空，输出默认值 
isnull(wj.wjsum,0) as wjsum//数字

isnull(wj.wjsum,'无') as wjsum//字符

//3、GROUP BY 中Count 

SELECT TOP 100 PERCENT wjs_scid,COUNT (wjs_scid) AS scidsum FROM [dbo].[wj_selectresult_wj] WHERE wjs_id = 28 GROUP BY wjs_id,wjs_cid,wjs_scid //视图或多表查询用到TOP 100 PERCENT 

//因为SQL规则规定，如果子查询里，有order ...，就必须有TOP，所以就用SELECT TOP 100 PERCENT来限定

SELECT  wjs_scid,COUNT (DISTINCT operator_id) AS scidsum FROM [dbo].[wj_selectresult_wj] WHERE wjs_id = 28 GROUP BY wjs_scid ;//分组 去重复 计数

//4、固定输出列
SELECT wjc.*, scidsum,wjsperc=0,wjpsum='0' FROM [dbo].[wjcontent_wj]


//5查询多字段重复
select group_id,app_id,operator_id from app_messages group by group_id,app_id,operator_id having count(*) > 1

//6 日期+-
select DateAdd(month,-1,DateAdd(dd,-1, DateAdd(yy,5,'2017-08-04')))  //+5年 -天 -一个月


//7 petapoco 分页

SELECT
    COUNT (*)
FROM
    Sys_User u
LEFT JOIN Sys_Organize o ON u.DepartmentId = o.Id
WHERE
    (
        u.DeleteMark = 0
        AND u.Account LIKE @0
        OR u.RealName LIKE @1
    )

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY u.SortCode) peta_rn,
            u.*, o.FullName
        FROM
            Sys_User u
        LEFT JOIN Sys_Organize o ON u.DepartmentId = o.Id
        WHERE
            (
                u.DeleteMark = 0
                AND u.Account LIKE @0
                OR u.RealName LIKE @1
            )
    ) peta_paged
WHERE
    peta_rn > @2
AND peta_rn <= @3 


//字符串in 操作 charindex mysql 用instr  不太好用，不推荐使用，请用 转int 方式
SELECT
    Id,
    Name,
    ParentId
FROM
    HR_PostWorkType
WHERE
    1 = 1
AND 
charindex(PostId,'9,10,0') <> 0
ORDER BY
    SortCode ASC

//按生日得到当前年龄
 SELECT dbo.HR_Person.RealName as 姓名,Gender_Name as 性别,FLOOR(datediff(DY,Birthday,getdate())/365.25) as 年龄,dbo.HR_Person.CardNum as 身份证号,dbo.HR_Person.Org_Name as 单位,MobilePhone as 联系电话,Address as 地址 FROM [dbo].[HR_Person] WHERE


--备份mssql数据库

declare @strfile nvarchar(1000)
SET @strfile ='D:\DB\DDPT\edu_dudao'+CONVERT(varchar(100), GETDATE(), 12)+'.bak'
declare @strName nvarchar(1000)
SET @strName ='edu_dudao'+CONVERT(varchar(100), GETDATE(), 12)

BACKUP DATABASE [edu_dudao]
TO
  DISK = @strfile
WITH
  NAME = @strName,
  NOFORMAT, NOINIT, SKIP,
  STATS = 5
 
--按月份统计数量

SELECT
    YEAR (HR_WorkRecord.StartTime) 年,MONTH (HR_WorkRecord.StartTime) 月,
    COUNT (1) 数量
FROM
    HR_WorkRecord
WHERE
year(HR_WorkRecord.StartTime)='2018'
GROUP BY
    YEAR (HR_WorkRecord.StartTime) , MONTH (HR_WorkRecord.StartTime)

select 
sum(case month(HR_WorkRecord.StartTime) when '1' then 1 else 0 end) as 一月,
sum(case month(HR_WorkRecord.StartTime) when '2' then 1 else 0 end) as 二月,
sum(case month(HR_WorkRecord.StartTime) when '3' then 1 else 0 end) as 三月,
sum(case month(HR_WorkRecord.StartTime) when '4' then 1 else 0 end) as 四月,
sum(case month(HR_WorkRecord.StartTime) when '5' then 1 else 0 end) as 五月,
sum(case month(HR_WorkRecord.StartTime) when '6' then 1 else 0 end) as 六月,
sum(case month(HR_WorkRecord.StartTime) when '7' then 1 else 0 end) as 七月,
sum(case month(HR_WorkRecord.StartTime) when '8' then 1 else 0 end) as 八月,
sum(case month(HR_WorkRecord.StartTime) when '9' then 1 else 0 end) as 九月,
sum(case month(HR_WorkRecord.StartTime) when '10' then 1 else 0 end) as 十月,
sum(case month(HR_WorkRecord.StartTime) when '11' then 1 else 0 end) as 十一月,
sum(case month(HR_WorkRecord.StartTime) when '12' then 1 else 0 end) as 十二月
from HR_WorkRecord
where year(HR_WorkRecord.StartTime)='2018';

--统计销售额
1、每年
select year(ordertime) 年，
sum(Total) 销售合计
from
订单表
group by year(ordertime)
2、每月
select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 销售合计
from
订单表
group by year(ordertime)，
month(ordertime
3、每日
select year(ordertime) 年，
month(ordertime) 月,
day(ordertime) 日，
sum(Total) 销售合计
from
订单表
group by year(ordertime)，
month(ordertime),
day(ordertime)
另外每日也可以这样:
select convert(char(8),ordertime,112) dt，
sum(Total) 销售合计
from
订单表
group by convert(char(8),ordertime,112)
如果需要增加查询条件，在from后加where 即可。

转载于:https://www.cnblogs.com/chenmfly/p/5735561.html