本示例是:查询药品最后一次采购的供应商和采购日期时间。注意:WITH (NOLOCK)只是为了防止查询时表死锁,如果你的表不会死锁,可以删除WITH (NOLOCK)
方式一:按日期时间倒序排序取第一条为条件,此方式效率最低,查询速度非常慢,8028条数据29秒,此方式不推荐使用
SELECT P_id,商品名称,最后一次供应商,最后一次采购时间
FROM
(SELECT Auto_id AS P_id FROM dbo.Products) sp --商品表
LEFT JOIN
(SELECT P_id AS cgP_id,商品名称,供应商 AS 最后一次供应商,记账时间 AS 最后一次采购时间 FROM dbo.Productdetail WITH (NOLOCK) WHERE 单据类型 = '采购入库单') cg --单据明细表
ON cg.cgP_id = sp.P_id AND cg.最后一次采购时间 = (SELECT TOP 1 记账时间 FROM dbo.Productdetail WITH (NOLOCK) WHERE 单据类型 = '采购入库单' AND P_id = sp.P_id ORDER BY 记账时间 DESC) --按日期时间倒序排序取第一条(即采购最大日期时间)为条件
WHERE 最后一次采购时间 BETWEEN '2023-01-01 00:00:00' AND '2024-06-30 23:59:59'
ORDER BY 最后一次采购时间
方式二:按最大日期时间为条件,比方式一效率高,查询速度快,8028条数据1秒,若关联的表少和数据量小,可以推荐使用
SELECT P_id,商品名称,最后一次供应商,最后一次采购时间
FROM
(SELECT Auto_id AS P_id FROM dbo.Products) sp --商品表
LEFT JOIN
(SELECT P_id AS cgP_id,商品名称,供应商 AS 最后一次供应商,记账时间 AS 最后一次采购时间 FROM dbo.Productdetail WITH (NOLOCK) WHERE 单据类型 = '采购入库单') cg --单据明细表
ON cg.cgP_id = sp.P_id AND cg.最后一次采购时间 = (SELECT MAX(记账时间) FROM dbo.Productdetail WITH (NOLOCK) WHERE 单据类型 = '采购入库单' AND P_id = sp.P_id) --取最大采购日期时间为条件
WHERE 最后一次采购时间 BETWEEN '2023-01-01 00:00:00' AND '2024-06-30 23:59:59'
ORDER BY 最后一次采购时间
方式三:按最大日期时间为条件,比方式一效率高,查询速度快,8028条数据1秒,若关联的表多和数据量大,推荐使用该方式三,该方式三查询速度最快
SELECT P_id,商品名称,最后一次供应商,最后一次采购时间
FROM
(SELECT Auto_id AS P_id FROM dbo.Products) sp --商品表
LEFT JOIN
(SELECT P_id AS lastP_id,MAX(记账时间) AS 最大记账时间 FROM dbo.Productdetail WITH (NOLOCK) WHERE 单据类型 = '采购入库单' GROUP BY P_id) lastTime --获取每个商品的采购最大日期时间
ON lastTime.lastP_id = sp.P_id
LEFT JOIN
(SELECT P_id AS cgP_id,商品名称,供应商 AS 最后一次供应商,记账时间 AS 最后一次采购时间 FROM dbo.Productdetail WITH (NOLOCK) WHERE 单据类型 = '采购入库单') cg --单据明细表
ON cg.cgP_id = sp.P_id AND cg.最后一次采购时间 = lastTime.最大记账时间 --取最大日期时间为条件
WHERE 最后一次采购时间 BETWEEN '2023-01-01 00:00:00' AND '2024-06-30 23:59:59'
ORDER BY 最后一次采购时间
总结:推荐使用方式三,效率高,速度快。大家可根据自己实际情况选择适合自己的方式