How do I convert MySQL that uses group by and order by clauses to T
I've got the following MySQL query that I would like to translate so it works in MSSQL:
SELECT *
FROM
(
SELECT *
FROM ranking
ORDER BY rank_no ASC
, effective_dt DESC
) AS sorted_rank
WHERE sorted_rank.rank_id = 1950
GROUP BY sorted_rank.rank_no
LIMIT 10
I've spent an afternoon fiddling but I keep running into problems with GROUP BY and aggregates and all kinds of other errors.
To add a further spanner in the works, the ranking table DOES NOT have a primary key, and I think it may be required to get this working...
Server Version: Microsoft SQL Server 2000 - 8.00.2039
Schema:
rank_id int(11)
week_id int(11)
rider_id int(11)
year_no int(11)
rank_no int(11)
effective_dt datetime
lastupdate_dt datetime
point_no float
average_no float
result_qy int(11)
Dataset:
INSERT INTO `ranking` (`id`, `rank_id`, `week_id`, `rider_id`, `year_no`, `rank_no`, `effective_dt`, `lastupdate_dt`, `point_no`, `average_no`, `result_qy`)
VALUES
(244, 1950, 417, 72253, 2007, 1, '2006-09-03 00:00:00', '2006-09-01 01:45:00', 2559.19, 426.53, 5),
(108, 1950, 426, 72253, 2007, 1, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2559.19, 426.53, 5),
(340, 1950, 386, 21767, 2006, 1, '2006-01-29 00:00:00', '2006-11-29 13:31:00', 3256.25, 814.06, 4),
(178, 1950, 420, 60369, 2007, 2, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 2315.86, 385.98, 4),
(166, 1950, 417, 60369, 2007, 2, '2006-09-03 00:00:00', '2006-09-01 01:45:00', 2315.86, 385.98, 4),
(109, 1950, 426, 60369, 2007, 2, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2315.86, 385.98, 4),
(110, 1950, 426, 49428, 2007, 3, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2191.19, 365.2, 4),
(227, 1950, 417, 49428, 2007, 3, '2006-09-03 00:00:00', '2006-09-01 01:45:00', 2191.19, 365.2, 4),
(409, 1950, 388, 19570, 2006, 3, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 3106.26, 776.57, 4),
(72, 1950, 399, 47036, 2006, 4, '2006-04-30 00:00:00', '2006-11-29 13:33:00', 1038.02, 346.01, 3),
(413, 1950, 388, 55533, 2006, 4, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2835.3, 708.83, 4),
(111, 1950, 426, 64517, 2007, 4, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2001.68, 333.61, 4),
(112, 1950, 426, 72379, 2007, 5, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1677.32, 279.55, 6),
(263, 1950, 420, 27123, 2007, 5, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1607.31, 267.88, 4),
(415, 1950, 388, 45738, 2006, 5, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2744.03, 686.01, 4),
(113, 1950, 426, 27123, 2007, 6, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1607.31, 267.88, 4),
(575, 1950, 420, 50354, 2007, 6, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1148.84, 191.47, 2),
(422, 1950, 388, 39070, 2006, 6, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2626.51, 656.63, 4),
(114, 1950, 426, 65745, 2007, 7, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1576.83, 262.8, 4),
(293, 1950, 388, 42127, 2006, 7, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2614.46, 653.62, 4),
(576, 1950, 420, 32669, 2007, 7, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1133.15, 188.86, 2),
(577, 1950, 420, 23242, 2007, 8, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1107.79, 184.63, 2),
(199, 1950, 420, 60322, 2007, 8, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1390.22, 231.7, 3),
(115, 1950, 426, 54984, 2007, 8, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1532.8, 255.47, 3),
(578, 1950, 420, 41116, 2007, 9, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1096.64, 182.77, 2),
(116, 1950, 426, 72386, 2007, 9, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1442.49, 240.41, 6),
(74, 1950, 399, 32669, 2006, 9, '2006-04-30 00:00:00', '2006-11-29 13:33:00', 817.64, 272.55, 3),
(818890, 1950, 1167, 77510, 0, 10, '2012-01-13 00:00:00', '2012-01-13 11:11:00', 1464.43, 366.11, 4),
(825706, 1950, 1168, 70324, 0, 10, '2012-01-20 00:00:00', '2012-01-20 11:05:00', 1259.38, 314.85, 2),
(826752, 1950, 1170, 75911, 0, 10, '2012-02-01 00:00:00', '2012-02-01 12:58:00', 1237.95, 309.49, 3);
Correct output:
1950 426 72253 2007 1 2006-11-05 00:00:00 2006-11-01 05:59:00 2559.19 426.53 5
1950 426 60369 2007 2 2006-11-05 00:00:00 2006-11-01 05:59:00 2315.86 385.98 4
1950 426 49428 2007 3 2006-11-05 00:00:00 2006-11-01 05:59:00 2191.19 365.2 4
1950 426 64517 2007 4 2006-11-05 00:00:00 2006-11-01 05:59:00 2001.68 333.61 4
1950 426 72379 2007 5 2006-11-05 00:00:00 2006-11-01 05:59:00 1677.32 279.55 6
1950 426 27123 2007 6 2006-11-05 00:00:00 2006-11-01 05:59:00 1607.31 267.88 4
1950 426 65745 2007 7 2006-11-05 00:00:00 2006-11-01 05:59:00 1576.83 262.8 4
1950 426 54984 2007 8 2006-11-05 00:00:00 2006-11-01 05:59:00 1532.8 255.47 3
1950 426 72386 2007 9 2006-11-05 00:00:00 2006-11-01 05:59:00 1442.49 240.41 6
1950 1170 75911 0 10 2012-02-01 00:00:00 2012-02-01 12:58:00 1237.95 309.49 3
SQL Server 2000 and above
Based on your requirement, you need to find the most recent effective date within each rank. To achieve, you need to write a sub query that would fetch the maximum effective date for a given rank and then compare it with the date in the row to filter down to the required results. This will work in all versions from SQL Server 2000 and above.
Following query has been tested in SQL Server 2000.
Click here to view the demo in SQL Fiddle
Script:
SELECT
TOP 10 id
, rank_id
, week_id
, rider_id
, year_no
, rank_no
, effective_dt
, lastupdate_dt
, point_no
, average_no
, result_qy
FROM ranking r_outer
WHERE rank_id = 1950
AND effective_dt =
(
SELECT MAX(effective_dt)
FROM ranking r_inner
WHERE r_inner.rank_no = r_outer.rank_no
AND r_inner.rank_id = 1950
)
ORDER BY rank_no;
SQL Server 2005 and above
You need to use RANK function that would partition the results by rank_no column and sort each partition by rank_no followed by effective_dt in descending order. Each sorted partition section will be assigned a rank value like 1, 2, 3 etc. You are interested in only the first rank of each of these partition groups. So, hence the outer SELECT to filter the results by rank_num = 1 .
RANK function is supported only in SQL Server 2005 and above.
Click here to view demo in SQL Fiddle using SQL Server 2012.
Script:
SELECT
TOP 10 id
, rank_id
, week_id
, rider_id
, year_no
, rank_no
, effective_dt
, lastupdate_dt
, point_no
, average_no
, result_qy
FROM
(
SELECT id
, rank_id
, week_id
, rider_id
, year_no
, rank_no
, effective_dt
, lastupdate_dt
, point_no
, average_no
, result_qy
, RANK() OVER(
PARTITION BY rank_no
ORDER BY rank_no, effective_dt DESC) rownum
FROM ranking r
WHERE r.rank_id = 1950
) t1
WHERE rownum = 1;
我想推荐在WHERE子句中使用子查询来获取最大effective_dt记录（假设值在一个rank_no中是唯一的，我猜它是一个日期时间）：
SELECT TOP 10 *
FROM ranking R
WHERE R.rank_id = 1950 AND
R.effective_dt = (SELECT MAX(effective_dt) from ranking R2 WHERE R2.rank_no = R.rank_no AND R2.rank_id = R.rank_id)
ORDER BY R.rank_no
Just a note of caution without a primary key or clustered on this table it is going to be damned slow when the data grows. I would seriously recommend that you find out which fields are supposed to be keys. A table without a clustered index is a heap and they have no order(generally stored in the order of which they are inserted).
You can try the following query as well.
/*
This CTE will sort the table into a usable structure. To see what it does run the select statement seperately.
*/
;WITH CTERankID
AS
(
SELECT *
,ROW_NUMBER() OVER(PARTITION BY rank_no ORDER BY effective_Dt DESC) AS RowNo
FROM ranking
)
SELECT TOP 10
rank_id
,week_id
,year_no
,rank_no
,effective_dt
,lastupdate_dt
,point_no
,average_no
,result_qy
,rowno
FROM CTERankID AS rankTable
WHERE rankTable.RowNo=1
AND rankTable.rank_id = 1950
Hopefully it will help a little. PS This will not work in MySQL as MySQL does not support CTE's.
链接地址: http://www.djcxy.com/p/95668.html上一篇: 如何为每个ID创建一个标识