sql根据时间查询当天-昨天记录

最新推荐文章于 2024-07-28 03:18:05 发布

墨玉先生

最新推荐文章于 2024-07-28 03:18:05 发布

阅读量2.9k

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/weidong22/article/details/73650268

版权

这篇博客介绍了如何使用SQL查询数据库中当天和昨天的记录。通过JOIN操作，分别获取了每个销售人员今日和昨日的销售记录数，并给出了所有记录的总数。同时，展示了如何联查这些数据以进行综合分析。

摘要由CSDN通过智能技术生成

-- 今日记录
SELECT t.salesid,IFNULL(tod.todaynum,0) AS todaynum
FROM tbcompany t
LEFT JOIN
(SELECT COUNT(tgtime) AS todaynum,salesid FROM tbcompany WHERE YEAR(tgtime)=YEAR(NOW()) AND MONTH(tgtime)=MONTH(NOW()) AND DAY(tgtime)=DAY(NOW()) GROUP BY salesid) tod
ON t.salesid=tod.salesid
GROUP BY salesid

-- 昨日记录
SELECT t.salesid,IFNULL(yed.ysdaynum,0) AS ysdaynum
FROM tbcompany t
LEFT JOIN
(SELECT salesid,COUNT(tgtime) AS ysdaynum FROM tbcompany WHERE TO_DAYS(NOW( ) ) - TO_DAYS(tgtime) = 1 GROUP BY salesid) yed
ON t.salesid=yed.salesid
GROUP BY salesid

-- 全部记录
SELECT salesid,COUNT(tgtime) AS alltgnum FROM tbcompany GROUP BY salesid

-- 联查
SELECT a.salesid,a.todaynum,b.ysdaynum,c.alltgnum
FROM
(SELECT t.salesid,IFNULL(tod.todaynum,0) AS todaynum
FROM tbcompany t