如果你想避免重写整个查询，你可以通过使用CTE（公共表表达式）来计算单个查询中两个日期范围的计数来实现这一点。这种方法并没有完全消除重写查询，但封装了共享逻辑。下面是一个使用CTE的示例：

WITH DateRanges AS (
  SELECT
    (SELECT COUNT(*)
     FROM TABLE
     WHERE birth_date >= TO_DATE(:start_date1, 'YYYY/MM/DD')
       AND birth_date <= TO_DATE(:end_date1, 'YYYY/MM/DD')) AS count1,
    (SELECT COUNT(*)
     FROM TABLE
     WHERE birth_date >= TO_DATE(:start_date2, 'YYYY/MM/DD')
       AND birth_date <= TO_DATE(:end_date2, 'YYYY/MM/DD')) AS count2
)
SELECT count1, count2
FROM DateRanges;

字符串
此SQL查询使用一个名为DateRanges的CTE，通过对每个日期范围使用单独的SELECT NULL（*）子查询来计算count1和count2，而无需重复整个查询逻辑。此方法允许您在一次查询执行中计算这两个计数。

您可以连接一个返回每个日期的子查询，然后使用GROUP BY。

SELECT t1.start_date, t1.end_date, COALESCE(COUNT(t2.birth_date), 0) AS count
FROM (
    SELECT :start_date1 AS start_date, :end_date1 AS end_date
    UNION ALL
    SELECT :start_date2 AS start_date, :end_date2 AS end_date
) t1
LEFT JOIN TABLE t2 ON t2.birth_date BETWEEN t1.start_date AND t1.end_date
GROUP BY t1.start_date, t1.end_date

字符串

您可以使用条件聚合仅查询表一次：

SELECT COUNT(
         CASE
         WHEN birth_date BETWEEN TO_DATE(:start_date1, 'YYYY/MM/DD')
                             AND TO_DATE(:end_date1, 'YYYY/MM/DD')
         THEN 1
         END
       ) AS count1,
       COUNT(
         CASE
         WHEN birth_date BETWEEN TO_DATE(:start_date2, 'YYYY/MM/DD')
                             AND TO_DATE(:end_date2, 'YYYY/MM/DD')
         THEN 1
         END
       ) AS count2
FROM   TABLE
WHERE  birth_date BETWEEN LEAST(TO_DATE(:start_date1, 'YYYY/MM/DD'), TO_DATE(:start_date2, 'YYYY/MM/DD'))
                      AND GREATEST(TO_DATE(:end_date1, 'YYYY/MM/DD'), TO_DATE(:end_date2, 'YYYY/MM/DD'))

字符串
如果你想把它作为一个大型查询的一部分，那么CROSS JOIN：

SELECT d.*,
       c.count1,
       c.count2
FROM   DUAL d
       CROSS JOIN (
         SELECT COUNT(
                  CASE
                  WHEN birth_date BETWEEN TO_DATE(:start_date1, 'YYYY/MM/DD')
                                      AND TO_DATE(:end_date1, 'YYYY/MM/DD')
                  THEN 1
                  END
                ) AS count1,
                COUNT(
                  CASE
                  WHEN birth_date BETWEEN TO_DATE(:start_date2, 'YYYY/MM/DD')
                                      AND TO_DATE(:end_date2, 'YYYY/MM/DD')
                  THEN 1
                  END
                ) AS count2
         FROM   TABLE
         WHERE  birth_date BETWEEN LEAST(TO_DATE(:start_date1, 'YYYY/MM/DD'), TO_DATE(:start_date2, 'YYYY/MM/DD'))
                               AND GREATEST(TO_DATE(:end_date1, 'YYYY/MM/DD'), TO_DATE(:end_date2, 'YYYY/MM/DD'))
       ) c;

型