您可以使用带有SELECT语句的子查询来生成最近7天内所有日期的列表，然后将其与事务表进行LEFT JOIN。这将为您提供一个结果集，其中0表示没有事务的日期：

SELECT  dates.date, COALESCE(SUM(transactio s.amount), 0) AS total_amount FROM (SELECT DATE_SUB(CURDATE(),INTERVAL (7 + (@i := @i + 1)) DAY) AS date  FROM    (SELECT @i := -1) r, information_schema.tables  LIMIT 7) dates LEFT JOIN transactions ON dates.date =DATE(transactions.transaction_date) GROUP BY dates.date ORDER BY dates.date DESC;

尝试先生成日期，然后联接数据表：

SELECT COUNT(transaction_id) AS orders
      , SUM(amount) AS sales
      , CAST(dates.time AS DATE) AS time 
 FROM 
 (
    SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) + INTERVAL num DAY AS time
    FROM 
    (
       SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
     ) n 
  ) dates
 LEFT JOIN tbltransactions 
    ON dates.time = tbltransactions.time
 GROUP BY CAST(dates.time AS DATE) 
 ORDER BY dates.time ASC

以下是测试数据：

CREATE TABLE tbltransactions (
  time DATE,
  transaction_id INT,
  amount DECIMAL(10,2)
);

INSERT INTO tbltransactions (time, transaction_id, amount)
VALUES 
  ('2023-01-20', 1, 100.00),
  ('2023-01-21', 2, 200.00),
  ('2023-01-27', 3, 300.00),
  ('2023-01-29', 4, 400.00),
  ('2023-01-29', 5, 500.00);