如何计数子行？

Question

我的sqlite数据库里有两张桌子--投标书和很多东西。

招标：

id
name
address

批次：

id
tender_id # it's tender's ID from table `tenders`
summ

一份标书可能有很多地段。如何计算有0批次的标书？

我尝试了一些查询，例如：

select lots.* from lots
inner join tenders on tenders.id = lots.tender_id
group by lots.id
having count(*) =0

和

SELECT COUNT(tenders.id) AS cnt FROM tenders, lots WHERE lots.tender_id = tenders.id group by tenders.id having cnt=0

没有结果。但当我试着做

select * from lots where tender_id=263128

因此，我看到0行。

谢谢。

Answer 1

SELECT tenders.id 
FROM tenders 
WHERE tenders.id NOT IN (SELECT tender_id FROM lots)

或不同数量的批次：

SELECT tenders.id
FROM tenders
WHERE tenders.id IN 
(
SELECT tender_id FROM lots GROUP BY tender_id HAVING COUNT(*) IN (2,3)
)

Answer 2

您使用连接的尝试看起来很有希望，但当然没有结果，因为您从很多地方进行选择。

我会把它变成一个left join，这样你就可以在很多地方检查null了

select
        tenders.id
    from tenders
    left join lots on tenders.id = lots.tender_id
    where lots.tender_id is null

或者，如果你想对照每一份标书的具体批次进行核对：

select
        tenders.id
    from tenders
    left join lots on tenders.id = lots.tender_id
    group by tenders.id
    having count(*) = 2

Answer 3

使用子查询：

SELECT id, (SELECT COUNT() FROM lots WHERE tender_id=tenders.id) AS LotCount FROM tenders;