te')); return $arr; } /* 遍历用户所有主题 * @param $uid 用户ID * @param int $page 页数 * @param int $pagesize 每页记录条数 * @param bool $desc 排序方式 TRUE降序 FALSE升序 * @param string $key 返回的数组用那一列的值作为 key * @param array $col 查询哪些列 */ function thread_tid_find_by_uid($uid, $page = 1, $pagesize = 1000, $desc = TRUE, $key = 'tid', $col = array()) { if (empty($uid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('uid' => $uid), array('tid' => $orderby), $page, $pagesize, $key, $col); return $arr; } // 遍历栏目下tid 支持数组 $fid = array(1,2,3) function thread_tid_find_by_fid($fid, $page = 1, $pagesize = 1000, $desc = TRUE) { if (empty($fid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('fid' => $fid), array('tid' => $orderby), $page, $pagesize, 'tid', array('tid', 'verify_date')); return $arr; } function thread_tid_delete($tid) { if (empty($tid)) return FALSE; $r = thread_tid__delete(array('tid' => $tid)); return $r; } function thread_tid_count() { $n = thread_tid__count(); return $n; } // 统计用户主题数 大数量下严谨使用非主键统计 function thread_uid_count($uid) { $n = thread_tid__count(array('uid' => $uid)); return $n; } // 统计栏目主题数 大数量下严谨使用非主键统计 function thread_fid_count($fid) { $n = thread_tid__count(array('fid' => $fid)); return $n; } ?>sql - Return list of params doesn't have records - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - Return list of params doesn't have records - Stack Overflow

    sql - Return list of params doesn't have records - Stack Overflow

    programmeradmin3浏览0评论

    Its possible to return list of dates doesn't having record on one request SQL? Example:

    Table A

    Id date. value
    1 2025-01-02 5
    2 2025-01-03 6
    3 2025-01-06 7
    4 2025-01-09 8

    Its possible to return list of dates doesn't having record on one request SQL? Example:

    Table A

    Id date. value
    1 2025-01-02 5
    2 2025-01-03 6
    3 2025-01-06 7
    4 2025-01-09 8

    I want return list of date who doesn't have record between two dates [dateStart,dateEnd]

    For example if dateStard = '2025-01-01' and dateEnd='2025-01-09'

    I expect result:

    date
    2025-01-04
    2025-01-05
    2025-01-07
    2025-01-08
    • sql
    • plsql
    Share edited Feb 17 at 22:19 Dale K 27.3k15 gold badges57 silver badges83 bronze badges asked Feb 17 at 21:55 DevJavaDevJava 396 bronze badges 8
    • What rdbms are you using? – Mureinik Commented Feb 17 at 22:02
    • Did you try anything? – Dale K Commented Feb 17 at 22:20
    • Should 2025-01-01 be part of expected results as well since your date range starts from 2025-01-01? – samhita Commented Feb 17 at 22:42
    • You tagged the request plsql. PL/SQL is Oracle's programming language. In your request you say you are looking for a SQL solution, though. Please remove the PL/SQL tag and tag the request with your DBMS instead. Are you using Oracle? Is this what you tried to say with that tag? – Thorsten Kettner Commented Feb 18 at 5:52
    • 1 Thank you , i have found solution ===> SELECT date_expected:: date FROM generate_series('2025-01-01', '2025-02-21', interval '1 day') AS date_expected EXCEPT (SELECT date FROM A) – DevJava Commented 2 days ago
     |  Show 3 more comments

    2 Answers 2

    Reset to default 1

    You can obtain the result by generating the dates and then comparing them with the table to identify the missing ones.

    For example let's start from 2025-02-17' The recursive part adds one day to the date, as long as dt is less than the final date.

    Then go and select all the dates generated by the recursive part and check where they are not present in the table.

    Basically you check if there is no record on those dates.

    I hope I explained myself:

    WITH RECURSIVE date_series AS (
            SELECT DATE_ADD('2025-02-17', INTERVAL 1 DAY) AS dt
            UNION ALL
            SELECT DATE_ADD(dt, INTERVAL 1 DAY)
            FROM date_series
            WHERE dt < DATE_SUB('2025-02-27', INTERVAL 1 DAY)
        )
        SELECT dt AS missing_date
        FROM date_series
        WHERE dt NOT IN (SELECT date_col FROM A);

    My Table:

    MariaDB [TEST]> select * from A;
+----+------------+-------+
| id | date_col   | value |
+----+------------+-------+
|  1 | 2025-01-02 |     5 |
|  2 | 2025-01-03 |     6 |
|  3 | 2025-01-06 |     7 |
|  4 | 2025-01-08 |     8 |
|  5 | 2025-02-16 |     5 |
|  6 | 2025-02-18 |     6 |
|  7 | 2025-02-20 |     7 |
|  8 | 2025-02-22 |     8 |
+----+------------+-------+


Result from my DB:
+--------------+
| missing_date |
+--------------+
| 2025-02-17   |
| 2025-02-19   |
| 2025-02-21   |
| 2025-02-23   |
| 2025-02-24   |
| 2025-02-25   |
| 2025-02-26   |
+--------------+

    I would say:

    -- Generate the list of all dates between dateStart and dateEnd. This is really Oracle-specific.
with fromto as
(
    select to_date('2025-01-01') + rownum - 1 as d from dual -- Remove the "- 1" if you want to start reporting missing dates from dateStart + 1 instead of dateStart.
    connect by to_date('2025-01-01') + rownum - 1 <= to_date('2025-01-09')
)
-- Now keep only those with no match in your data table.
select * from fromto where not exists (select 1 from t where t.d = fromto.d)
order by fromto.d;

    See it in a fiddle.

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论