在WHERE子句中声明用于IN条件的变量。

17 浏览
0 Comments

在WHERE子句中声明用于IN条件的变量。

我有以下表格，你也可以在SQL Fiddle 这里找到：

CREATE TABLE Flows (
    Flow_Date DATE,
    Product TEXT,
    FlowType TEXT,
    Country TEXT,
    Quantity VARCHAR(255)
);
INSERT INTO Flows
(Flow_Date, Product, FlowType, Country, Quantity)
VALUES 
("2019-05-23", "Product A", "Inbound", "DE", "400"),
("2019-05-23", "Product A", "Inbound", "DE", "400"),
("2019-05-23", "Product B", "Inbound", "NL", "500"),
("2019-05-23", "Product B", "Inbound", "NL", "500"),
("2019-05-23", "Product A", "Outbound", "FR", "300"),
("2019-05-23", "Product A", "Outbound", "FR", "300"),
("2019-05-23", "Product B", "Outbound", "US", "200"),
("2019-05-23", "Product B", "Outbound", "US", "200"),
("2019-05-24", "Product A", "Inbound", "DE", "900"),
("2019-05-24", "Product A", "Inbound", "DE", "900"),
("2019-05-24", "Product B", "Inbound", "NL", "800"),
("2019-05-24", "Product B", "Inbound", "NL", "800"),
("2019-05-24", "Product A", "Outbound", "FR", "650"),
("2019-05-24", "Product A", "Outbound", "FR", "650"),
("2019-05-24", "Product B", "Outbound", "US", "450"),
("2019-05-24", "Product B", "Outbound", "US", "450"),
("2019-05-25", "Product A", "Inbound", "DE", "900"),
("2019-05-25", "Product A", "Inbound", "DE", "900"),
("2019-05-25", "Product B", "Inbound", "NL", "800"),
("2019-05-25", "Product B", "Inbound", "NL", "800"),
("2019-05-25", "Product A", "Outbound", "FR", "650"),
("2019-05-25", "Product A", "Outbound", "FR", "650"),
("2019-05-25", "Product B", "Outbound", "US", "450"),
("2019-05-25", "Product B", "Outbound", "US", "450"),
("2019-05-26", "Product A", "Inbound", "DE", "900"),
("2019-05-26", "Product A", "Inbound", "DE", "900"),
("2019-05-26", "Product B", "Inbound", "NL", "800"),
("2019-05-26", "Product B", "Inbound", "NL", "800"),
("2019-05-26", "Product A", "Outbound", "FR", "650"),
("2019-05-26", "Product A", "Outbound", "FR", "650"),
("2019-05-26", "Product B", "Outbound", "US", "450"),
("2019-05-26", "Product B", "Outbound", "US", "450");

我使用以下查询语句从这个表中获取数据：

SELECT Flow_Date, Product, FlowType, Country
FROM Flows
WHERE Flow_Date BETWEEN ("2019-05-23 00:00:00") AND ("2019-05-25 23:59:59")
AND Country IN ("DE","NL")
GROUP BY 1,2,3,4;

到目前为止一切正常。

然而，现在我想为语句中的WHERE子句中的IN条件设置一个变量。

因此，我尝试使用以下代码：

SET @country = ("DE", "NL");
SELECT Flow_Date, Product, FlowType, Country
FROM Flows
WHERE Flow_Date BETWEEN ("2019-05-23 00:00:00") AND ("2019-05-25 23:59:59")
AND Country IN @country
GROUP BY 1,2,3,4;

然而，我得到了错误信息Operand should contain 1 column(s)。

我需要更改代码中的什么才能使其工作？

0

1 答案

0 Comments

问题的原因是在WHERE子句中，使用了FIND_IN_SET函数来检查Country列是否在指定的字符串中存在。然而，在代码中声明的变量中，没有给出具体的值。

解决方法是在代码中声明变量，并将其赋予指定的值，以便FIND_IN_SET函数能够正确地检查Country列的值是否存在于变量中。以下是修复后的代码：

SET @countries = "DE,NL";
SELECT DISTINCT Flow_Date, Product, FlowType, Country
FROM Flows
WHERE Flow_Date BETWEEN ("2019-05-23 00:00:00") AND ("2019-05-25 00:00:00")
  AND FIND_IN_SET(Country, @countries);

通过声明并赋值@countries变量，代码现在可以正确地检查Country列的值是否存在于变量中。这将解决原始问题，并确保查询按预期工作。

0