SQL 按日月年统计分析数据

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

ycsno1

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

阅读量1.4k

点赞数

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

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

版权

Windows 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

如下，表名是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)