gpt4 book ai didi

mysql - sql查询以像闹钟一样按时获取冲突

转载 作者:行者123 更新时间:2023-11-29 17:19:52 26 4
gpt4 key购买 nike

我正在尝试创建一个 sql 查询来获取表上可能存在冲突的列表,但到目前为止我的 sql 不起作用。这个想法是有一个闹钟功能

如果date_start = date_end 则表示一次拍摄

示例:date_start = 2018-11-07 10:37:00date_end = 2018-11-07 10:37:00

如果 date_start 不为 nulldate_end = null ,其中某一天列应与 0 不同

示例:date_start = 2018-11-07 10:37:00 且 date_end = NULL 且星期一 = 2。这意味着从每个星期一的 start_date 开始,我将在那时获得该行

我现在的问题是,如果我有 2 行可以同时具有相同的时间和日期,我正在尝试获取冲突列表。这是我的数据库的示例:

sampledb

我在上面创建了一个 sql 查询,但效果不佳,有人可以帮忙吗?

SELECT
id
FROM
my_table
WHERE
date_start IN(
SELECT
date_start
FROM
my_table
WHERE
date_start = date_end
GROUP BY
date_start
HAVING
COUNT(*) > 1
)
UNION
SELECT
id
FROM
my_table
WHERE
DATE_FORMAT(date_start, '%H:%i:%s') IN(
SELECT
DATE_FORMAT(date_start, '%H:%i:%s')
FROM
my_table
WHERE
date_end IS NULL AND(
monday = 2 OR tuesday = 3 OR wednesday = 4 OR thursday = 5 OR friday = 6 OR saturday = 7 OR sunday = 1
)
GROUP BY
DATE_FORMAT(date_start, '%H:%i:%s')
HAVING
COUNT(*) > 1
)
UNION
SELECT
id
FROM
my_table
WHERE
DATE_FORMAT(date_start, '%H:%i:%s') IN(

SELECT
CASE WHEN
(DAYOFWEEK(date_start) = 2 AND (select monday from my_table where monday = 2) IS NOT NULL)
OR (DAYOFWEEK(date_start) = 3 AND (select tuesday from my_table where tuesday = 3) IS NOT NULL)
OR (DAYOFWEEK(date_start) = 4 AND (select wednesday from my_table where wednesday = 4) IS NOT NULL)
OR (DAYOFWEEK(date_start) = 5 AND (select thursday from my_table where thursday = 5) IS NOT NULL)
OR (DAYOFWEEK(date_start) = 6 AND (select friday from my_table where friday = 6) IS NOT NULL)
OR (DAYOFWEEK(date_start) = 7 AND (select saturday from my_table where saturday = 7) IS NOT NULL)
OR (DAYOFWEEK(date_start) = 1 AND (select sunday from my_table where sunday = 1) IS NOT NULL)
THEN DATE_FORMAT(date_start, '%H:%i:%s')
ELSE NULL
END
FROM
my_table
WHERE
(DAYOFWEEK(date_start) = 2
OR DAYOFWEEK(date_start) = 3
OR DAYOFWEEK(date_start) = 4
OR DAYOFWEEK(date_start) = 5
OR DAYOFWEEK(date_start) = 6
OR DAYOFWEEK(date_start) = 7
OR DAYOFWEEK(date_start) = 1
)
GROUP BY
DATE_FORMAT(date_start, '%H:%i:%s')
HAVING
COUNT(*) > 1
)

最佳答案

也许是这样的

SELECT date_start FROM alarms 
WHERE date_end IS NULL AND
monday + tuesday + wednesday + thursday + friday + saturday + sunday > 0
GROUP BY TIME(date_start)
HAVING COUNT(monday)>0 OR COUNT(tuesday)>0 OR COUNT(wednesday)>0 OR
COUNT(thursday)>0 OR COUNT(friday)>0 OR COUNT(saturday)>0 OR COUNT(sunday)>0

更新

如果您想捕获一次性事件和每周事件之间的冲突,您可以使用这样的查询

SELECT a1.date_start AS oneshow,a2.date_start AS weekly,DAYOFWEEK(a1.date_start) as dow 
FROM alarms AS a1
LEFT JOIN alarms AS a2 ON
a1.date_start >= a2.date_start AND a2.date_end IS NULL
AND TIME(a1.date_start) = TIME(a2.date_start)
AND DAYOFWEEK(a1.date_start) IN (a2.monday, a2.tuesday, a2.wednesday, a2.thursday, a2.friday, a2.saturday, a2.sunday)
WHERE a1.date_end IS NOT NULL

关于mysql - sql查询以像闹钟一样按时获取冲突,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51317034/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com