Hot answers tagged

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