如何使用COUNT来获取mysql的零值？

I read several questions about this issue on * : It seems that COUNT should be used with the right Joining to display sums of every items, including the ones summing zero.

我在*上读了几个关于这个问题的问题：似乎COUNT应该与右连接一起使用来显示每个项目的总和，包括总和为零的项目。

I'm not really able to make it with my case, after several hours of headache...

经过几个小时的头痛后，我真的无法用我的情况做出来......

Well, I have 2 tables. The first one is called "words2", with a list of words. The second one is called "links2". It's linking together two words : idWord1 and idWord2. (There are no links linking together two identical words)

好吧，我有2张桌子。第一个被称为“words2”，带有一个单词列表。第二个叫做“links2”。它将两个单词连接在一起：idWord1和idWord2。（没有链接将两个相同的单词链接在一起）

For each word, I would like to know how many links are used, even if there is no link.

对于每个单词，我想知道使用了多少链接，即使没有链接。

This is my query :

这是我的查询：

SELECT *, COUNT(*) AS qty  
FROM ( 
    SELECT *
    FROM words2
    LEFT OUTER JOIN links2 AS linksA ON words2.idWord = linksA.idWord1 

    UNION

    SELECT *
    FROM words2
    LEFT OUTER JOIN links2 AS linksB ON words2.idWord = linksB.idWord2
) AS tmp
WHERE idUser = 3 AND linkType = 'individual'
GROUP BY word ORDER BY word

It works fine unless I don't have any results for the unused words, which are not displayed.

它工作正常，除非我没有显示未使用的单词的任何结果。

Thank you very much for your help!

非常感谢您的帮助！

2 个解决方案

#1

To do this with your original query change the count call to COUNT(idWord1). This will cause it to count the number of times idWord1 is NOT NULL. Right now it is counting the number of rows period so you get a 1 where you should get a zero.

要使用原始查询执行此操作，请将计数调用更改为COUNT（idWord1）。这将导致它计算idWord1为非NULL的次数。现在它正在计算行数周期，所以你得到1你应该得到零。

Here's my sample dataset:

这是我的样本数据集：

words2
-------
idWord
-------
foo
bar
baz
biz
buzz

links2
-------
idWord1 | idWord2
-------
foo     | bar
foo     | baz
bar     | baz
buzz    | foo
buzz    | bar

(This dataset disregards the idUser and linkType fields because your original question doesn't describe how they are used and they don't appear to be relevant to the answer.)

（此数据集忽略idUser和linkType字段，因为您的原始问题未描述它们的使用方式，并且它们似乎与答案无关。）

When I run your query on my dataset I get this:

当我在我的数据集上运行查询时，我得到了这个：

idWord | idWord1 | idWord2 | linkCount
--------------------------------------
bar    | bar     | baz     | 3
baz    | NULL    | NULL    | 2
biz    | NULL    | NULL    | 1
buzz   | buzz    | foo     | 2
foo    | foo     | bar     | 3

Also note that COUNT(*) will be more expensive depending upon the storage engine you're using. See this other question for details.

另请注意，COUNT（*）将更昂贵，具体取决于您使用的存储引擎。有关详细信息，请参阅其他问题

When I change the count to COUNT(idWord1) i get this:

当我将计数更改为COUNT（idWord1）时，我得到：

idWord | idWord1 | idWord2 | linkCount
--------------------------------------
bar    | bar     | baz     | 3
baz    | NULL    | NULL    | 2
biz    | NULL    | NULL    | 0
buzz   | buzz    | foo     | 2
foo    | foo     | bar     | 3

Here's an even simpler query that uses no subquery and joins words2 to links2 using an OR statement:

这是一个更简单的查询，它不使用子查询，并使用OR语句将words2连接到links2：

SELECT
  words2.idWord
  -- this will count the number of links to each word
  -- if there are no links the COUNT() call will return 0
  , COUNT(idWord1) AS linkCount
FROM words2
  LEFT JOIN links2
    ON words2.idWord = links2.idWord1
      OR words2.idWord = links2.idWord2
GROUP BY words2.idWord
ORDER by words2.idWord

When run on the sample dataset I get the following results:

在样本数据集上运行时，我得到以下结果：

idWord | linkCount
-------------------
bar    | 3
baz    | 2
biz    | 0
buzz   | 2
foo    | 3

#2

SELECT
    w.idWord
  , ( SELECT COUNT(*) 
      FROM links2 AS l
      WHERE l.idWord1 = w.idWord
    ) +
    ( SELECT COUNT(*) 
      FROM links2 AS l
      WHERE l.idWord2 = w.idWord
    ) AS linkCount
FROM words2 AS w

要么

SELECT
    w.idWord
  , ( SELECT COUNT(*) 
      FROM links2 AS l
      WHERE l.idWord1 = w.idWord
         OR l.idWord2 = w.idWord
    ) AS linkCount
FROM words2 AS w

#1