从条件表获取WHERE子句的条件 [英] Getting conditions for WHERE clause from a table of criterias
问题描述
我创建了以下简单的DataModel
:
我用以下数据填充了表格:
And I filled the tables with the following Data:
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
:
Afterwards, I created a query to get Customers
and their Orders
based on the criterias Country
and OrderValue
:
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";
到目前为止,所有这些都工作正常.
All this works fine so far.
但是,与其在SQL查询的WHERE
子句中不包含这些条件,不如将它们存储在一个名为Criterias
的单独表中.然后,查询应该从该表中获取数据,并像上面的查询中一样完全应用它们.
However, instead of having the criterias in the WHERE
clause within the SQL query they should be stored in a seperate table called Criterias
. The query should then get the datas from this table and apply them exactly like it does now in the query above.
要实现此目的,我需要在代码中进行哪些更改?
What do I have to change in my code to achieve this?
推荐答案
您只需join
表或在where
子句中使用子查询.
You would just join
the tables or use a subquery in the where
clause.
后者更易于键入,因此:
The latter is easier to type, so:
WHERE EXISTS (SELECT 1
FROM Criterias cr
WHERE cr.Country = c.Country AND
cr.MinOrderValue <= o.OrderValue
)
这篇关于从条件表获取WHERE子句的条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!