作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我创建了以下简单的 DataModel
:
我用以下数据填充表格:
1) 表客户
INSERT INTO test.customer
(CustomerName, Country, RegistrationDate)
VALUES
("Customer A","DE","2015-05-03"),
("Customer B","US","2015-07-25"),
("Customer C","US","2016-02-15"),
("Customer D","DE","2017-09-21"),
("Customer E","AU","2018-12-07");
2) 表订单
INSERT INTO test.orders
(idCustomer, PaymentMethod, OrderDate, OrderValue)
VALUES
("1","CreditCard","2015-05-04","500"),
("1","PayPal","2015-11-18","200"),
("3","PayPal","2017-09-04","300"),
("2","Invoice","2018-04-30","100");
3) 表格条件
INSERT INTO test.criterias
(Country, MinimumOrderValue)
VALUES
("DE","300"),
("US","150"),
("AU","200");
之后,我创建了一个查询以根据条件 Country
和 OrderValue
获取 Customers
及其 Orders
:
SELECT
test.customer.idCustomer, CustomerName, Country,
test.orders.OrderValue
FROM test.customer
LEFT JOIN test.customer_info ON test.customer.idCustomer = test.customer_info.idCustomer
LEFT JOIN test.orders ON test.customer.idCustomer = test.orders.idCustomer
WHERE
Country = "US" AND OrderValue >= "150"
OR Country = "DE" AND OrderValue >= "300"
OR country = "AU" AND OrderValue >= "200";
到目前为止一切正常。
但是,与其在 SQL 查询的 WHERE
子句中包含条件,不如将它们存储在名为 Criterias
的单独表中。然后查询应该从该表中获取数据并像现在在上面的查询中一样应用它们。
我必须更改我的代码才能实现此目标吗?
最佳答案
不一定是答案;评论太长了...
一组有效的查询条件可能如下所示...
WHERE (country = "US" AND OrderValue >= 150)
OR (country = "DE" AND OrderValue >= 300)
OR (country = "AU" AND OrderValue >= 200);
...或者确实(虽然可能更慢)...
WHERE (country,ordervalue) IN(('US',150),('DE',300),('AU',200));
关于mysql - 从条件表中获取 WHERE 子句的条件,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54350789/
我是一名优秀的程序员,十分优秀!