选择列值以字符串开头且不以字符串开头的行

问题描述：

考虑以下几行

name    StringValue
foo     Mac123

foo     Windows123

foo     Andorid123

bar     Windows123

bar     Andorid123

dumb    Windows123

这是从下面查询返回的结果元组:

This is the result tuples returned from query below:

select name, stringValue from TABLE1 A, TABLE2 B, TABLE13 C
where A.id = B.id
and B.id = C.id
GROUP BY name, stringValue

如何选择这样的名称的stringValue 从未包含以Mac"开头的字符串，但还需要包含以Windows"开头的值的名称

How do I select the name where the stringValue of such name never contained a string that start with "Mac", but also needs to contains the value that start with "Windows"

这应该返回

name
bar
dumb

我有什么:

select name, stringValue from TABLE1 A, TABLE2 B, TABLE13 C
where A.id = B.id
and B.id = C.id
GROUP BY name, stringValue
Having stringValue LIKE "Windows" and stringValue not LIKE "Mac"

问题:上述查询返回以名称Windows"开头的所有列.因为如果前半条件为真，后半条件也为真.

Problem: Above query returns all columns that start with the name "Windows". Since if the first half condition is true, the second half is true as well.

我认为问题与我没有按我想要的那样工作有关..

I think the problem has to do with my having by not working as I wanted it too..

如何修改查询，使其返回所有名称，其中具有此名称的任何列的字符串值不以 Mac 开头，但也以 Windows 开头.

How do I modify the query so it returns all the names where any column with this name, will have a string value that does not start with Mac, but also start with Windows.

答

为此无需联接.具有特定 HAVING 子句的聚合应该这样做:

There is no need of joins for this. Aggregation with a specific HAVING clause should do it:

SELECT name
FROM t
GROUP BY name
HAVING COUNT(CASE WHEN stringvalue LIKE 'WINDOWS%' THEN 1 END) > 0
AND    COUNT(CASE WHEN stringvalue LIKE 'MAC%'     THEN 1 END) = 0;

DB<>小提琴

选择列值以字符串开头且不以字符串开头的行

相关推荐