我已经为此寻找了答案，但还没有找到合适的解决方案。我有一个数据库与一个表"数据"，在那里我有所有的产品与他们的到期日期列出。不同产品的总数约为120，但"data"表有超过5000行。每个产品都有多个独立的，过期监控的单元。
我需要列出所有独特的产品，并列出每年失效日期的列。目前，我在主查询的迭代中为每年运行多个另一个查询，以计算两个日期之间过期日期的出现次数。
CREATE TABLE IF NOT EXISTS `data` (
`EXPIRY` date DEFAULT NULL,
`RNO` int(6) DEFAULT NULL,
`PRODID` int(3) DEFAULT NULL,
`NAME` varchar(38) DEFAULT NULL,
`RND` varchar(2) DEFAULT NULL,
`RDY` varchar(3) DEFAULT NULL,
`ARTY` varchar(3) DEFAULT NULL,
`ARYK` varchar(4) DEFAULT NULL,
`BATCH` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
// list all unique product rows
$main_loop = $database->query('SELECT * FROM data GROUP BY rno HAVING COUNT(*) >=1');
$rows = $database->resultset($main_loop);
foreach($rows as $data) {
echo "<tr>";
echo "<td>".$data['RNO']."</td>";
<td>
// another query to count occurrences of expiry in each product between two dates
$thisrno = $data['RNO'];
$database->query("SELECT * FROM data WHERE EXP BETWEEN '2016-01-01' AND '2016-12-31' AND RNO = '$thisrno'");
$database->execute();
$rows = $database->rowCount();
echo $rows;
echo "</td><td>";
...i would need to do this for each column (2016-2025)
...
}
我相信有更好的解决办法。问题还在于，这样我就不能按每年到期单位的数量对表进行排序，而且效率不高。
结果应该是:
RNO | NAME | Expiring in 2016 | Expiring in 2017 | ...
336540 | Prod_name_1 | 34 | 62 |
391755 | Prod_name_2 | 2 | 116 |
653112 | Prod_name_3 | 46 | 7 |
看起来相当简单:
select rno, name,
sum(exp between '2016-01-01' and '2016-12-31') as exp2016,
sum(exp between '2017-01-01' and '2017-12-31') as exp2017,
sum(exp between '2018-01-01' and '2018-12-31') as exp2018,
sum(exp between '2019-01-01' and '2019-12-31') as exp2019,
sum(exp between '2020-01-01' and '2020-12-31') as exp2020,
sum(exp between '2021-01-01' and '2021-12-31') as exp2021,
sum(exp between '2022-01-01' and '2022-12-31') as exp2022,
sum(exp between '2023-01-01' and '2023-12-31') as exp2023,
sum(exp between '2024-01-01' and '2024-12-31') as exp2024,
sum(exp between '2025-01-01' and '2025-12-31') as exp2025
from data
where exp between '2016-01-01' and '2025-12-31'
group by rno;
如果你想包括那些年内没有过期的产品，省略where子句。
我不确定你的问题是关于排序的;您可以选择一年，并根据该年的过期次数对行进行排序，但这似乎有点奇怪。