代码之家  ›  专栏  ›  技术社区  ›  Travis Heeter

如何将不同查询的结果合并为一个?

db2
  •  0
  • Travis Heeter  · 技术社区  · 7 年前

    我在一个专栏中有情绪分析的结果。我想展示每个数字代表的内容和计数,如下所示:

     Neg | Neutral | Pos 
    ---------+-----+-----
      30 |   55    | 100 
    

    我在网上找到了很多不同的方法,但都没有用。db2语法似乎经常更改,我发现的所有文档在我当前的bluemix帐户上都不起作用。

    我能得到的最接近的是联合:

    SELECT  COUNT(*) as NEGATIVE      FROM C7  WHERE SENTIMENT = 1
    union
    SELECT  COUNT(*) as NEUTRAL       FROM C7  WHERE SENTIMENT = 2
    union
    SELECT  COUNT(*) as Pos           FROM C7  WHERE SENTIMENT = 3
    

    然而,这给了我:

    NEGATIVE
    --------
       30
       55
      100
    

    如何并排组合每个查询的结果?


    我在DB2中尝试了post-Pivoting的答案,但出现了错误:

    SELECT 
      C7.SENTIMENT,
      COUNT(CASE WHEN C7.SENTIMENT = 1 THEN Value END) AS NEGATIVE,
      COUNT(CASE WHEN C7.SENTIMENT = 2 THEN Value END) AS NEUTRAL,
      COUNT(CASE WHEN C7.SENTIMENT = 3 THEN Value END) AS POSITIVE,
    FROM C7
    GROUP BY C7.SENTIMENT
    

    SQL0206N:SQL0206N“值”在使用它的上下文中无效。SQLSTATE=42703

    SELECT DISTINCT
      C7.SENTIMENT,
        (SELECT value  FROM C7  WHERE C7.SENTIMENT = 1) AS VERY_NEGATIVE,
        (SELECT value  FROM C7  WHERE C7.SENTIMENT = 2) AS NEGATIVE,
        (SELECT value  FROM C7  WHERE C7.SENTIMENT = 3) AS NEUTRAL
    FROM C7
    

    1 回复  |  直到 7 年前
        1
  •  1
  •   Travis Heeter    7 年前

    SELECT DISTINCT
      (SELECT COUNT(SENTIMENT) FROM C7 WHERE C7.SENTIMENT = 1) AS NEGATIVE,
      (SELECT COUNT(SENTIMENT) FROM C7 WHERE C7.SENTIMENT = 2) AS NEUTRAL,
      (SELECT COUNT(SENTIMENT) FROM C7 WHERE C7.SENTIMENT = 3) AS POSITIVE
    FROM C7
    

    这似乎是我徘徊了很长时间,但找不到的东西,因为一些帖子用奇怪的表名和值回答了这个问题。

    因此,基本上,要使查询彼此相邻,必须使用多个 SELECT s

    另外,请注意,我使用了 DISTINCT