mysql分页+日期加减，新人求助

根据ExpiryUnit日期单位和.ExpiryDate字段值来判断商品是否过期
日期单位 1：天，2：月，3：年；

SELECT SQL_CALC_FOUND_ROWS
 a.ProductID,a.ProductName,a.ExpiryDate,a.ExpiryUnit,b.ProductionDate,b.WareHouseID,b.BinID,b.ShelfID
FROM product a
LEFT JOIN  warehouseinventory b ON a.ProductID=b.ProductID  
WHERE DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)<=NOW() and  a.CompanyID=@CompanyID
LIMIT 0,10;
SELECT FOUND_ROWS() AS TotalRecords;

上面得出的结果是已过期商品；
如何如何根据日期单位来替换 DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate  DAY)函数中‘DAY’为‘MONTH’或者为'YEAR'
尝试用case语句只能得到一列数据

SELECT CASE  ExpiryUnit
WHEN 1 then
  (SELECT  a.ProductID
FROM product a 
LEFT JOIN  warehouseinventory b ON a.ProductID=b.ProductID  
WHERE DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)<=NOW() and  a.CompanyID=@CompanyID
)
ELSE '' END 
AS t
FROM product  

实在是太菜了，求助大神！！

5 个解决方案

#1

case when 可以多次判断case when ....then.... when.... then....else ...end

#2

引用 1 楼 wangjian0228 的回复:

case when 可以多次判断case when ....then.... when.... then....else ...end

是的，只是每一次then后面的判断结果只有一列，而我需要查出多列
最新的sql ，似乎解决了，这样写合理吗？

SELECT SQL_CALC_FOUND_ROWS
 a.ProductID,a.ProductName,a.ExpiryDate,a.ExpiryUnit,b.ProductionDate,b.WareHouseID,b.BinID,b.ShelfID
FROM product a
LEFT JOIN  warehouseinventory b ON a.ProductID=b.ProductID 
WHERE ( a.ExpiryUnit=1 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)<=NOW() )   
OR    ( a.ExpiryUnit=2 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)<=NOW())
OR    ( a.ExpiryUnit=3 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)<=NOW() )
AND    a.CompanyID=@CompanyID
LIMIT 0,10;
SELECT FOUND_ROWS() AS TotalRecords;

#3

引用 2 楼 C_2016 的回复:

Quote: 引用 1 楼 wangjian0228 的回复:

case when 可以多次判断case when ....then.... when.... then....else ...end

是的，只是每一次then后面的判断结果只有一列，而我需要查出多列
最新的sql ，似乎解决了，这样写合理吗？

SELECT SQL_CALC_FOUND_ROWS
 a.ProductID,a.ProductName,a.ExpiryDate,a.ExpiryUnit,b.ProductionDate,b.WareHouseID,b.BinID,b.ShelfID
FROM product a
LEFT JOIN  warehouseinventory b ON a.ProductID=b.ProductID 
WHERE ( a.ExpiryUnit=1 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)<=NOW() )   
OR    ( a.ExpiryUnit=2 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)<=NOW())
OR    ( a.ExpiryUnit=3 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)<=NOW() )
AND    a.CompanyID=@CompanyID
LIMIT 0,10;
SELECT FOUND_ROWS() AS TotalRecords;

感觉你这个不符合逻辑呀  

DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)
DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)
DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)

你这个 a.ExpiryDate 应该是保质期吧，如果写的是12个月呢？
那你是要加12年?
其次 就是你这个条件都是用or连接的，那就是优先级相等的条件，满足一条就行，是不是应该考虑一下实际的业务，应该有个优先级的判断吧？

#4

引用 3 楼 VertigozZ 的回复:

Quote: 引用 2 楼 C_2016 的回复:

Quote: 引用 1 楼 wangjian0228 的回复:

case when 可以多次判断case when ....then.... when.... then....else ...end

是的，只是每一次then后面的判断结果只有一列，而我需要查出多列
最新的sql ，似乎解决了，这样写合理吗？

SELECT SQL_CALC_FOUND_ROWS
 a.ProductID,a.ProductName,a.ExpiryDate,a.ExpiryUnit,b.ProductionDate,b.WareHouseID,b.BinID,b.ShelfID
FROM product a
LEFT JOIN  warehouseinventory b ON a.ProductID=b.ProductID 
WHERE ( a.ExpiryUnit=1 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)<=NOW() )   
OR    ( a.ExpiryUnit=2 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)<=NOW())
OR    ( a.ExpiryUnit=3 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)<=NOW() )
AND    a.CompanyID=@CompanyID
LIMIT 0,10;
SELECT FOUND_ROWS() AS TotalRecords;

感觉你这个不符合逻辑呀  

DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)
DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)
DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)

你这个 a.ExpiryDate 应该是保质期吧，如果写的是12个月呢？
那你是要加12年?
其次 就是你这个条件都是用or连接的，那就是优先级相等的条件，满足一条就行，是不是应该考虑一下实际的业务，应该有个优先级的判断吧？

如果是12个月的她的ExpiryUnit=2,会在（ a.ExpiryUnit=2 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)）中筛选，业务逻辑就是筛选全部符合条件的，不需要优先级判断。

#5

引用 4 楼 C_2016 的回复:

Quote: 引用 3 楼 VertigozZ 的回复:

Quote: 引用 2 楼 C_2016 的回复:

Quote: 引用 1 楼 wangjian0228 的回复:

case when 可以多次判断case when ....then.... when.... then....else ...end

是的，只是每一次then后面的判断结果只有一列，而我需要查出多列
最新的sql ，似乎解决了，这样写合理吗？

SELECT SQL_CALC_FOUND_ROWS
 a.ProductID,a.ProductName,a.ExpiryDate,a.ExpiryUnit,b.ProductionDate,b.WareHouseID,b.BinID,b.ShelfID
FROM product a
LEFT JOIN  warehouseinventory b ON a.ProductID=b.ProductID 
WHERE ( a.ExpiryUnit=1 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)<=NOW() )   
OR    ( a.ExpiryUnit=2 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)<=NOW())
OR    ( a.ExpiryUnit=3 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)<=NOW() )
AND    a.CompanyID=@CompanyID
LIMIT 0,10;
SELECT FOUND_ROWS() AS TotalRecords;

感觉你这个不符合逻辑呀  

DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate DAY)
DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)
DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate YEAR)

你这个 a.ExpiryDate 应该是保质期吧，如果写的是12个月呢？
那你是要加12年?
其次 就是你这个条件都是用or连接的，那就是优先级相等的条件，满足一条就行，是不是应该考虑一下实际的业务，应该有个优先级的判断吧？

如果是12个月的她的ExpiryUnit=2,会在（ a.ExpiryUnit=2 AND DATE_ADD(b.ProductionDate,INTERVAL a.ExpiryDate MONTH)）中筛选，业务逻辑就是筛选全部符合条件的，不需要优先级判断。

那就没有问题了，可以这么写