关于分组后的排序问题! 有请各位高人 本帖最后由 window09 于 2009-12-11 14:05:38 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select a.用户,a.产品,a.数量 from tt a inner jointt b on a.用户=b.用户 and a.数量<=b.数量 group by a.用户,a.产品,a.数量having count(b.数量)<=2 orselect a.用户,a.产品,a.数量 from tt a where 2<=(select count(*) from tt b where a.用户=b.用户 and a.数量 <=b.数量 )group by a.用户,a.产品,a.数量 select *from talbe1 awhere 2>(select count(*) from talbe1 where 用户=a.用户 and 数量>a.数量)order by 用户,数量 desc mysql> select * from t_window09;+------+-------+------+| 用户 | 产品 | 数量 |+------+-------+------+| A | 产品1 | 10 || A | 产品2 | 10 || A | 产品3 | 15 || B | 产品1 | 15 || B | 产品2 | 30 || B | 产品3 | 15 |+------+-------+------+6 rows in set (0.06 sec)mysql> select * -> from t_window09 a -> where 2>(select count(*) from t_window09 where 用户=a.用户 and (数量>a.量 or 数量=a.数量 and 产品>a.产品) ) -> order by 用户,数量 desc;+------+-------+------+| 用户 | 产品 | 数量 |+------+-------+------+| A | 产品3 | 15 || A | 产品2 | 10 || B | 产品2 | 30 || B | 产品3 | 15 |+------+-------+------+4 rows in set (0.11 sec)mysql> 1。创建临时表,(这个你已经提到了)2。创建VIEWcreate view kk as select ... 如何用MYSQL把数据放到oracle里啊 怎么去除多余的字段 my sql语法问题 字段命名规范 关于碎片的几个问题 究竟有多少人用pltcl或pljava写PostgreSQL的存储过程? PHP连接Mysql问题 用sql语句怎么样写??? EMS mysql manager谁知道哪里有注册码 腾讯的云数据库Mysql为什么连接不上 关于join MySQL 问题,急求
tt b on a.用户=b.用户 and a.数量<=b.数量 group by a.用户,a.产品,a.数量
having count(b.数量)<=2
select a.用户,a.产品,a.数量 from tt a
where 2<=(select count(*) from tt b where a.用户=b.用户 and a.数量 <=b.数量 )
group by a.用户,a.产品,a.数量
from talbe1 a
where 2>(select count(*) from talbe1 where 用户=a.用户 and 数量>a.数量)
order by 用户,数量 desc
mysql> select * from t_window09;
+------+-------+------+
| 用户 | 产品 | 数量 |
+------+-------+------+
| A | 产品1 | 10 |
| A | 产品2 | 10 |
| A | 产品3 | 15 |
| B | 产品1 | 15 |
| B | 产品2 | 30 |
| B | 产品3 | 15 |
+------+-------+------+
6 rows in set (0.06 sec)mysql> select *
-> from t_window09 a
-> where 2>(select count(*) from t_window09 where 用户=a.用户 and (数量>a.
量 or 数量=a.数量 and 产品>a.产品) )
-> order by 用户,数量 desc;
+------+-------+------+
| 用户 | 产品 | 数量 |
+------+-------+------+
| A | 产品3 | 15 |
| A | 产品2 | 10 |
| B | 产品2 | 30 |
| B | 产品3 | 15 |
+------+-------+------+
4 rows in set (0.11 sec)mysql>
2。创建VIEWcreate view kk as select ...