SQL 合集

1.分组查询每日类目ID的综合

SELECT dateTime,COUNT(distinct cateId) 每日类目总数 FROM [MTGoods_Down].[dbo].[sycmRXDP] where dateTime>'20201201' and shopId =1 group by dateTime order by dateTime desc
 

image.png

2.分组查询并且排序

SELECT dateTime,shopId,COUNT(distinct cateId) total_num FROM [MTGoods_Down].[dbo].[sycmRXDP] where dateTime>'20201201'  group by dateTime,shopId order by dateTime desc ,total_num asc,shopId desc
 

image.png

3.分组查询且过滤总数

image.png

SELECT dateTime,shopid,COUNT(distinct cateId) as tatol_num FROM [MTGoods_Down].[dbo].[sycmRXDP] where dateTime>'20201201' group by dateTime,shopId having COUNT(distinct cateId)>5 order by dateTime desc

image.png

多值复制

DECLARE @SONG CHAR(50),@A INT ,@B INT,@C INT     #声明变量，标量类型
SELECT @SONG = 'I LOVE SUTDY',@A = 1,@B =2       #赋值
SET @C = @A+@B     #运算
PRINT @SONG
PRINT @A
PRINT @B
PRINT @C

IF判断

DECLARE @A INT
SET @A =3
IF  @A>0
PRINT @A
ELSE
PRINT '小于0'

日期函数

SELECT DAY( GETDATE())   #获取当前天数
SELECT MONTH( GETDATE())   #获取当前月份
SELECT DATEDIFF(DAY,'20111101','20111105')  AS 时间间隔

在现有的时间上减少一个月

SELECT DATEADD ("MONTH",-1,GETDATE())

查询男员工年龄不等高于25或者女员工年龄等于23

select * from emp where sex = '男' and not age= 24 or sex= '女' and age = 23

查询男员工里面性王的

select * from emp where sex = '男' and name like '王%'

查询学生表里面性别为男的和 教师表里面性别为男的

select * from student where sex = '男'
union all
select * from teacher where sexe = '男'

使所有男生的年龄+1 所有女生的年龄-1

update student 
set age = 
case when  sex ='男'  then age +1
when  sex = '女' then age-1
end

查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

select * from  employees order by hire_date desc limit 1 offset 2

limit表示要取的数量，offset跳过的数据，取一条 跳2条

查询前两天数据

SELECT COUNT(1) FROM [oprHYGCHD] where businessDate=CONVERT(varchar(100), GETDATE()-2, 23)
>>>
'2020-01-03'