SQL 按日月年统计分析数据

最新推荐文章于 2022-10-24 15:52:12 发布

ycsno1

最新推荐文章于 2022-10-24 15:52:12 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： Windows 文章标签： sql null

本文链接：https://blog.csdn.net/ycsno1/article/details/6536162

Windows 专栏收录该内容

2 篇文章

订阅专栏

本文提供了一种通过SQL查询来分析库存和销售的方法。包括按日、月、年的库存及销售情况查询，帮助理解商品流通状况。

如下，表名是Circulation，开是日期是WareHouseInTime，结束日期是WareHouseOutTime
--按日查询库存
SELECT COUNT(ID) Amount,WareHouseInTime FROM Circulation
WHERE WareHouseInTime IS NOT NULL AND WareHouseOutTime IS NULL
GROUP BY WareHouseInTime

--按月查询库存
SELECT COUNT(ID) Amount,
CONVERT(VARCHAR,YEAR(WareHouseInTime))+'年'+CONVERT(VARCHAR,MONTH(WareHouseInTime))+'月' 频率
FROM Circulation
WHERE WareHouseInTime IS NOT NULL AND WareHouseOutTime IS NULL
GROUP BY CONVERT(VARCHAR,YEAR(WareHouseInTime))+'年'+CONVERT(VARCHAR,MONTH(WareHouseInTime))+'月'

--按年查询库存
SELECT COUNT(ID) Amount,
CONVERT(VARCHAR,YEAR(WareHouseInTime))+'年' 频率
FROM Circulation
WHERE WareHouseInTime IS NOT NULL AND WareHouseOutTime IS NULL
GROUP BY YEAR(WareHouseInTime)

--按日查询销售
SELECT COUNT(ID) Amount,WareHouseInTime FROM Circulation
WHERE WareHouseInTime IS NOT NULL AND WareHouseOutTime IS NOT NULL
GROUP BY WareHouseInTime

--按月查询销售
SELECT COUNT(ID) Amount,
CONVERT(VARCHAR,YEAR(WareHouseInTime))+'年'+CONVERT(VARCHAR,MONTH(WareHouseInTime))+'月' 频率
FROM Circulation
WHERE WareHouseInTime IS NOT NULL AND WareHouseOutTime IS NOT NULL
GROUP BY CONVERT(VARCHAR,YEAR(WareHouseInTime))+'年'+CONVERT(VARCHAR,MONTH(WareHouseInTime))+'月'

--按年查询库存
SELECT COUNT(ID) Amount,
CONVERT(VARCHAR,YEAR(WareHouseInTime))+'年' 频率
FROM Circulation
WHERE WareHouseInTime IS NOT NULL AND WareHouseOutTime IS NOT NULL
GROUP BY YEAR(WareHouseInTime)