星期四, 十二月 02, 2004

在MYSQL中如何得到group by对应最大值的哪条记录呢?

In ANSI SQL, I'd do it with a subquery like this:

SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);


在Mysql 4.1 以下的版本,不支持嵌套select的使用,所以你必须想别的的办法如下:
通过 "MAX-CONTACT 跳票"模式来实现.
SUBSTRING(MAX(CONTACT(Fileld_name_with_max_value,other FIled)),length(Fileld_name_with_max_value));
如此递归,则可以把包含最大值字段对应的记录的其他字段的值都读出来了.
一个实际的例子:
SELECT empid,substring(max(concat(effectivedate,paycatagorycode)),11) as paycatagorycode,max(effectivedate) as effectivedate,substring(max(concat(effectivedate,basicSalary)),11) as basicSalary, substring(max(concat(effectivedate,bankBranchCode)),11) as bankBranchCode, substring(max(concat(effectivedate,currencyCode)),11) as currencyCode, substring(max(concat(effectivedate,payCatagoryCode)),11) as payCatagoryCode,substring(max(concat(effectivedate,salaryPolicyType)),11) as salaryPolicyType,substring(max(concat(effectivedate,remarks)),11) as remarks FROM emp_M_SalaryBasicInfo group by empid