说明一下表结构:
hotclick这个是点击量,pm_id是歌曲编号是主键,b_id歌手编号,singer是歌手,name是歌名
hotclick pm_id b_id singer name
14 226 1 董杰 大城小爱
4 133 1 董杰 回家
2 134 1 董杰 无赖
3 135 1 董杰 爱海滔滔
11 94 1 董杰 春泥
4 95 1 董杰 谁说喝醉就无所谓
108 27 30 蒋舟 鹧鸪飞
101 28 30 蒋舟 如果我再遇见你
16 30 30 蒋舟 五月花园
22 76 31 樱桃 五月来看花
5 227 33 龚琳娜 孔雀飞来
0 352 33 龚琳娜 五月来
我要的数据是这样子的:
singer这一列是唯一,然后再按hotclick这个最大的一条
hotclick pm_id b_id singer name
108 27 30 蒋舟 鹧鸪飞
22 76 31 樱桃 五月来看花
14 226 1 董杰 大城小爱急啊。。高手帮帮忙的呀!!
hotclick这个是点击量,pm_id是歌曲编号是主键,b_id歌手编号,singer是歌手,name是歌名
hotclick pm_id b_id singer name
14 226 1 董杰 大城小爱
4 133 1 董杰 回家
2 134 1 董杰 无赖
3 135 1 董杰 爱海滔滔
11 94 1 董杰 春泥
4 95 1 董杰 谁说喝醉就无所谓
108 27 30 蒋舟 鹧鸪飞
101 28 30 蒋舟 如果我再遇见你
16 30 30 蒋舟 五月花园
22 76 31 樱桃 五月来看花
5 227 33 龚琳娜 孔雀飞来
0 352 33 龚琳娜 五月来
我要的数据是这样子的:
singer这一列是唯一,然后再按hotclick这个最大的一条
hotclick pm_id b_id singer name
108 27 30 蒋舟 鹧鸪飞
22 76 31 樱桃 五月来看花
14 226 1 董杰 大城小爱急啊。。高手帮帮忙的呀!!
where hotclick=(select max(hotclick) from 表 b where a.singer=b.singer)
where not exists(select 1 from 表 where b_id = t.b_id and hotclick > t.hotclick)
----方法1:
select * from 表 as a where not exists(select 1 from 表 where singer=a.singer and hotclick >a.hotclick )
----方法2:
select * from 表 as a where hotclick = (select max(hotclick ) from 表 where singer = a.singer)
order by singer
----方法3:
select a.* from 表 as a inner join (select singer,max(hotclick ) as hotclick from 表 group by singer) as b
on b.singer= a.singer and a.hotclick = b.hotclick order by a.singer
insert into 表
select 14,226,1,'董杰','大城小爱'
union all select 4,133,1,'董杰','回家'
union all select 2,134,1,'董杰','无赖'
union all select 3,135,1,'董杰','爱海滔滔'
union all select 11,94,1,'董杰','春泥'
union all select 4,95,1,'董杰','谁说喝醉就无所谓'
union all select 108,27,30,'蒋舟','鹧鸪飞'
union all select 101,28,30,'蒋舟','如果我再遇见你'
union all select 16,30,30,'蒋舟','五月花园'
union all select 22,76,31,'樱桃','五月来看花'
union all select 5,227,33,'龚琳娜','孔雀飞来'
union all select 0,352,33,'龚琳娜','五月来'select * from 表 a
where hotclick=(select max(hotclick) from 表 b where a.singer=b.singer)
/*
hotclick pm_id b_id singer name
----------- ----------- ----------- ---------- --------------------
22 76 31 樱桃 五月来看花
108 27 30 蒋舟 鹧鸪飞
5 227 33 龚琳娜 孔雀飞来
14 226 1 董杰 大城小爱(所影响的行数为 4 行)
*/
select * from 表 as a where pm_id= (select max(pm_id) from (select * from 表 as a where not exists(select 1 from 表 where singer=a.singer and hotclick >a.hotclick )) b
where b.singer = a.singer)
insert into 表
select 14,226,1,'董杰','大城小爱'
union all select 4,133,1,'董杰','回家'
union all select 2,134,1,'董杰','无赖'
union all select 3,135,1,'董杰','爱海滔滔'
union all select 11,94,1,'董杰','春泥'
union all select 4,95,1,'董杰','谁说喝醉就无所谓'
union all select 108,27,30,'蒋舟','鹧鸪飞'
union all select 101,28,30,'蒋舟','如果我再遇见你'
union all select 16,30,30,'蒋舟','五月花园'
union all select 22,76,31,'樱桃','五月来看花'
union all select 5,227,33,'龚琳娜','孔雀飞来'
union all select 0,352,33,'龚琳娜','五月来'
union all select 14, 229, 1, '董杰','大城小爱'select * from 表 as a where pm_id= (select max(pm_id) from (select * from 表 as a where not exists(select 1 from 表 where singer=a.singer and hotclick >a.hotclick )) b
where b.singer = a.singer)drop table 表
hotclick pm_id b_id singer name
----------- ----------- ----------- ---------- --------------------
108 27 30 蒋舟 鹧鸪飞
22 76 31 樱桃 五月来看花
5 227 33 龚琳娜 孔雀飞来
14 229 1 董杰 大城小爱(所影响的行数为 4 行)