SQLite 比较 2 个表的查询

Question

我想创建一个新查询来了解商店中有多少产品。

表:tb_store

    +--------+------------+------------------+-----------+
    | item_id| nome       |  data_out        |   data_in |
    +--------+------------+------------------+-----------+
    | 1      | Produ1     | null             | 2015-01-06|
    | 2      | Produ1     | null             | 2015-01-06|
    | 3      | Produ3     | null             | 2015-01-06|
    | 4      | Produ3     | null             | 2015-01-06|
    | 5      | Produ5     | null             | 2015-01-06|
    | 6      | Produ4     | 2015-01-06       | 2015-02-06|
    | 7      | Produ2     | 2015-01-06       | 2015-02-06|
    +--------+------------+------------------+-----------+

表:tb_product

+--------+------------+
| item_id| nome       |
+--------+------------+
| 1      | Produ1     |
| 2      | Produ2     |
| 3      | Produ3     |
| 4      | Produ4     |
| 5      | Produ5     |
+--------+------------+

我写了这个查询:

select nome, count(nome) as pezzi from  tb_store where data_out is null or data_out="" group by nome order by pezzi desc

结果是:

+--------+------------+
| nome   | pezzi      |
+--------+------------+
| Produ1 | 2          |
| Produ3 | 2          |
| Produ5 | 1          |
+--------+------------+

我想获得这个结果:

+--------+------------+
| nome   | pezzi      |
+--------+------------+
| Produ1 | 2          |
| Produ3 | 2          |
| Produ5 | 1          |
| Produ2 | 0          |
| Produ4 | 0          |
+--------+------------+

可能吗？如何重写查询？

编辑

我创建了一个像这样的新查询:

    SELECT DISTINCT nome, pezzi FROM(
SELECT nome,COALESCE(pezzi,0)as pezzi FROM(
SELECT p.nome, COUNT(s.nome) as pezzi
FROM tb_product as p LEFT JOIN tb_store as s
ON   p.nome = s.nome
WHERE s.data_out is null
OR    s.data_out = ""
GROUP BY p.nome
union
select nome,null as pezzi from tb_product) )
ORDER BY pezzi DESC

但我有一些重复的项目需要删除......结果是

nome         pezzi
crema         2
pistacchio    2
zabajone      2
bacio         1
cassata       1
cioccolato    1
ciocco rum    1
malaga        1
mango         1
mascarpone    1
nocciola      1
stracciatella 1
bacio         0
caramello     0
cassata       0
cioccolato    0
ciocco rum    0
crema         0
fragola       0
limone        0
malaga        0
mango         0
mascarpone    0
nocciola      0
pistacchio    0
zabajone      0

是否可以删除重复的0？

SELECT DISTINCT nome, pezzi FROM(
SELECT nome,COALESCE(pezzi,0)as pezzi FROM(
SELECT p.nome, COUNT(s.nome) as pezzi
FROM tb_product as p LEFT JOIN tb_store as s
ON   p.nome = s.nome
WHERE s.data_out is null
OR    s.data_out = ""
GROUP BY p.nome
union
select nome,null as pezzi from tb_product) GROUP BY nome ) 
ORDER BY pezzi DESC

Answer 1

您需要在两个表之间进行左连接，例如:

SELECT p.nome, COUNT(s.nome) as pezzi
FROM tb_product as p LEFT JOIN tb_store as s
ON   p.name = s.name
WHERE data_out is null
OR    data_out = ""
GROUP BY p.nome
ORDER BY pezzi DESC