SQLite لتحديد التواريخ ضمن نطاق ولكن بدون أيام الأحد وبعض الأعياد

2

الكود التالي

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-11-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x<'2018-01-31' AND 'weekday'>0
        ) 
        SELECT * FROM dates;

لا يستثني أيام الأحد من التواريخ. لماذا لا؟

كما أود استبعاد بعض التواريخ من مجموعة النتائج للتواريخ الموجودة في جدول آخر في قاعدة البيانات. دعنا نسمي هذا الجدول للعطلات. يمكن أن يكون مخطط هذا الجدول كما يلي:

CREATE TABLE Holidays (
    id              INTEGER PRIMARY KEY
                            UNIQUE,
    NameOfHoliday      TEXT    DEFAULT NULL,
    startDate DATE    DEFAULT NULL,
    endDate DATE    DEFAULT NULL
);

أدخل بعض البيانات في جدول الأعياد:

INSERT INTO Holidays VALUES(1,'Winter Break','2017-12-23','2018-01-14');

ثم كيف تستخدم CTE أعلاه مع جدول العطلات هذا لتحقيق النتيجة التي تحتوي على التواريخ ولكن بدون أيام الأحد وبدون تواريخ بين تاريخ البدء وتاريخ الانتهاء في العطلات؟

أفضل ، بال

1 إجابة

1
افضل جواب

جزء من مشكلتك هو أن "يوم الأسبوع" (سلسلة) سيكون دائمًا أكبر من 0 ، لذلك سيكون الاختبار دائمًا صحيحًا.

المشكلة الثانية هي أن العودية تتوقف عندما يتم إرجاع نتيجة فارغة من SELECT اليمنى (العودية). لذلك إذا كان لديك اختبار ليوم الأحد في هذا التحديد ، فلن يتم إجراء المزيد من عمليات التكرار عندما لا يتم استيفاء الشرط. وبالتالي ، سيتم تحديد البيانات فقط حتى الأحد الأول (لن يؤدي الأحد إلى أي صف) (حيث أن 2017-09-01 هو يوم الجمعة ، وستحصل على صفين فقط لأن الثالث هو الأحد). لذلك يجب أن يكون اختبار الأحد بعد العودية.

ما تريده هو الحصول على يوم من الأسبوع كعدد صحيح 0-6 (Sun-Sat). يمكنك استخدام ال strftime('%w',valid_date) للحصول على القيمة ، ولكنك تحتاج بعد ذلك إلى التأكد من أنها عدد صحيح بدلاً من نص / حرف / سلسلة حتى تتمكن من استخدام CAST(strftime('%w',valid_date) As INTEGER) .

لإصلاح الصفوف التي تقتصر فقط على الأحد الأول ، يجب أن يكون شرط WHERE لإزالة أيام الأحد في SELECT النهائية بعد العودية.

على هذا النحو ، يعد ما يلي إصلاحًا للمشكلات التي يمكنك استخدامها: -

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-09-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x <'2017-09-30'
        ) 
        SELECT x FROM dates WHERE CAST(strftime('%w',x) As INTEGER) > 0;

سيؤدي هذا إلى 26 صفًا ، بدلاً من 30 صفًا على سبيل المثال: -

Imagen 698937

......

  • أي يتم استبعاد الثالث والعاشر (وهكذا).

إضافي

بخصوص السؤال الاضافي: -

Also I would like to exclude some dates from the result set of dates which dates are in another table in the database. Let is call that table Holidays. The schema for that table could be like this:

CREATE TABLE Holidays (
    id              INTEGER PRIMARY KEY
                            UNIQUE,
    NameOfHoliday      TEXT    DEFAULT NULL,
    startDate DATE    DEFAULT NULL,
    endDate DATE    DEFAULT NULL
);

Let insert some data into Holidays table:

INSERT INTO Holidays VALUES(1,'Winter Break','2017-12-23','2018-01-14');

هذا معقد تمامًا خاصة أنه قد يكون لديك العديد من العطلات التي يجب مراعاتها (لنفترض أنك تريد القيام بعام كامل). ما أقترحه بدلاً من أن يكون لديك نطاق من التواريخ في عمودين هو أن يكون لديك جدول بتواريخ فردية هي عطلات ، ومن ثم يكون من السهل جدًا تلبية ذلك.

بدلاً من جدول الأعياد ، لنفترض أن هناك جدول أبسط للعطلات مثل: -

CREATE TABLE IF NOT EXISTS AltHolidays (NameOfHoliday TEXT, Day_To_Exclude TEXT);
INSERT OR IGNORE INTO AltHolidays VALUES
    ('Winter Break','2017-12-23'),
    ('Winter Break','2017-12-24'),
    ('Winter Break','2017-12-25'),
    ('Winter Break','2017-12-26'),
    ('Winter Break','2017-12-27'),
    ('Winter Break','2017-12-28'),
    ('Winter Break','2017-12-29'),
    ('Winter Break','2017-12-30'),
    ('Winter Break','2017-12-31'),
    ('Winter Break','2018-01-01'),
    ('Winter Break','2018-01-02'),
    ('Winter Break','2018-01-03'),
    ('Winter Break','2018-01-04'),
    ('Winter Break','2018-01-05'),
    ('Winter Break','2018-01-06'),
    ('Winter Break','2018-01-07'),
    ('Winter Break','2018-01-08'),
    ('Winter Break','2018-01-09'),
    ('Winter Break','2018-01-10'),
    ('Winter Break','2018-01-11'),
    ('Winter Break','2018-01-12'),
    ('Winter Break','2018-01-13'),
    ('Winter Break','2018-01-14')
;
  • من الناحية المثالية ، سيتم الاحتفاظ بالأوصاف في جدولها الخاص وسيتم الرجوع إليها.

ثم (بافتراض أنك لا تزال تريد استبعاد أيام الأحد أيضًا) ، يمكنك استخدامه NOT IN مثل: -

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-11-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x<'2018-01-31'
        ) 
        SELECT * 
                FROM dates 
                WHERE x NOT IN (SELECT Day_To_Exclude FROM AltHolidays) -- Exclude holidays
                    AND CAST(strftime('%w',x) AS INTEGER) <> 0 -- Exclude Sundays
;

سيؤدي ذلك إلى 60 صفًا (92 - 23 (أيام العطل) - 9 (أيام الأحد المتبقية))

:مؤلف
فوق
قائمة طعام