GROUP BY WITH HAVING (DISTINCT) : PHP, MYSQL

本文介绍了GROUP BY WITH HAVING (DISTINCT) : PHP, MYSQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 id | mid | pid | owgh | nwgh | 1 3 12 1.5 0.6 2 3 12 1.5 0.3 3 3 14 0.6 0.4 4 3 15 1.2 1.1 5 4 16 1.5 1.0 6 4 17 2.4 1.2 7 3 19 3.0 1.4

从上面我想要 nwgh 的中间和总和及其相应的总数.id 例如:mid=3 或 mid=4但使用 DISTINCT pid 但请注意 nwgh 的总和不应为 DISTINCT

From Above i want total count of mid AND SUM of nwgh with its resp. id ex: mid=3 or mid=4 but with DISTINCT pid but please note sum of nwgh should not be DISTINCT

因此我的结果如下:

mid | countmid | totalnwgh 3 4 (DISTINCT value) 3.8 (no DISTINCT it take both value of pid =12) 4 2 2.2

在上面的结果 mid = 3 有计数 4 因为 pid = 12 重复，因此它的 DISTINCT 但 nwgh 不应该是 DISTINCT ，它的总数

in above result mid = 3 have count 4 beause pid = 12 is repeated hence its DISTINCT but nwgh should not be DISTINCT , its total count

我尝试过的

Select mid , COUNT(mid) as countmid , SUM(nwgh) as totalnwgh from test where mid = 3 GROUP BY mid HAVING count(DISTINCT pid)

推荐答案

如果我明白你想要什么，你只需要在你的 COUNT 中做一个 distinct.

If I understand what you want, you just have to do a distinct in your COUNT.

你可以试试这个:

SELECT mid , COUNT(distinct pid) as countmid , SUM(nwgh) as totalnwgh FROM test GROUP BY mid

如果你想试试这个 sqlfiddle:sqlfiddle/#!9/45e68/2

Try this sqlfiddle if you want : sqlfiddle/#!9/45e68/2