LINUX.ORG.RU

История изменений

Исправление leave, (текущая версия) :

PGSQL

CREATE TABLE logs (id SERIAL DEFAULT, event_time TIMESTAMP NOT NULL, message VARCHAR(1024) NOT NULL);


WITH days AS (
  SELECT DISTINCT event_time::date AS day
  FROM logs
  WHERE message IS NOT NULL AND btrim(message) <> ''
  ORDER BY day DESC
  LIMIT 7
)
SELECT *
FROM logs
WHERE event_time::date IN (SELECT day FROM days)
ORDER BY event_time DESC;

MySQL

CREATE TABLE logs (id INT PRIMARY KEY, event_time TIMESTAMP NOT NULL, message VARCHAR(1024) NOT NULL);


SELECT *
FROM logs
WHERE DATE(event_time) IN (
    SELECT day
    FROM (
        SELECT DATE(event_time) AS day
        FROM logs
        WHERE message IS NOT NULL AND TRIM(message) <> ''
        GROUP BY DATE(event_time)
        ORDER BY day DESC
        LIMIT 7
    ) AS t
)
ORDER BY event_time DESC;

Исправление leave, :

PGSQL

CREATE TABLE logs (id SERIAL DEFAULT, event_time TIMESTAMP, message VARCHAR(1024));


WITH days AS (
  SELECT DISTINCT event_time::date AS day
  FROM logs
  WHERE message IS NOT NULL AND btrim(message) <> ''
  ORDER BY day DESC
  LIMIT 7
)
SELECT *
FROM logs
WHERE event_time::date IN (SELECT day FROM days)
ORDER BY event_time DESC;

MySQL

CREATE TABLE logs (id INT PRIMARY KEY, event_time TIMESTAMP NOT NULL, message VARCHAR(1024) NOT NULL);


SELECT *
FROM logs
WHERE DATE(event_time) IN (
    SELECT day
    FROM (
        SELECT DATE(event_time) AS day
        FROM logs
        WHERE message IS NOT NULL AND TRIM(message) <> ''
        GROUP BY DATE(event_time)
        ORDER BY day DESC
        LIMIT 7
    ) AS t
)
ORDER BY event_time DESC;

Исходная версия leave, : 

CREATE TABLE logs (id INT PRIMARY KEY, event_time TIMESTAMP NOT NULL, message VARCHAR(1024) NOT NULL);

CREATE TABLE logs (id SERIAL DEFAULT, event_time TIMESTAMP, message VARCHAR(1024));

PGSQL

WITH days AS (
  SELECT DISTINCT event_time::date AS day
  FROM logs
  WHERE message IS NOT NULL AND btrim(message) <> ''
  ORDER BY day DESC
  LIMIT 7
)
SELECT *
FROM logs
WHERE event_time::date IN (SELECT day FROM days)
ORDER BY event_time DESC;

MySQL

SELECT *
FROM logs
WHERE DATE(event_time) IN (
    SELECT day
    FROM (
        SELECT DATE(event_time) AS day
        FROM logs
        WHERE message IS NOT NULL AND TRIM(message) <> ''
        GROUP BY DATE(event_time)
        ORDER BY day DESC
        LIMIT 7
    ) AS t
)
ORDER BY event_time DESC;