作者：小5聊基础
简介：一只喜欢全栈方向的程序员，欢迎咨询，尽绵薄之力答疑解惑
编程原则：Write Less Do More
- 知识点
| 编号 | 知识点 | 说明 |
| 1 | group by | 对某个表字段进行分组 |
| 2 | convert | 数据类型转换 |
| 3 | case when then else end | 逻辑判断 |
| 4 | max | 最大值 |
| 5 | min | 最小值 |
| 6 | count | 记录数 |
| 7 | sum | 统计值，综合 |
| 8 | left join | 左链接 |
1、知识点
1）group by
此处以商品编号进行分组
select shopid from stock group by shopid
编辑2）
2）convert
数据类型转换，varchar(10)和120，datetime时间数据类型转为字符串，并只显示10位长度值，刚好就是yyyy-MM-dd
select convert(varchar(10),timevalue,120) as timevalue from stock
编辑
2、创建表
USE [test] GO /****** Object: Table [dbo].[stock] Script Date: 04/17/2022 10:52:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[stock]( [typeValue] [varchar](50) NULL, [shopid] [int] NULL, [timevalue] [datetime] NULL, [stockValue] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
3、表添加记录
模拟一定量的记录进行测试
--truncate table stock /* insert into stock(typeValue,shopid,timevalue,stockValue) values('入库',123,'2020-01-01',100) insert into stock(typeValue,shopid,timevalue,stockValue) values('入库',234,'2020-01-02',100) insert into stock(typeValue,shopid,timevalue,stockValue) values('出库',123,'2020-01-03',50) insert into stock(typeValue,shopid,timevalue,stockValue) values('出库',234,'2020-01-04',50) insert into stock(typeValue,shopid,timevalue,stockValue) values('出库',123,'2020-01-05',50) insert into stock(typeValue,shopid,timevalue,stockValue) values('出库',234,'2020-01-06',50) insert into stock(typeValue,shopid,timevalue,stockValue) values('入库',123,'2020-01-07',100) insert into stock(typeValue,shopid,timevalue,stockValue) values('入库',234,'2020-01-08',100) insert into stock(typeValue,shopid,timevalue,stockValue) values('出库',123,'2020-01-09',30) insert into stock(typeValue,shopid,timevalue,stockValue) values('出库',234,'2020-01-10',30) */
4、查询代码
/* 编号123，还存在库存，则使用上次进货时间2020-01-11和2020-02-20相差，就是40天 编号234，还存在库存，则使用上次进货时间2020-01-11和2020-02-20相差，就是41天' */ declare @searchTime varchar(50) set @searchTime='2020-02-20' select a.shopid as 商品编号, convert(varchar(10),a.inMaxTime,120) as 上次进货时间, @searchTime as 搜索时间, (case when (a.totalInValue-b.totalOutValue)>0 then datediff(day, a.inMaxTime, @searchTime) --还有库存，则用上次进货时间计算 else datediff(day, b.outMaxTime, @searchTime) end) as 库龄, --没有库存了，使用最后一次出库时间计算 (a.totalInValue-b.totalOutValue) as '库存', (case when (a.totalInValue-b.totalOutValue)>0 then '还有库存' else '已出完' end) as 是否出库完 from( --先按商品编号分组，并筛选入库值和统计入库总值 --出库最小得时间 select shopid, min(timevalue) as inMinTime, --入库最小时间 max(timevalue) as inMaxTime, --入库最大时间（上次进货时间） sum(stockValue) as totalInValue, count(1) as inCount from stock where typeValue='入库' and timevalue<=@searchTime group by shopid ) as a left join( select shopid, min(timevalue) as outMinTime, --出库最小时间 max(timevalue) as outMaxTime, --出库最大时间 sum(stockValue) as totalOutValue --出库总数 from stock where typeValue='出库' and timevalue<=@searchTime group by shopid ) as b on a.shopid=b.shopid
