Ошибка с одним SQL-запросом

Я пытаюсь написать SQL-запрос для моей базы данных, который будет извлекать все определенные события пользователей. Если они отмечены как повторяющиеся, я хочу, чтобы они показывались еженедельно в течение 52 недель после даты начала. Есть ли лучший способ сделать это? Мой текущий код кажется довольно неуклюжим.

Мои таблицы структурированы следующим образом:

event (
 event_id INT NOT NULL AUTO_INCREMENT, (PK)
 title VARCHAR(80) NOT NULL,
 description VARCHAR(200),
 event_start DATETIME,
 event_end DATETIME,
 group_id INT NOT NULL,
 recurring BOOLEAN
);

 Users{
 UserID (PK)
 Username
 }

 Groups{
 GroupID (PK)
 GroupName
 }

 Membership{
 UserID (FK)
 GroupID (FK)
 }

Вот запрос, который у меня есть на данный момент, он в настоящее время не работает, может ли кто-нибудь помочь мне?

var result = db.Query(
 @"SELECT e.event_id, e.title, e.description, e.event_start, e.event_end, e.group_id, e.recurring
 FROM event e
 JOIN Membership m ON m.GroupID = e.group_id
 WHERE e.recurring
 AND m.UserID = ?
 AND e.event_start >= ?
 AND e.event_end <= ?
 UNION ALL
 SELECT e.event_id, e.title, e.description, DATEADD(week, w.weeks, e.event_start),
 DATEADD(week, w.weeks, e.event_end), e.group_id, e.recurring
 FROM event e
 JOIN Membership m ON m.GroupID = e.group_id,
 (SELECT row_number() OVER (ORDER BY Object_ID) AS weeks
 FROM SYS.OBJECTS) AS w
 WHERE NOT e.recurring
 AND m.user_id = ?
 AND e.event_start >= ?
 AND e.event_end <= ?", username, start, end, username, start, end
 );

Может ли кто-нибудь помочь мне адаптировать мой код, чтобы он выбирал все события, которые не повторяются, и он выбирает повторяющиеся события одновременно в течение 52 недель?

2 ответа

Может выглядеть так:

SELECT e.event_id, e.title, e.description, e.start_time, e.end_time
 , e.group_id, e.recurring
FROM Events e
JOIN Membership m ON m.groupid = e.group_id
WHERE e.recurring = 0 
AND m.user_id = ?
AND e.start_time >= ?
AND e.end_time <= ?

UNION ALL
SELECT e.event_ID, e.title, e.description
 ,DATEADD(week, w.weeks, e.start_time) -- AS event_start
 ,DATEADD(week, w.weeks, e.end_time) -- AS event_end
 ,e.group_id, e.recurring
FROM Events e
JOIN Membership m ON m.groupid = e.group_id
 ,(SELECT row_number() OVER (ORDER BY Object_ID) AS weeks
 FROM SYS.OBJECTS) AS w
WHERE e.recurring = 1
AND m.user_id = ?
AND e.start_time >= ?
AND e.end_time <= ?
  • Используйте UNION ALL вместо UNION.

  • event_start и event_end вместо start_time и end_time (??) Я изменил это.

  • Я тоже это пропустил: user_id находится в таблице Membership, поэтому вам нужно JOIN это, если вы хотите использовать его в предложении WHERE. Внешние столбцы ключей - group_id/groupid. (Я бы унифицировал орфографию!)

  • У вас был SELECT * в первой части запроса. Лучше указать имена столбцов в таком запросе, или более поздние изменения в основной таблице будут разбивать его неожиданными способами.

  • Мой первый проект имел вспомогательную функцию generate_series() для генерации набора чисел 1-52 отсюда, но ваше исходное решение лучше, поэтому я вернул это изменение.


Попробуй это. Вы использовали event_start и event_stop в своем SQL, но они не были определены в таблице Events. Единственное, что вы перепутали, было start_time <= @CurrentTime AND end_time> = @CurrentTime. Я не думаю, что ваш код неуклюж. UNION ALL работает лучше, чем UNION (UNION работает как DISTINCT: сначала ORDER BY, а затем удаляет дубликаты).

DROP TABLE #Events
CREATE TABLE #Events (
 event_id INT NOT NULL,
 title VARCHAR(80) NOT NULL,
 description VARCHAR(200),
 start_time DATETIME,
 end_time DATETIME,
 group_id INT NOT NULL,
 recurring bit
);

INSERT INTO #Events(event_id, title, description, start_time, end_time, group_id, recurring) VALUES
(1, 'meeting1', 'meeting1', '20000101', '20000102', 1, 0),
(2, 'meeting2', 'meeting2', '20120309', '20120310', 1, 0),
(3, 'meeting3', 'meeting3', '20120311', '20120312', 1, 0),
(10, 'meeting10', 'meeting10', '20000101', '20000102', 1, 1),
(20, 'meeting20', 'meeting20', '20120309', '20120310', 1, 1),
(30, 'meeting30', 'meeting30', '20120311', '20120312', 1, 1)

DECLARE @CurrentTime DATETIME
SET @CurrentTime = GETDATE()

SELECT event_id, 
 title,
 description, 
 start_time, 
 end_time, 
 group_id, 
 recurring
FROM #Events 
WHERE recurring = 0 AND 
-- user_id = ? AND 
 start_time <= @CurrentTime AND 
 end_time >= @CurrentTime

UNION ALL

SELECT event_ID, 
 title, 
 description, 
 DATEADD(WEEK, Interval, start_time) [event_start], 
 DATEADD(WEEK, Interval, end_time) [event_end], 
 group_id, 
 recurring
FROM #Events,
 (SELECT ROW_NUMBER() OVER(ORDER BY Object_ID) [Interval]
 FROM SYS.OBJECTS) as T 
WHERE recurring = 1 AND 
 Interval <= 52 AND 
-- user_id = ? AND 
 start_time <= @CurrentTime AND 
 end_time >= @CurrentTime
;

licensed under cc by-sa 3.0 with attribution.