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 ex：mid = 3或mid = 4 但带有DISTINCT pid，但请注意nwgh的总和不应该为DISTINCT

因此，我的结果如下：

mid | countmid | totalnwgh 3 4（DISTINCT值）3.8（无DISTINCT它需要pid = 12的两个值） 4 2 2.2

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

我试过的东西

选择mid，COUNT（mid）作为countmid，SUM（nwgh）作为来自test的totalnwgh，其中mid = 3 GROUP BY mid HAVING count（DISTINCT pid） pre>

解决方案

如果我理解您的要求，您只需在 强> COUNT 即可。

您可以试试这个：

SELECT mid， COUNT（distinct pid）作为countmid， SUM（nwgh）作为totalnwgh FROM test GROUP BY mid

如果你愿意，可以试试这个sqlfiddle： http：// sqlfiddle /＃！9 / 45e68 / 2

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

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

Hence my result will be as below :

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

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

what i have tried

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

解决方案

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

You can try this :

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

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