数据库表:id topic_id post_date
1 1 2012-10-02
2 1 2012-11-03
3 2 2012-11-05
4 2 2012-11-04
5 3 2012-11-06
6 3 2012-08-06期望结果:
id topic_id post_date
5 3 2012-11-06
6 3 2012-08-06
3 2 2012-11-05
4 2 2012-11-04
2 1 2012-11-03
1 1 2012-10-02按照topic_id分组, 然后每组内按照post_date排序, 整个分组按照组内第一个日期降序排列。小弟急 谢谢了
1 1 2012-10-02
2 1 2012-11-03
3 2 2012-11-05
4 2 2012-11-04
5 3 2012-11-06
6 3 2012-08-06期望结果:
id topic_id post_date
5 3 2012-11-06
6 3 2012-08-06
3 2 2012-11-05
4 2 2012-11-04
2 1 2012-11-03
1 1 2012-10-02按照topic_id分组, 然后每组内按照post_date排序, 整个分组按照组内第一个日期降序排列。小弟急 谢谢了
SELECT
ROW_NUMBER() OVER(PARTITION BY topic_id ORDER BY post_date DESC) rowid,
id,
topic_id,
post_date
FROM tb
ORDER BY topic_id DESC
WITH test (id ,topic_id , post_date)
AS (
SELECT 1 , 1 , '2012-10-02'
UNION ALL
SELECT 2 , 1 , '2012-11-03'
UNION ALL
SELECT 3 , 2 , '2012-11-05'
UNION ALL
SELECT 4 , 2 , '2012-11-04'
UNION ALL
SELECT 5 , 3 , '2012-11-06'
UNION ALL
SELECT 6 , 3 , '2012-08-06')
SELECT * FROM test
ORDER BY topic_id DESC ,post_date DESC
/*
id topic_id post_date
----------- ----------- ----------
5 3 2012-11-06
6 3 2012-08-06
3 2 2012-11-05
4 2 2012-11-04
2 1 2012-11-03
1 1 2012-10-02
(6 行受影响)
*/
AS
(
SELECT 1, 1, '2012-10-02' union all
SELECT 2, 1, '2012-11-03' union all
SELECT 3, 2, '2012-11-05' union all
SELECT 4, 2, '2012-11-04' union all
SELECT 5, 3, '2012-11-06' union all
SELECT 6, 3, '2012-08-06'
)
SELECT
ROW_NUMBER() OVER(PARTITION BY topic_id ORDER BY post_date DESC) rowid,
id,
topic_id,
post_date
FROM c1
ORDER BY topic_id DESCrowid id topic_id post_date
-------------------- ----------- ----------- ----------
1 5 3 2012-11-06
2 6 3 2012-08-06
1 3 2 2012-11-05
2 4 2 2012-11-04
1 2 1 2012-11-03
2 1 1 2012-10-02(6 行受影响)貌似没有用到分组……
小K: 我是在mysql上做的, 这个在mysql上不行啊,先谢谢了
我给的数据可能不太清楚, 如果数据是这样id topic_id post_date
1 1 2012-10-02
2 1 2012-11-03
3 2 2012-11-10
4 2 2012-11-04
5 3 2012-11-06
6 3 2012-08-06期望结果:id topic_id post_date
3 2 2012-11-10
4 2 2012-11-04
5 3 2012-11-06
6 3 2012-08-06
2 1 2012-11-03
1 1 2012-10-02所以两个ORDER BY topic_id DESC ,post_date DESC 是搞不定的不好意思 是我发错地方了。 还是请大家帮助我下,我真的急
3 2 2012-11-10
5 3 2012-11-06
2 1 2012-11-03这三条数据是分组后,每组的第一条记录。
SELECT *
FROM tb a
WHERE (SELECT COUNT(1) FROM tb WHERE topic_id =a.topic_id and post_date>=a.post_date)<=5
ORDER BY a.topic_id,a.post_date DESC;
修改后是:select deptno , job, avg(sal) “平均工资”, count(*) “部门该职位的人数”, sum(sal) “部门该职位总工资”, min(sal) “部门该职位的最低工资" from emp group by deptno, job order by deptno
可是修改后的语句运行还是错误的,是不是双引号用得不对??
后面还有接着一个问题就是:
order by后面的排序测试:修改后的语句能否放sal或者job。什么东西能放到分组查询的order by 后面?
这句话是啥意思都不懂,问得不清不楚。。
select * from test t1 order by (select max(post_date) from test t2 where t2.topic_id=t1.topic_id) desc ,post_date DESC
GO--if object_id('t1') is not null
-- drop table t1
--Go
----test data
--Create table t1([id] smallint,[topic_id] smallint,[post_date] datetime)
--Insert into t1
--Select 1,1,'2012-10-02'
--Union all Select 2,1,'2012-11-03'
--Union all Select 3,2,'2012-11-10'
--Union all Select 4,2,'2012-11-04'
--Union all Select 5,3,'2012-11-06'
--Union all Select 6,3,'2012-08-06'
;WITH Result1 AS (
select
ROW_NUMBER()OVER(ORDER BY MAX(post_date) DESC) AS Row
,topic_id
,MAX(post_date) AS max_post_date
from t1
GROUP BY topic_id
),Result2 AS (
SELECT
a.Row
,b.*
FROM Result1 AS a
INNER JOIN t1 AS b ON a.topic_id=b.topic_id
)SELECT
id
,topic_id
,post_date
FROM Result2
ORDER BY Row ASC,post_date DESC
SELECT
b.id
,b.topic_id
,b.post_date
FROM (
SELECT
(SELECT COUNT(1) FROM t1 WHERE post_date>MAX(a.post_date)) AS Row
,a.topic_id
FROM t1 AS a
GROUP BY a.topic_id
) AS a,t1 AS b
WHERE a.topic_id=b.topic_id
ORDER BY a.Row ASC,b.post_date DESC