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

最新推荐文章于 2024-08-05 12:37:12 发布

墨玉先生

最新推荐文章于 2024-08-05 12:37:12 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

分类专栏： java

本文链接：https://blog.youkuaiyun.com/weidong22/article/details/73650268

java 专栏收录该内容

22 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

-- 今日记录
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
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) a,
(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) b,
(SELECT salesid,COUNT(tgtime) AS alltgnum FROM tbcompany GROUP BY salesid) c
WHERE a.salesid=b.salesid AND b.salesid=c.salesid

SELECT SUM(Money)
-(SELECT IFNULL(SUM(Money),0) FROM kg_account_variation WHERE DealType=2 AND (DealState=1 OR DealState=2) AND DistributorId IS NULL AND ShopUserId=1070 AND ShopId=1041) AS Money

FROM kg_account_variation WHERE DealType=1 AND DealState=2 AND DistributorId IS NULL AND ShopUserId=1070 AND ShopId=1041