需要编辑具有多列的SQL搜索查询

Question

我有一个搜索查询，包括一个表和两列。

Select * 
from Gecoserv _a 
where _a.typeOfEcosystem IN (:typeNameList) 
    AND _a.service IN (:serviceNameList)

它工作，但没有给出我想要的结果

假设:

typeNameList = { Freshwater, Saltwater, Dunes }
serviceNameList = {Habitat, Food, Recreation }

我希望网格只显示单一的组合(淡水-栖息地，咸水-食物和沙丘-娱乐等)，但它也显示其他组合(例如:淡水-娱乐)。

也就是说:查询结果显示

[0,0],[0,1],[0,2],
[1,0],[1,1],[1,2],
[2,0],[2,1],[2,2].

但是我只想看:[0,0],[1,1],[2,2] .

我知道我必须在查询的末尾添加一些东西。我尝试了很多方法，但没有找到一个完美的解决方案。

有谁能帮我做这件事吗?

问候,

Melih

Answer 1

没有任何支持模式，甚至没有数据库制造商，我的回答将不得不对您的设置做出一些假设。

这听起来很像笛卡尔积。你的问题的关键是你的IN从句都是相互独立的。因此，一条记录可以满足这两个条件，但不是您希望在结果集中看到的记录。作为一个例子，假设我们有如下的记录:

id   name          typeOfEcosystem  service
--   ------------  ---------------  ----------
 1   myEcoService  Freshwater       Recreation

你的查询，正如它现在写的，将首先接受typeOfEcosystem(淡水)并检查它是否在你的列表中，它是。结果:真正的

然后，查询将获取服务(Recreation)并检查它是否在第二个列表中。再次找到值。结果:真正的

所有条件求值为true，因此该行包含在最终结果集中。

<<p> 解决方案/strong>

听起来你想要一个多列IN子句。请注意，这与您的尝试不同，因为最终解决方案应该只涉及单个IN关键字。现在，查询的确切形式将在很大程度上取决于您的数据库及其提供的内容。一个适用于任何sql兼容数据库的解决方案是使用字符串操作。一个粗略的例子如下:

Select * 
  from Gecoserv _a 
 where _a.typeOfEcosystem || '-' || _a.service IN ('Freshwater-Habitat', 'Saltwater-Food', 'Dunes-Recreation')

在这个例子中，'||'被假定为连接。这是非常便携的，但它很慢。可以更优雅地解决这个问题，但确切的解决方案将更多地取决于您的特定数据库和需求。例如，Oracle中的多列in子句可能像这样简单:

Select * 
  from Gecoserv _a 
 where (_a.typeOfEcosystem, _a.service) IN (('Freshwater', 'Habitat'), ('Saltwater', 'Food'), ('Dunes', 'Recreation'));

您可能需要对这些解决方案中的任何一个进行调整，以使它们与您的DBMS很好地配合。进一步的技巧(和简单的阅读)可以在以下网址找到:

1. 选择使用SQLite重复列组合的记录

Answer 2

我认为这是不可能的，当这是你唯一的信息。参数值之间的关系，是它们在列表中的位置。在SQL中没有关联和/或保留这些位置的选项。

你对创建sql语句有什么影响?

如果您可以在代码中遍历列表并动态创建sql，则可以得到这样的解决方案:

SELECT * FROM Gecoserv 
  WHERE typeOfEcosystem = 'Freshwater' AND service = 'Habitat'
UNION
SELECT * FROM Gecoserv 
  WHERE typeOfEcosystem = 'Freshwater' AND service = 'Food'
UNION
...

关于如何参数化这个的例子，请看下面的答案。

另一种方法:分割字符串

你的主要问题是保持参数在其列表中的"排名"/"位置"。这可以通过将列表替换为字符串并将它们分开来实现。

我在sql中使用了分割字符串的示例。

结果查询(使用这个Fiddle)看起来像:

DECLARE 
  @ecoSystems varchar(100) = 'Freshwater,Saltwater,Dunes',
  @services varchar(100) = 'Habitat,Food,Recreation',
  @separator char(1) = ','
SELECT
  [g].[id],
  [g].[typeOfEcoSystem],
  [g].[service]
FROM [dbo].[Split](@separator, @ecoSystems) [e]
INNER JOIN [dbo].[Split](@separator, @services) [s] 
  ON [e].[position] = [s].[position]
INNER JOIN [Gecoserv] [g] 
  ON [e].[part] = [g].[typeOfEcoSystem]
    AND [s].[part] = [g].[service]
ORDER BY [id] ASC

这是否适用于您的场景?(答案:是的，几乎…)

最后没有函数

DECLARE 
  @ecoSystems varchar(100) = 'Freshwater,Saltwater,Dunes',
  @services varchar(100) = 'Habitat,Food,Recreation',
  @separator char(1) = ',';
WITH [EcoSystemsAndServices]([posE], [startE], [endE], [posS], [startS], [endS])
AS 
(
  SELECT 
    1, 
    1, 
    CHARINDEX(@separator, @ecoSystems),
    1, 
    1, 
    CHARINDEX(@separator, @services)
  UNION ALL
  SELECT 
    [posE] + 1, 
    [endE] + 1, 
    CHARINDEX(@separator, @ecoSystems, [endE] + 1),
    [posS] + 1, 
    [endS] + 1, 
    CHARINDEX(@separator, @services, [endS] + 1)
  FROM [EcoSystemsAndServices]
  WHERE [endE] > 0
)

SELECT 
  [g].[id],
  [g].[typeOfEcoSystem],
  [g].[service]
FROM [Gecoserv] [g]
INNER JOIN [EcoSystemsAndServices] [ess] 
  ON [g].[typeOfEcoSystem] = SUBSTRING(@ecoSystems, 
                                        [startE], 
                                        CASE WHEN [endE] > 0 THEN [endE] - [startE] ELSE 100 END)
    AND [g].[service] = SUBSTRING(@services, 
                                  [startS], 
                                  CASE WHEN [endS] > 0 THEN [endS] - [startS] ELSE 100 END)
ORDER BY [id] ASC