如果在单个表中没有匹配MySQL的记录,则获取空行

vcirk6k6  于 2023-05-28  发布在  Mysql
关注(0)|答案(2)|浏览(182)
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);

预期输出:
| 日期|时间|
| - -----|- -----|
| 2022-05-22 2022-05-22| 14点整|
| 2022-05-22 2022-05-22|空值|
| 2022-05-23|七点三十五分|
| 2022-05-23| 14点整|
如您所见,日期为2022-05-22的07:35没有条目。
我也试过在单表和CTE上加入,但没有任何效果。
我使用PHP与MySQL
先谢谢你了。

ffx8fchx

ffx8fchx1#

日历表方法 * 可能 * 是您正在寻找的。考虑下面的选择查询,它会生成您想要的精确输出。

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;
72qzrwbm

72qzrwbm2#

@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;

相关问题