杂记

• 存在则更新

   //on duplicate key update

• 调用存储过程

  $sql = "call proc_shift_intermediary_special({$specialId},{$saleId},@result)";
  $res = Db::query($sql);
  

• 时间戳日期互转

  FROM_UNIXTIME 时间戳转日期
     UNIX_TIMESTAMP 日期转时间戳
  

• 条件显示

   //如果为空 填充0
   IFNULL(t.`CouponYouMoney`,0) AS '优惠券抵扣金额数'
   
   //枚举
   (
     CASE t.`TransferType`
     WHEN '0' THEN
      '户转'
     WHEN '1' THEN
      '止盈强转'
   WHEN '2' THEN
      '止损强转'
   WHEN '3' THEN
      '强转'
   WHEN '4' THEN
      '投资商强转'
     ELSE
      '未知状态'
     END
    ) '卖出类型',
   
   //子查询出的结果也可以作为一个表用来 join到主表里
   SELECT s.`Code` AS '编码',
   s.`CompanyName` AS '公司名称',
   s.`Money` AS '资产',
   s.`TotalProfit` AS '总利润',
   s.`TotalStorage` AS '总库存',
   IFNULL(t.`TotalMoney`,'0') AS '总额'
   FROM
   `tb_view_special` s
   LEFT JOIN (
   SELECT SpecialId,sum(TotalMoney) as TotalMoney FROM `tb_transaction` WHERE `State` = 1 GROUP BY SpecialId
   ) t
   ON s.SpecialId = t.SpecialId
   WHERE
   s.`State` IN ('0', '1')