如何在查询中使用AND参数使用AND参数

I have an oracle query that has several parameter, one of the parameter is like an array, the parameter name for example :COLOR. let say I have a table PEOPLE

我有一个oracle查询，它有几个参数，其中一个参数就像一个数组，参数名称例如：COLOR。假设我有一个桌子人

 ID    NAME   WARECOLOR
 12   Sabrina   red
 12   Sabrina   blue
 32   Mark      blue
 45   Bob       red 
 25   Lean      white
 01   Sara      Orange

I want to use this parameter in two way in two different queries, one using the OR condition and the other one using the AND condition. let say I want all the names who have "Red OR blue" If I send the parameter into :COLORS to be as

我想在两个不同的查询中以两种方式使用此参数，一个使用OR条件，另一个使用AND条件。假设我想要所有具有“红色或蓝色”的名称如果我将参数发送到：颜色为

|red|blue|

and write this query

并编写此查询

SELECT ID, NAME, COLOR
FROM PEOPLE
WHERE  INSTR(:COLORS, COLORS) > 0
GROUP ID, NAME, COLOR;

The result will be like:

结果如下：

 ID    NAME   WARECOLOR
 12   Sabrina   red
 12   Sabrina   blue
 32   Mark      blue
 45   Bob       red

which is good, but my question is: how can I use it to be AND condition? Like if I want all the names who have "Red AND blue", which means the result should be like:

这是好的，但我的问题是：我怎么能用它来和条件？就像我想要所有拥有“红色和蓝色”的名字一样，这意味着结果应该是这样的：

 ID    NAME   WARECOLOR
 12   Sabrina   red
 12   Sabrina   blue

could any one help me through this case please?

我可以帮助我解决这个案子吗？

2 个解决方案

#1

To find the persons, you can:

要找到这些人，您可以：

SELECT ID, NAME
FROM PEOPLE
WHERE  INSTR(:COLORS, WARECOLOR) > 0
GROUP ID, NAME
HAVING COUNT(*) = regexp_count(:COLORS,'[|]') - 1 ;

regexp_count(:COLORS,'[|]') just counts the | from :COLORS. If your table is not properly normalized(you have duplicates) you may use count(distinct WARECOLOR) instead of count(*)

regexp_count（：COLORS，'[|]'）只计算|来自：颜色。如果您的表没有正确规范化（您有重复），您可以使用count（distinct WARECOLOR）而不是count（*）

Then it is simple to get the rows:

然后获取行很简单：

SELECT ID, NAME, COLOR
FROM PEOPLE
WHERE  id, name in (
    SELECT ID, NAME
    FROM PEOPLE
    WHERE  INSTR(:COLORS, WARECOLOR) > 0
    GROUP ID, NAME
    HAVING COUNT(*) = regexp_count(:COLORS,'[|]') - 1 ;
 )
;

#2

You can try below SQL script on your SQL Server development environment. I used the user defined function dbo.split() to split input string using SQL

您可以在SQL Server开发环境中尝试以下SQL脚本。我使用用户定义的函数dbo.split（）来使用SQL拆分输入字符串

/*create table colorTbl (
ID int,   NAME   varchar(10), WARECOLOR varchar(10)
)
insert into colorTbl select 12,'Sabrina','red'
insert into colorTbl select 12   ,'Sabrina','blue'
insert into colorTbl select  32   ,'Mark','blue'
insert into colorTbl select  45   ,'Bob','red' 
insert into colorTbl select  25   ,'Lean','white'
insert into colorTbl select  01   ,'Sara','Orange'*/

declare @color varchar(max) = '|red|blue|'

;with cte as (
    select val, COUNT(*) over (partition by 1) cnt1
    from dbo.Split(@color,'|')
    where val <> ''
)
select * 
from (
select *, COUNT(*) over (partition by name) cnt2
from colorTbl c
inner join cte on c.warecolor = cte.val
) t where cnt1 = cnt2

The split function returns colors and order number of each color. This enables you to know the count of the input parameters. Using SQL Count() function with Partition By clause enables me count rows with colors matched for each user. When I match these two count values, you will implement AND for concatenated parameter values.

split函数返回每种颜色的颜色和顺序号。这使您可以知道输入参数的计数。使用带有Partition By子句的SQL Count（）函数可以计算每个用户匹配颜色的行。当我匹配这两个计数值时，您将为连接的参数值实现AND。

#1