在聚合函数COUNT和SUM里面调用IF函数

一、创建订单表和订单-商品表

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL COMMENT '用户ID',
  `status` tinyint(1) unsigned NOT NULL COMMENT '订单状态（0-未付款，1-已付款，2-已取消）',
  `price` decimal(6,2) unsigned NOT NULL COMMENT '订单总金额',
  `create_time` int(10) unsigned NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

CREATE TABLE `order_goods` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int(10) unsigned NOT NULL COMMENT '订单ID',
  `good_id` int(10) unsigned NOT NULL COMMENT '商品ID',
  `price` decimal(6,2) unsigned NOT NULL COMMENT '商品单价',
  `num` tinyint(3) unsigned NOT NULL COMMENT '商品数量',
  `create_time` int(10) unsigned NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `index_order_id` (`order_id`),
  KEY `index_good_id` (`good_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单-商品表'

 二、使用 PHP 随机生成一些数据

<?php
set_time_limit(60);
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8mb4';
try {
    $conn = new PDO($dsn, 'root', '123456');
} catch (PDOException $e) {
    die($e->getMessage());
}
// 生成 orders 表数据
$stmt = $conn->prepare('INSERT INTO orders (user_id, status, price, create_time) VALUES (?, ?, ?, ?)');
// 生成一万条订单，时间从今年1月1日0时0分到当前时间
$orderTotal = 10000;
$startTime = strtotime(date('Y-01-01'));
$endTime = time();
for ($i=0; $i < $orderTotal; $i++) {
    $times[] = mt_rand($startTime, $endTime);
}
sort($times);
for ($i=0; $i < $orderTotal; $i++) {
    $userId = mt_rand(1, 100);
    $status = array_rand([0, 1, 2]);
    $createTime = $times[$i];
    $result = $stmt->execute([$userId, $status, 0, $createTime]);
}

// 生成 100 个商品及对应价格
$goodTotal = 100;
for ($i=1; $i <= $goodTotal; $i++) { 
    $goods[$i] = mt_rand(30, 300);
}

// 生成 order_goods 表数据
$stmt = $conn->prepare('INSERT INTO order_goods (order_id, good_id, price, num, create_time) 
    VALUES (?, ?, ?, ?, ?)');
for ($i=1; $i <= $orderTotal; $i++) { 
    $rand = mt_rand(1, 5); // 假设一个订单包含1~5种不同商品
    $ids = [];
    for ($j=0; $j < $rand; $j++) { 
        $goodId = mt_rand(1, $goodTotal);
        if (in_array($goodId, $ids)) {
            continue;
        } else {
            array_push($ids, $goodId);
        }
        $num = mt_rand(1, 3); // 假设每种商品的购买数量为1~3件
        $result = $stmt->execute([$i, $goodId, $goods[$goodId], $num, 0]);
    }
}

// 更新 order_goods 表 create_time 字段
$conn->exec('UPDATE order_goods AS g,orders AS o 
    SET g.create_time = o.create_time 
    WHERE o.id = g.order_id');
// 更新 orders 表 price 字段
$conn->exec('UPDATE orders AS o SET price = 
    (SELECT SUM(price * num) FROM order_goods WHERE order_id = o.id)');

三、查询今天的总订单数，今天价格大于 100 的商品总数；昨天的总订单数，昨天价格大于 100 的商品总数

set @today = UNIX_TIMESTAMP(CURDATE()); -- 今天
set @tomarrow = UNIX_TIMESTAMP(DATE_ADD(CURDATE(), INTERVAL 1 day));  -- 明天
set @yesterday = UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 day)); -- 昨天
SELECT COUNT(DISTINCT IF(o.create_time BETWEEN @today AND @tomarrow, g.order_id, NULL)) AS today_total_orders,
SUM(IF(g.price > 100 AND g.create_time BETWEEN @today AND @tomarrow, g.num, 0)) AS today_total_goods,
COUNT(DISTINCT IF(o.create_time BETWEEN @yesterday AND @today, g.order_id, NULL)) AS yesterday_total_orders, 
SUM(IF(g.price > 100 AND g.create_time BETWEEN @yesterday AND @today, g.num, 0)) AS yesterday_total_goods  
FROM orders AS o,order_goods AS g 
WHERE o.id = g.order_id;

四、总结

在聚合函数 COUNT 和 SUM 中可以这样使用 IF 函数：

　　count(if(表达式, true, null))、count(distinct if(表达式, 字段名称, null))

　　sum(if(表达式, 字段名称, 0))