MySql查询ls_order当天的数据 时间的列名是create_time，是时间类型

您可以使用以下 SQL 查询语句来获取 ls_order 表中当天的数据：

SELECT * FROM ls_order WHERE DATE(create_time) = CURDATE();

其中，`ls_order` 是您要查询的表名，`create_time` 是存储时间的列名。`DATE()` 函数用于从日期时间值中提取日期部分。`CURDATE()` 函数用于获取当前日期。这条 SQL 语句将返回 ls_order 表中当天的所有数据。如果您只需要特定列的数据，则可以将 `*` 替换为您需要的列名。

相关问题

SELECT pl.id AS product_line_id, pl.name AS product_line_name, ROUND(SUM(s.price), 2) AS daily_sales, ROUND(SUM(s.price) OVER (PARTITION BY pl.id, DATE_TRUNC('week', o.create_time)), 2) AS weekly_sales, ROUND(SUM(s.price) OVER (PARTITION BY pl.id, DATE_TRUNC('month', o.create_time)), 2) AS monthly_sales, ROUND(SUM(s.price) OVER (PARTITION BY pl.id, DATE_TRUNC('year', o.create_time)), 2) AS yearly_sales FROM ls_order_sku AS od JOIN ls_order AS o ON o.id = od.order_id JOIN ls_sku AS s ON s.id = od.sku_id JOIN ls_spu AS sp ON sp.id = s.spu_id JOIN ls_product_line AS pl ON pl.id = s.product_line_id GROUP BY pl.id, pl.name, DATE_TRUNC('day', o.create_time) ORDER BY pl.id, DATE_TRUNC('day', o.create_time) 这个sql 报 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY pl.id, DATE_TRUNC('week', o.create_time)), 2) AS weekly_sales, ' at line 5 这个错 怎么解决

根据报错信息，提示语法错误在第5行，即 `SUM(s.price) OVER (PARTITION BY pl.id, DATE_TRUNC('week', o.create_time))` 这一行。这是因为在 MySQL 中，没有 `OVER` 关键字来支持窗口函数，而 `OVER` 是 PostgreSQL 中的语法。因此，需要将 `SUM(s.price) OVER` 改为 `SUM(s.price)`，并将 `GROUP BY` 语句中的分组条件改为 `pl.id, pl.name, DATE(o.create_time)`，即：

SELECT
    pl.id AS product_line_id,
    pl.name AS product_line_name,
    ROUND(SUM(s.price), 2) AS daily_sales,
    ROUND(SUM(CASE WHEN DATE_TRUNC('week', o.create_time) = DATE_TRUNC('week', CURDATE()) THEN s.price ELSE 0 END), 2) AS weekly_sales,
    ROUND(SUM(CASE WHEN DATE_TRUNC('month', o.create_time) = DATE_TRUNC('month', CURDATE()) THEN s.price ELSE 0 END), 2) AS monthly_sales,
    ROUND(SUM(CASE WHEN DATE_TRUNC('year', o.create_time) = DATE_TRUNC('year', CURDATE()) THEN s.price ELSE 0 END), 2) AS yearly_sales
FROM
    ls_order_sku AS od
    JOIN ls_order AS o ON o.id = od.order_id
    JOIN ls_sku AS s ON s.id = od.sku_id
    JOIN ls_spu AS sp ON sp.id = s.spu_id
    JOIN ls_product_line AS pl ON pl.id = s.product_line_id
GROUP BY
    pl.id,
    pl.name,
    DATE(o.create_time)
ORDER BY
    pl.id,
    DATE(o.create_time)

另外，因为 MySQL 不支持 `DATE_TRUNC` 函数，所以需要使用 `DATE` 函数将日期截取到天、月、年。上面的 SQL 语句中，我使用了类似 `DATE_TRUNC('week', o.create_time) = DATE_TRUNC('week', CURDATE())` 这样的语法来判断当前记录的日期是否属于本周、本月、本年。