表c数据:
a b
2011 2011-08-31
2011 2011-09-11
2011 2011-10-11
2012 2012-08-31
2012 2012-10-31
2012 2012-10-11
得到结果:
增加一列序号i,按a+b排序
i a b
1 2011 2011-08-31
2 2011 2011-09-11
3 2011 2011-10-11
1 2012 2012-08-31
2 2012 2012-10-11
3 2012 2012-10-31
a b
2011 2011-08-31
2011 2011-09-11
2011 2011-10-11
2012 2012-08-31
2012 2012-10-31
2012 2012-10-11
得到结果:
增加一列序号i,按a+b排序
i a b
1 2011 2011-08-31
2 2011 2011-09-11
3 2011 2011-10-11
1 2012 2012-08-31
2 2012 2012-10-11
3 2012 2012-10-31
FROM biao
AS
(
SELECT '2011', '2011-08-31'
UNION ALL
SELECT '2011', '2011-09-11'
UNION ALL
SELECT '2011', '2011-10-11'
UNION ALL
SELECT '2012', '2012-08-31'
UNION ALL
SELECT '2012', '2012-10-31'
UNION ALL
SELECT '2012', '2012-10-11')
SELECT ROW_NUMBER() OVER(PARTITION BY a ORDER BY (CAST(a AS DATETIME)+CAST(b AS DATETIME))) id,
a, b
FROM c1/*id a b
-------------------- ---- ----------
1 2011 2011-08-31
2 2011 2011-09-11
3 2011 2011-10-11
1 2012 2012-08-31
2 2012 2012-10-11
3 2012 2012-10-31(6 行受影响)*/
;WITH c1(a, b)
AS
(
SELECT '2011', '2011-08-31'
UNION ALL
SELECT '2011', '2011-09-11'
UNION ALL
SELECT '2011', '2011-10-11'
UNION ALL
SELECT '2012', '2012-08-31'
UNION ALL
SELECT '2012', '2012-10-31'
UNION ALL
SELECT '2012', '2012-10-11'
)select ROW_NUMBER()OVER(PARTITION BY a ORDER BY a+b) id ,a,b
FROM c1
/*
id a b
-------------------- ---- ----------
1 2011 2011-08-31
2 2011 2011-09-11
3 2011 2011-10-11
1 2012 2012-08-31
2 2012 2012-10-11
3 2012 2012-10-31
(6 行受影响)
*/