选择列值以字符串开头且不以字符串开头的行 [英] Select a row where column value start with string and does not start with string

问题描述

考虑以下几行

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<>小提琴

这篇关于选择列值以字符串开头且不以字符串开头的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！