SELECTCASEWHEN k.num>0THEN"大"ELSE"小"ENDAS SE
FROM
dy_sku K
//作为条件时:SELECTCASEWHEN(ifnull(k.num,0)-ifnull(k.sale_num,0))>0THEN"库存充足"ELSE"缺货中"ENDAS repertoryStatus
FROM
dy_sku k
LEFTJOIN dy_spu p ON k.spu_id = p.id
<where><if test="repertoryStatus != null and repertoryStatus != ''">andCASEWHEN(ifnull(k.num,0)-ifnull(k.sale_num,0))>0THEN"库存充足"ELSE"缺货中"END=#{repertoryStatus}</if></where>ORDERBY k.sort ASC,k.update_time DESC,k.create_time DESClimit#{pageNum},#{pageSize}
//1.间隔天数
datediff(NOW(),c.create_time)CASEWHEN(datediff(NOW(),c.create_time)>=30)THEN"1"ELSE"0"ENDAS thirtyDayExceptStatus
//2.自定单位
FRAC_SECOND 表示间隔是毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年
select timestampdiff(week,'2019-07-30','2019-09-04');3. 格式化时间只保留天与当前对比输出1即为过期(当前时间小于过期时间)
CASEWHEN(timestampdiff(DAY,DATE_FORMAT(t.expire_time,'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d'))>=0)THEN'1'ELSE'0'ENDAS preExpireStatus,
4.格式化时间
DATE_FORMAT(k.apply_time,'%Y年%m月')AS'applyTime'
5.mysql 触发器
-- ------------------------------ Triggers structure for table sku_tag-- ----------------------------#新增触发器DROPTRIGGERIFEXISTS`tag_sku_insert`;delimiter;;CREATETRIGGER`tag_sku_insert`AFTERINSERTON`sku_tag`FOR EACH ROWBEGINUPDATE dy_sku SET is_has_tag =1WHERE id = NEW.sku_id;END;;delimiter;#更新触发器DROPTRIGGERIFEXISTS`tag_sku_update`;delimiter;;CREATETRIGGER`tag_sku_update`AFTERUPDATEON`sku_tag`FOR EACH ROWBEGINIF NEW.one_tag_id ISNULLTHEN##去除标签UPDATE dy_sku SET is_has_tag =0WHERE id = NEW.sku_id;ELSEUPDATE dy_sku SET is_has_tag =1WHERE id = NEW.sku_id;ENDIF;END;;delimiter;DROPTRIGGERIFEXISTS`tag_po_det_update`;delimiter;;CREATETRIGGER`tag_po_det_update`AFTERUPDATEON`sms_wms_po_det`FOR EACH ROWBEGINDECLARE count intDEFAULT0;DECLARE count2 intDEFAULT0;SELECTcount(1)into count FROM sms_wms_po_det s WHERE s.twd_po_no = NEW.twd_po_no and s.twd_is_close ='Y';SELECTcount(1)into count2 FROM sms_wms_po_det s WHERE s.twd_po_no = NEW.twd_po_no and s.twd_is_close ='N';IF count >0THEN##存在关结UPDATE sms_wms_po SET po_state ='2'WHERE po_no = NEW.twd_po_no;ENDIF;IF count2<=0THEN##未关结数<=0,所有关结UPDATE sms_wms_po SET po_state ='4'WHERE po_no = NEW.twd_po_no;ENDIF;END;;delimiter;#删除触发器DROPTRIGGERIFEXISTS`tag_sku_delete`;delimiter;;CREATETRIGGER`tag_sku_delete`AFTERDELETEON`sku_tag`FOR EACH ROWBEGINUPDATE dy_sku SET is_has_tag =0WHERE id = OLD.sku_id;END;;delimiter;#启动触发器SET FOREIGN_KEY_CHECKS =1;#测试SELECT is_has_tag FROM dy_sku WHERE id=13;INSERTINTO`xmall`.`sku_tag`(`sku_id`,`one_tag_id`,`one_tag_name`,`two_tag_id`,`two_tag_name`)VALUES(13,2,'测试22',NULL,NULL);SELECT is_has_tag FROM dy_sku WHERE id=13;SELECT is_has_tag FROM dy_sku WHERE id=13;UPDATESET`one_tag_id`=NULLWHERE sku_id=13;SELECT is_has_tag FROM dy_sku WHERE id=13;SELECT is_has_tag FROM dy_sku WHERE id=13;DELETEFROM`xmall`.`sku_tag`WHERE sku_id=13;SELECT is_has_tag FROM dy_sku WHERE id=13;
DELETEFROM auto WHERE id in(SELECT a.id FROM(##通过user_id,disable查询重复记录,并保留一条SELECT b.*FROM auto b
WHERE b.user_id IN(SELECT user_id FROM auto GROUPBY license_plate_no,`owner`, user_id,`disable`HAVINGcount(*)>1)AND b.`disable`IN(SELECT`disable`FROM auto GROUPBY license_plate_no,`owner`, user_id,`disable`HAVINGcount(*)>1)AND b.id NOTIN(SELECTMIN(id)FROM auto GROUPBY license_plate_no,`owner`, user_id,`disable`HAVINGcount(*)>1)) a
);
15. 一对多时分组或查询最大值
订单,支付 一对多取出最大支付时间
select(selectMAX(p.pay_time)from payment p where p.parent_order_no=t.parent_order_no) payTime,
t.parent_order_no parentOrderNo
from sub_purchase_order t
16. 查重
SELECT Name FROM dbo.Member t WHERE Name IN(SELECT Name FROM dbo.Member GROUPBY Name HAVINGCOUNT(Name)>1)ORDERBY t.Name