mysql - MySQL从两个字段查询三种状态的逻辑
问题描述
我有一个简单的订单,order_items,order_status 模式:
可以看出,order可以有几个order_items。每个 order_item 有两个(布尔)字段,checked_in 和 verify。
订单的状态采用值 ORDERED、RECEIVING、RECEIVED 和 CANCELED。
签入或验证 order_item 后,我想创建一个数据库触发器(在 order_items 表上),它为每个 order_item 查询 check_in 和已验证字段的值。
如果未设置任何字段,则订单状态为“已订购”。如果设置了所有字段,则订单状态为“已收到”。如果介于两者之间,则订单状态为“正在接收”。
当前状态设置为“手动”,通过运行以下查询
SELECT oi.checked_in, oi.verified
FROM order_items AS oi
WHERE order_id = 54;
产生这样的结果:
完整的代码如下所示:
dsl_shared_ptr<TSQLQuery> q(new TSQLQuery(NULL));
q->SQLConnection = LITDBConnectionDM->SQLConnection1;
int orderID = OrdersCDS->FieldByName("id")->AsInteger;
stringstream query;
query <<
"\
SELECT oi.checked_in, oi.verified \
FROM order_items AS oi \
WHERE order_id = :oID";
q->SQL->Add(query.str().c_str());
q->Params->ParamByName("oID")->AsInteger = orderID;
q->Open();
q->First();
int checkedInCount(0);
int verifiedCount(0);
int recordCount(0);
if(!q->IsEmpty())
{
while(!q->Eof)
{
recordCount++;
if(q->FieldByName("checked_in") && q->FieldByName("checked_in")->AsInteger)
{
checkedInCount++;
}
if(q->FieldByName("verified") && q->FieldByName("verified")->AsInteger)
{
verifiedCount++;
}
q->Next();
}
}
string orderStatus("");
if(checkedInCount == 0 && verifiedCount == 0)
{
orderStatus = "ORDERED";
}
else if(checkedInCount == verifiedCount && checkedInCount == recordCount)
{
orderStatus = "RECEIVED";
}
else
{
orderStatus = "RECEIVING";
}
int order_status = getIDForOrderStatus(orderStatus);
OrdersCDS->Edit();
OrdersCDS->FieldByName("status")->AsInteger = order_status;
OrdersCDS->Post();
OrdersCDS->ApplyUpdates(0);
可以看出,我通过检查每个 order_item 的已验证、checked_in 标志来“手动”推断订单状态。
如何在触发器内的 SQL 查询中实现上述功能?
解决方案
基本上我认为update
您要编写的查询是:
update orders o
inner join (
select
order_id,
max(checked_in + verified) max_verif,
min(checked_in + verified) min_verif
from order_items AS oi
where order_id = 54
group by order_id
) oi on oi.order_id = o.order_id
inner join order_status s on s.status = case
when max_verif = 0 then 'ORDERED'
when min_verif = 2 then 'RECEIVED'
else 'RECEIVING'
end
set o.status = s.id
逻辑是聚合订单项;我们可以通过查看每个项目的两个状态的min()
和来决定应该为订单分配哪个状态。max()
然后我们带上status
表格,将状态的描述翻译成相关的主键值。
请注意,存储此信息不一定是一个好主意。当订单项目的状态发生变化时(或者当新项目添加到订单中,如果在您的用例中可能的话),需要额外的努力来使信息保持最新。
从上面的查询开始,您可以很好地创建一个视图,它可以在查询时即时计算信息。
推荐阅读
- linux - 每日错误:无法以附加模式打开流或文件“.../storage/logs/*”:无法打开流:权限被拒绝
- amazon-s3 - 邮递员中的预签名网址失败
- javascript - 从服务器发出的Javascript套接字然后从客户端监听不起作用
- google-apps-script - 如何使用 Google Apps 脚本获取和设置当前周日期(星期日)
- c# - 在 Blazor 中创建本地表
- angular8 - ngx-extended-pdf-viewer 打印问题
- dynamics-crm - 相关记录的 Dynamics CRM 约会列表
- kubernetes - 以其他 clusterIP 为目标的端点 IP 地址
- python - 计算飞镖分数结帐python的最短路径
- .htaccess - .htaccess 重写包含文件名的 URL