国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

mysql 分組

elisa.yang / 2007人閱讀

摘要:表結構和數據查詢分組最大記錄默認取分組第一條分組前條記錄

表結構和數據
create table cat(  
    id(1) int not null auto_increment primary key,  
    cat_id int(1),  
    value int(1),  
    name varchar(20)  
);  
insert into cat (cat_id,name,value) values ("1","name1", "1");  
insert into cat (cat_id,name,value) values ("1","name2", "2");  
insert into cat (cat_id,name,value) values ("1","name3", "3");  
insert into cat (cat_id,name,value) values ("1","name4", "4");  
insert into cat (cat_id,name,value) values ("2","name5", "5");  
insert into cat (cat_id,name,value) values ("2","name6", "6");  
insert into cat (cat_id,name,value) values ("2","name7", "7");   
insert into cat (cat_id,name,value) values ("2","name8", "8");  
insert into cat (cat_id,name,value) values ("3","name9", "9");  
insert into cat (cat_id,name,value) values ("3","name10","10");  
insert into cat (cat_id,name,value) values ("3","name11","11");  
insert into cat (cat_id,name,value) values ("3","name12","12"); 
mysql> select *from cat;
+----+--------+-------+--------+
| id | cat_id | value | name   |
+----+--------+-------+--------+
|  1 |      1 |     1 | name1  |
|  2 |      1 |     2 | name2  |
|  3 |      1 |     3 | name3  |
|  4 |      1 |     4 | name4  |
|  5 |      2 |     5 | name5  |
|  6 |      2 |     6 | name6  |
|  7 |      2 |     7 | name7  |
|  8 |      2 |     8 | name8  |
|  9 |      3 |     9 | name9  |
| 10 |      3 |    10 | name10 |
| 11 |      3 |    11 | name11 |
| 12 |      3 |    12 | name12 |
+----+--------+-------+--------+
12 rows in set (0.13 sec) 
查詢分組最大記錄
// 默認取分組第一條
mysql> select *from cat group by cat_id order by cat_id;
+----+--------+-------+-------+
| id | cat_id | value | name  |
+----+--------+-------+-------+
|  1 |      1 |     1 | name1 |
|  5 |      2 |     5 | name5 |
|  9 |      3 |     9 | name9 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)
mysql> select *from (select *from cat order by value desc) a group by cat_id;
+----+--------+-------+--------+
| id | cat_id | value | name   |
+----+--------+-------+--------+
|  4 |      1 |     4 | name4  |
|  8 |      2 |     8 | name8  |
| 12 |      3 |    12 | name12 |
+----+--------+-------+--------+
3 rows in set (0.06 sec)
mysql> select a.* from cat a where value = (select max(value) from cat where cat
_id = a.cat_id) order by a.cat_id;
mysql> select a.* from cat a,(select cat_id,max(value) value from cat group by cat_id) b where a.cat_id = b.cat_id and a.value = b.value order by a.cat_id; 
mysql> select a.* from cat a inner join (select cat_id, max(value) value from cat group by cat_id) b on a.cat_id= b.cat_id and a.value= b.value order by a.cat_id;
+----+--------+-------+--------+
| id | cat_id | value | name   |
+----+--------+-------+--------+
|  4 |      1 |     4 | name4  |
|  8 |      2 |     8 | name8  |
| 12 |      3 |    12 | name12 |
+----+--------+-------+--------+
3 rows in set (0.00 sec)
分組前 3 條記錄
mysql> select a.* from cat a where exists (select count(*) from cat where cat_id= a.cat_id and value > a.value having Count(*) < 3) order by a.cat_id,a.value desc;
mysql> select *from cat a where (select count(*) from cat b where a.cat_id=b.cat_id and b.value>a.value) < 3 order by a.cat_id,a.value desc;

+----+--------+-------+--------+
| id | cat_id | value | name   |
+----+--------+-------+--------+
|  4 |      1 |     4 | name4  |
|  3 |      1 |     3 | name3  |
|  2 |      1 |     2 | name2  |
|  8 |      2 |     8 | name8  |
|  7 |      2 |     7 | name7  |
|  6 |      2 |     6 | name6  |
| 12 |      3 |    12 | name12 |
| 11 |      3 |    11 | name11 |
| 10 |      3 |    10 | name10 |
+----+--------+-------+--------+
9 rows in set (0.15 sec)

文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。

轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/25550.html

相關文章

發表評論

0條評論

elisa.yang

|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<