数据库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 分頁SELECTCOUNT (*)
FROMSys_User u
LEFT JOIN Sys_Organize o ON u.DepartmentId = o.Id
WHERE(u.DeleteMark = 0AND u.Account LIKE @0OR u.RealName LIKE @1)SELECT*
FROM(SELECTROW_NUMBER () OVER (ORDER BY u.SortCode) peta_rn,u.*, o.FullNameFROMSys_User uLEFT JOIN Sys_Organize o ON u.DepartmentId = o.IdWHERE(u.DeleteMark = 0AND u.Account LIKE @0OR u.RealName LIKE @1)) peta_paged
WHEREpeta_rn > @2
AND peta_rn <= @3 //字符串in 操作 charindex mysql 用instr 不太好用，不推薦使用，請用 轉int 方式
SELECTId,Name,ParentId
FROMHR_PostWorkType
WHERE1 = 1
AND
charindex(PostId,'9,10,0') <> 0
ORDER BYSortCode 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]
TODISK = @strfile
WITHNAME = @strName,NOFORMAT, NOINIT, SKIP,STATS = 5--按月份統計數量SELECTYEAR (HR_WorkRecord.StartTime) 年,MONTH (HR_WorkRecord.StartTime) 月,COUNT (1) 數量
FROMHR_WorkRecord
WHERE
year(HR_WorkRecord.StartTime)='2018'
GROUP BYYEAR (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
總結
- 上一篇： MqSql的加锁分析
- 下一篇： UIVIewController自定义切