select m.*,n.sales from ( select a.region,min(a.total)total from ( select region,sales,sum(total)total from sales group by region,sales )a group by a.region )m left join ( select region,sales,sum(total)total from sales group by region,sales )n on m.region = n.region and m.total = n.total;
#先得求出每个人的合计 mysql> create table t1 select region,sales,sum(total) as total from t group by region,sales; Query OK, 5 rows affected (0.16 sec) Records: 5 Duplicates: 0 Warnings: 0 #到这里,才是分组求最大最小 mysql> select * from t1 where not exists(select 1 from t1 a where t1.region=a.region and t1.total>a.total); +--------+--------+---------+ | region | sales | total | +--------+--------+---------+ | A | 李四 | 500000 | | B | 钱七 | 800000 | | C | 赵五 | 3500000 | +--------+--------+---------+ 3 rows in set (0.00 sec)
select t.sales from table t,(select min(total) as total,region from table group by region) tt where t.total=tt.total and t.region=tt.region
select min(total),region,sales from ( select sum(total) as total,region,sales from t group by region,sales ) a group by region
把上面SQL中的t换成你自己的表名即可
这个后边加上group by t.region就是正确的了
select region,sales from (SELECT orderID,region,sales,sum(total) as stotal FROM `ordertable` group by region,sales order by stotal)as a group by region
SELECT sales,total FROM mytest GROUP BY sales HAVING SUM(total) IN ( SELECT MIN(sc) FROM (SELECT sales AS sales,SUM(total) AS sc,region AS region FROM mytest GROUP BY region,sales) AS a GROUP BY region)此句若查不出来,自绝!!!
SELECT sales,SUM(total) FROM mytest GROUP BY sales HAVING SUM(total) IN ( SELECT MIN(sc) FROM (SELECT sales AS sales,SUM(total) AS sc,region AS region FROM mytest GROUP BY region,sales) AS a GROUP BY region)发错,是此句~~~
[征集]分组取最大N条记录方法征集,及散分....
(
select a.region,min(a.total)total from
(
select region,sales,sum(total)total from sales group by region,sales
)a
group by a.region
)m
left join
(
select region,sales,sum(total)total from sales group by region,sales
)n
on m.region = n.region and m.total = n.total;
CREATE TABLE `t` (
`orderid` int(11) DEFAULT NULL,
`region` varchar(20) DEFAULT NULL,
`sales` varchar(20) DEFAULT NULL,
`total` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8insert into t values
(1,'A','张三',1000000),
(2,'A','李四',500000),
(3,'C','赵五',2000000),
(4,'A','张三',1500000),
(5,'C','赵五',1500000),
(6,'A','张三',500000),
(7,'B','王六',1000000),
(8,'B','钱七',800000),
(9,'B','王六',1000000);
#先得求出每个人的合计
mysql> create table t1 select region,sales,sum(total) as total from t group by region,sales;
Query OK, 5 rows affected (0.16 sec)
Records: 5 Duplicates: 0 Warnings: 0
#到这里,才是分组求最大最小
mysql> select * from t1 where not exists(select 1 from t1 a where t1.region=a.region and t1.total>a.total);
+--------+--------+---------+
| region | sales | total |
+--------+--------+---------+
| A | 李四 | 500000 |
| B | 钱七 | 800000 |
| C | 赵五 | 3500000 |
+--------+--------+---------+
3 rows in set (0.00 sec)
(
select sum(total) as total,region,sales from t group by region,sales
) a
group by region
SELECT MIN(sc) FROM (SELECT sales AS sales,SUM(total) AS sc,region AS region FROM mytest GROUP BY region,sales) AS a
GROUP BY region)此句若查不出来,自绝!!!
SELECT MIN(sc) FROM (SELECT sales AS sales,SUM(total) AS sc,region AS region FROM mytest GROUP BY region,sales) AS a
GROUP BY region)发错,是此句~~~