mysql中的数据统计后如何求和?

huhu • 5 年前 • 1410 次点击

第二个是表格B。请帮帮我。我在mysql中有这个表:

表a

sentid  |   eventid  |  a  |   sentiment  |
-------------------------------------------
1       |    10      | yes |   pos        |
2       |    10      | so  |   neu        |
3       |    11      | me  |   neu        |
4       |    11      | luv |   pos        |
5       |    11      | so  |   neu        |

 bid    |   eventid  |  b  |   sentiment  |
-------------------------------------------
1       |    11      | hee |   pos        |
2       |    11      | yup |   neu        |
3       |    10      | so  |   neu        |
4       |    10      | ha  |   neu        |
5       |    10      | no  |   neu        |

eventid  |  sentiment   | cnt |
-------------------------------
 10      |   pos        | 1   |
 10      |   neu        | 1   |  #from tablea
 10      |   neu        | 3   |  #from tableb

期望输出

eventid  |  sentiment   | cnt |
-------------------------------
 10      |   pos        | 1   |
 10      |   neu        | 4   |

我的代码:

select id,sentiment,count(*) as cnt from tablea WHERE id=10 group by sentiment 
union all 
select id,sentiment,count(*) as cnt from tableb WHERE id=10 group by sentiment

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/53966

1410 次点击

文章 [ 2 ] | 最新文章 5 年前

• 1 楼

Tim Biegeleisen 5 年前

你应该先联合,然后聚合:

SELECT
    eventid,
    sentiment,
    COUNT(*) AS cnt
FROM
(
    SELECT eventid, sentiment FROM tablea
    UNION ALL
    SELECT eventid, sentiment FROM tableb
) t
-- WHERE eventid = 10     -- optional
GROUP BY
    eventid,
    sentiment;

• 2 楼

Thorsten Kettner 5 年前

先联合,然后聚合。

select eventid, sentiment, count(*) as cnt
from
(
  select eventid, sentiment from tablea
  union all
  select eventid, sentiment from tableb
) unioned
group by eventid, sentiment
order by eventid, sentiment;

如果你想为一个 eventid 只有,那么地方 where eventid = 10 之后 from tablea 和 from tableb group by ,但速度可能会慢一些。

登录后回复