目前工作中常用的一些语句整理
1、负数累加之和(有正负数)
SELECT SUM(ABS(credit_num)) from student_credit_detail;
重点在ABS,将所有分值转换为正数
2、统计表中一个字段大于0或者小于0的和
比如这个我需要统计一个表评分值加分数和减分数的总和
先用CASE when 将credit_num小于0的值筛出来,小于则用原值,大于则等于0,因为会有负数,所以用ABS取绝对值,最后用SUM求和
SELECT sum(ABS(CASE when credit_num<0 THEN credit_num ELSE 0 end)) as "减分数",
sum(CASE when credit_num>=0 THEN credit_num ELSE 0 end) as "加分数" from student_credit_detail WHERE create_time>"2023-05-01 00:00:00";
3、表中某个字段值统计
查询签到表,连表查活动签到表中每个活动签到的人数
SELECT T.`NAME`,COUNT(*) AS count FROM B ts
LEFT JOIN A T ON T.ID = ts.TRAINING_ID
WHERE T.IS_DELETED = 0
GROUP BY ts.TRAINING_ID HAVING count > 1
4、查看某一字段中的重复项
重复字段GROUP BY vehNoF HAVING COUNT(1) > 1
select 字段名 from 表名 group by 字段名 having count(字段名)>1
5、返回唯一值,去掉重复项
distinct 用于返回唯一不同的值,去掉重复值
limit详解,第一个值返回表中第几个值 比如第二个就填1,第二个值返回指定行数比如返回五行数据就填5 如果后面的都需要就填-1
select distinct id from 表名 order by id desc limit 1,5
6、用户id在其他表中出现的次数
SELECT wu.ID,wu.`NAME`,wu.IS_DELETED,(SELECT COUNT(1) FROM TRAINING_CREDIT WHERE WECHAT_USER_ID = wu.ID) trainingCredit, --培训活动学时条数
(SELECT COUNT(1) FROM SCHOOL_TRAINING_CREDIT WHERE WECHAT_USER_ID = wu.ID) schoolTrainingCredit, --校本活动学时数
(SELECT COUNT(1) FROM CONVERT_CREDIT WHERE WECHAT_USER_ID = wu.ID) convertCredit, --折算学时
(SELECT COUNT(1) FROM MUNICIPAL_TRAINING_CREDIT WHERE WECHAT_USER_ID = wu.ID) municipalTrainingCredit, --公需课
(SELECT COUNT(1) FROM O_LIVE_RECORD WHERE USER_ID = wu.ID) liveRecode, --直播学时数据
wu.WECHAT_JSON_USERID,wu.OPENID,wu.MOBILE_PHONE,wu.CARDID,wu.CREATE_TIME,wu.MODIFY_TIME
FROM WECHAT_USER wu WHERE --用户表
wu.ID = 12282 OR wu.ID = 24256045982
7、当前时间
select now()
8、迁移数据库中某张表到另外一个库 select into 会新建表
select * into cxgpsinformationbbpt.dbo.Warning from Warning where time>'2020-03-29'
9、迁移数据到已存在表中
insert into 新表 select *from 旧表;
10、清空表数据,释放内存
TRUNCATE TABLE warning;