SQL查询库存管理
希望我可以解释我遇到的问题。
我必须使用pseudocode/sql查询编写逐步方法，以自动生成库存数据库中库存低/到期的产品/项目列表。该列表必须在每天12点进行更新。
我尝试了
CREATE EVENT IF NOT EXISTS update_table
ON SCHEDULE EVERY 1 DAY STARTS '2022-05-22 00:00:00'
ON COMPLETION PRESERVE ENABLE
Do
Select inventory.products from inventory where inventory.stocks <
inventory.required_stocks.
Hope I can explain the problem I'm having trouble with.
I have to write a stepwise methodology using pseudocode/SQL query to auto generate a list of products/items with low stock/expiry from the inventory database.The list must be updated at 12 a.m. daily.
I tried this
CREATE EVENT IF NOT EXISTS update_table
ON SCHEDULE EVERY 1 DAY STARTS '2022-05-22 00:00:00'
ON COMPLETION PRESERVE ENABLE
Do
Select inventory.products from inventory where inventory.stocks <
inventory.required_stocks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您所陈述的要求是在每个日历日开始后不久就会运行某种报告。
您必须回答的下一个问题是：您将如何处理该报告？您是否只需将其放入数据库中某个地方的“ Low_stock”表中？您是否将其格式化为电子邮件并将其发送给您的采购部门？如果不首先分析您打算增强的整体业务流程，就很难为您的需求制作“伪代码”。
各种RDBMS系统都有在一天中的特定时间进行计划的方法。您已经显示了Mariadb / MySQL提供的事件设置。 SQL Server具有其“作业”系统。 PostgreSQL具有PG_CRON扩展名。哟
，事情是，您不能仅仅在这些计划的数据库操作中进行选择的操作：结果集从该上下文中具有noplace。您可以做
创建表Midnight_run作为选择“ whthing ...”以将结果放在表中。但是结果在另一个表中。如果您想从DBM中获取结果，则需要 unixish cron job 或a
专业提示尽力避免安排精确的午夜。那时有很多事情。如果您等到小时几分钟后，您的代码不太可能与其他午夜代码抗衡。
Your stated requirement is to run some sort of report very soon after the beginning of each calendar day.
The next question you must answer is this: What will you do with that report? Will you simply drop it into "low_stock" table someplace in your database? Will you format it into an email message and send it to your purchasing department? It will be difficult to make "pseudocode" for your requirement without first analyzing the overall business process you intend to enhance.
Various RDBMS systems have ways of doing scheduled things at particular times of day. You've shown the EVENT setup provided by MariaDB / MySQL. SQL Server has their "Jobs" system. postgreSQL has the pg_cron extension. Yo
The thing is, you can't just do SELECT operations from within these scheduled database actions: the result sets have noplace to go from that context. You can do
CREATE TABLE midnight_run AS SELECT whatever ...to place the results in a table. But then the results are in another table.If you want to get the results out of the DBMS, you'll need a UNIXish cron job or a Windowsish scheduled task running an appropriate application at midnight each day.
Pro tip Do your best to avoid scheduling stuff for precisely midnight. Many things run then. If you wait until a couple of minutes after the hour, your code is less likely to contend with other midnight code.