作者tn801534 (踢恩)
看板Database
标题[SQL ] 关於三个table group by
时间Fri Jan 14 17:00:47 2011
因为目前系统是使用mysql4 没有子查询功能
SELECT A.*,AVG(point) avgPoint
FROM A LEFT JOIN B ON A.id = B.id
GROUP BY A.id ORDER BY avgPoint
SELECT A.*,SUM(click) sumClick
FROM A LEFT JOIN C ON A.id = C.id
GROUP BY A.id
请问这两个SQL在不生级mysql的情况下有办法一次做完吗
因为我可能会用到sumClick排序
SELECT A.*,AVG(point) avgPoint,SUM(click) sumClick
FROM A LEFT JOIN B ON A.id = B.id LEFT JOIN C ON A.id = C.id
GROUP BY A.id
上面这样做sumClick会被重复算到不正确
希望有高手能帮我解决~~
万分感谢~~~~
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 211.20.224.10
1F：→ kylekai:mysql 4.0 ?.. 那可真是久远了... 01/14 17:31
2F：→ tn801534:的确是4.0 叹... 01/15 14:44
※ tn801534:转录至看板 Web_Design 01/15 14:45
3F：推 danielguo:如果B有PK, sum / GREATEST(1, COUNT(DISTINCT B.`PK`)) 01/18 07:15
4F：→ tn801534:感谢楼上大大 真是奇特的方法~~.. 01/20 12:30