SQL / SQlite嵌套select in having子句

I was playing a bit with sqlite and encountered the following problem: it looks like it is not possible to nest a subquery in a having clause

我正在玩sqlite并遇到以下问题：看起来无法在子句中嵌套子查询

When I try to call a query with:

当我尝试使用以下命令调用查询时：

... having count(select * from produkt) > 1

I get the error:

我收到错误：

OperationalError: near "select": syntax error

OperationalError：接近“select”：语法错误

While executing

执行时

 ... having count(1) > 1

everything is fine

一切安好

Would you have any workarounds for that?

你有任何解决方法吗？

edit2:

EDIT2：

I want to write this:

我想写这个：

select distinct standort.name, standort.ort
from produktion
    join standort on id_standort = standort.id
    join produkt on id_produkt = produkt.id
where produkt.gewicht < 1

EXCEPT

select distinct standort.name, standort.ort
from produktion
    join standort on id_standort = standort.id
    join produkt on id_produkt = produkt.id
where produkt.kategorie = "Spiel"

In a more elegant way, using "having"

以更优雅的方式，使用“拥有”

Cheers and thanks a lot!

干杯，非常感谢！

Wojtek

WOJTEK

2 个解决方案

#1

I think this fits your intention better than your current select

我认为这比你当前的选择更符合你的意图

SELECT ... 
FROM Standort
     JOIN produktion ...
     JOIN produkt ...
WHERE product.kategorie != "Spiel" AND produkt.gewicht > 1

#2

I'm not sure in what context you would do this, but this is syntactically correct:

我不确定你会在什么情况下这样做，但这在语法上是正确的：

having (select count(*) from produkt) > 1

EDIT:

编辑：

For your actual question, this query is simpler:

对于您的实际问题，此查询更简单：

select s.name, s.ort
from produktion pn join
     standort s
     on pn.id_standort = s.id join
     produkt p
     on pn.id_produkt = p.id
group by s.name, s.ort
having sum(case when p.gewicht < 1 then 1 else 0 end) > 0 and
       sum(case when p.kategorie = 'Spiel' then 1 else 0 end) = 0;

This returns all s.name/s.ort combinations that have a "gewicht" less than 1 and no "kategorie" called 'Spiel'.

这将返回所有s.name/s.ort组合，其中“gewicht”小于1而没有“kategorie”称为“Spiel”。

#1