3
As correctly noted by Charlieface, this is a Gaps and Islands problem. Another way of solving this specific variation – also involving a window function, though a different one this time – would go like this:
WITH
partitioned AS
(
SELECT
*
, DATEDIFF(Date, '1970-01-01') - ROW_NUMBER() OVER (ORDER BY Date ASC) AS PartID
FROM
...
2
the correct error message is
Error Code: 1336. Dynamic SQL is not allowed in stored function or trigger
SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute ...
1
This is a type of gaps-and-islands problem, of which there are a number of solutions.
Here is one:
We can identify the starting points of each island by using LAG to check the previous row (with a default for the first row)
We can then number the islands using a running COUNT
Then simply group by that number
WITH StartingPoints AS (
SELECT *,
...
1
WITH RECURSIVE -- calendar generation needs in recuirsion
-- generate calendar, DATE() performs parameter checking additionally
calendar AS ( SELECT DATE(@start) created_at
UNION ALL
SELECT created_at + INTERVAL 1 DAY
FROM calendar
WHERE created_at < DATE(@end) ),
-- collect currencies list
...
1
In general, a mapping table does not need its own id. Instead, the PRIMARY KEY should be a composite key with the 2 (or more) column-ids referencing the tables that are being related in a many-to-many relationship.
For a 1-to-many relationship, a mapping table is unnecessary.
Your "DLB" smells like a "Relation", hence the above comments ...
1
You must use INSERT .. SELECT:
INSERT INTO course_student ( course_id , student_id )
SELECT course.id, student.id
FROM course
CROSS JOIN student
WHERE course.name = 'K01'
AND student.name = 'Duy';
1
Each sub-SELECT needs its own parentheses:
INSERT INTO course_student(course_id , student_id )
VALUES (
( SELECT id FROM course WHERE name = "K01" ),
( SELECT id FROM student WHERE name = "Duy" )
);
Only top voted, non community-wiki answers of a minimum length are eligible
