SQL 查询语句

1. sql 准确取值

where id in(001,002,012) #条件为数字
where deptcd in ('BJ','BK','PJ','PK', 'YW') #条件为字段时要加引号
where pg.hname!='无人员' #否定取值
where  WORKER_DATE  = (case when left(CONVERT(varchar(100), GETDATE(), 8),2)<7 then CONVERT(varchar,GETDATE(),112)-1 else CONVERT(varchar,GETDATE(),112) end)# 条件取值

2. sql模糊取值

where name like '胡%'
#以胡开头是 '胡%'，以胡结尾是 '%胡'，包含胡是 '%胡%'
where name like '胡_'
#姓“胡”且名字为两个字
like'a_c'
# 匹配 a 和 c 之间只有一个字符的字符串,不严格区分大小写
not like 'a_c'
# 排除法匹配

3. sql 判断取值

select 
case when SUBSTRING(start_time,9,2)<7 then left(start_time,8)-1 else left(start_time,8) end as worker_date
from a

CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END

CASE WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' ELSE '不及格' END

4. sql 条件判断取值

where worker_type=(case when left(CONVERT(varchar(100), GETDATE(), 8),2)<20 and left(CONVERT(varchar(100), GETDATE(), 8),2)>6 then '0' else '1' end)

5. sql 多条件筛选

 where  worker_gx in ('PQ19','PQ20','PQ29') 

6. sql 表头重命名

select sum(work1_qty) 修补量 from a0 where  worker_gx in ('PQ19','PQ20','PQ29') group by worker_date

7. sql日期取值

#当月第一天
CONVERT(varchar,DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0),112)
#当月第一天前n天
CONVERT(varchar,DATEADD(MM,DATEDIFF(MM,0,GETDATE()),-n),112)

#当月所有日期，3月不可用
CONVERT(VARCHAR(10), DATEADD(DD, number, DATENAME(YY, GETDATE())+DATENAME(MM, GETDATE())+'01'), 112) FROM master..spt_values WHERE type='p' AND number<DAY(DATEADD(MM, 1, GETDATE())-DAY(GETDATE()))
#当日前n天所在月的所有日期，3月不可用
SELECT CONVERT(VARCHAR(10), DATEADD(DD, number, DATENAME(YY, GETDATE()-n)+DATENAME(MM, GETDATE()-n)+'01'), 112) work_date
FROM master..spt_values
WHERE type='p' AND number<DAY(DATEADD(MM, 1, GETDATE()-n)-DAY(GETDATE()-n))

#当月所有日期
CONVERT(VARCHAR(10), DATEADD(DD, number, DATENAME(YY, GETDATE())+DATENAME(MM, GETDATE())+'01'), 112) FROM master..spt_values WHERE type='p' AND number<32-Day(getdate()+(32-Day(getdate())))
#当日前n天所在月的所有日期
SELECT CONVERT(VARCHAR(10), DATEADD(DD, number, DATENAME(YY, GETDATE()-n)+DATENAME(MM, GETDATE()-n)+'01'), 112) work_date
FROM master..spt_values
WHERE type='p' AND number<32-Day(getdate()-n+(32-Day(getdate()-n)))
#当日前n天
CONVERT(varchar,GETDATE()-n,112)
#判断20221101是否与当前月份相差一个月
where DateDiff(mm, '20221101', GetDate())=1
# 求两个日期的时间差
DATEDIFF(datepart,startdate,enddate)

select   dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate()))     
/*上个月一号*/
select   dateadd(dd,-day(getdate()),getdate())                                
/* 上月月底 */
select   dateadd(dd,-day(getdate())+1,getdate()) 
/* 本月一号 */
select   dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate()))            
/* 本月底 */
select   dateadd(dd,-day(dateadd(month,1,getdate()))+1,dateadd(month,1,getdate()))        
/* 下月一号 */
select   dateadd(dd,-day(dateadd(month,2,getdate())),dateadd(month,2,getdate()))            
/* 下月月底 */

8. 字典查询

select a.name tabname,a1.name '字段',B.value '字段说明'
from sysobjects a left join sys.columns a1 on a.id = a1.object_id
left join sys.extended_properties b on b.major_id =a.id and b.minor_id = a1.column_id
where a.name ='wad100_line'

9. sql连接

1.内连接：两边同时存在
select * from book as a,stu as b where a.sutid = b.stuid
select * from book as a inner join stu as b on a.sutid = b.stuid
2.    左连接
select * from book as a left join stu as b on a.sutid = b.stuid
3.    右连接
select * from book as a right join stu as b on a.sutid = b.stuid
4.全连接
select * from book as a full outer join stu as b on a.sutid = b.stuid
5笛卡尔积
select * from book as a cross join stu as b order by a.id

10. sql 计算

# 除0为0
p.入库量/nullif(p.人数,0)
# 空值为0
isnull(a,0)
# 保留两位小数
round(a,2)

8. sql 两列二选一

case when isnull(real_inbox_work_date,'')='' then PLAN_review_date  else real_inbox_work_date end

9. 设置百分数

CONCAT(CAST(ROUND((2/10)*100,2) AS CHAR),'%')