英文:
Get empty row if no record matches mysql in a single table
问题
以下是翻译好的部分：
CREATE TABLE table
(`id` int, `date` date, `time` time);
INSERT INTO table
(`id`, `date`, `time`)
VALUES
(1, '2022-05-22', 14:00:00),
(2, '2022-05-23', 07:35:00),
(4, '2022-05-23', 14:00:00);
期望的输出：
| date | time |
|-------------|-----------|
| 2022-05-22 | 14:00:00 |
| 2022-05-22 | NULL |
| 2022-05-23 | 07:35:00 |
| 2022-05-23 | 14:00:00 |
正如您注意到的，2022-05-22日期没有07:35的记录。
我已尝试在单个表上进行连接和使用CTE，但都没有成功。
我正在使用PHP和MySQL。
提前感谢。
英文:
CREATE TABLE table
(`id` int, `date` date, `time` time);
INSERT INTO table
(`id`, `date`, `time`)
VALUES
(1, '2022-05-22', 14:00:00),
(2, '2022-05-23', 07:35:00),
(4, '2022-05-23', 14:00:00);
Expected Output:
| date | time |
|---|---|
| 2022-05-22 | 14:00:00 |
| 2022-05-22 | NULL |
| 2022-05-23 | 07:35:00 |
| 2022-05-23 | 14:00:00 |
As you notice, there's no entry for 07:35 at date 2022-05-22.
I have tried join on single table and CTE also but nothing works.
I'm using PHP with MySQL
Thanks in advance.
答案1
得分: 2
一个日历表的方法 可能 是您要寻找的内容。考虑以下选择查询，它可以生成您想要的精确输出。
WITH dates AS (
SELECT '2022-05-22' AS dt UNION ALL
SELECT '2022-05-23'
),
times AS (
SELECT '07:35:00' AS tm UNION ALL
SELECT '14:00:00'
)
SELECT d.dt AS 日期, yt.time
FROM dates d
CROSS JOIN times t
LEFT JOIN yourTable yt
ON yt.date = d.dt AND
yt.time = t.tm
ORDER BY d.dt, t.tm;
英文:
A calendar table approach might be what you are looking for. Consider the following select query which however generates the exact output you want.
<!-- language: sql -->
WITH dates AS (
SELECT '2022-05-22' AS dt UNION ALL
SELECT '2022-05-23'
),
times AS (
SELECT '07:35:00' AS tm UNION ALL
SELECT '14:00:00'
)
SELECT d.dt AS date, yt.time
FROM dates d
CROSS JOIN times t
LEFT JOIN yourTable yt
ON yt.date = d.dt AND
yt.time = t.tm
ORDER BY d.dt, t.tm;
答案2
得分: 0
@Tim Biegeleisen提供的解决方案完全有效，但不幸的是，我的服务器不支持CTE，所以对于那些对CTE了解较少或使用较低版本的mysql的人，可以尝试这个解决方案...
SELECT DISTINCT(d.dt) AS date, yt.time
FROM (SELECT date as dt FROM tableName ) as d
CROSS JOIN (SELECT time as tm FROM tableName ) as t
LEFT JOIN tableName yt
ON yt.date = d.dt AND yt.time = t.tm
ORDER BY d.dt, t.tm;
英文:
Solution given by @Tim Biegeleisen is working just fine but unfortunately my server doesn't suppoert CTE, so who has little knowledge of CTE or have lower version of mysql can try this solution...
SELECT DISTINCT(d.dt) AS date, yt.time
FROM (SELECT date as dt FROM tableName ) as d
CROSS JOIN (SELECT time as tm FROM tableName ) as t
LEFT JOIN tableName yt
ON yt.date = d.dt AND yt.time = t.tm
ORDER BY d.dt, t.tm;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论