select?sc.*,?(
??select?count(1)?from?Score?where?cid?=?sc.cid?and?score?>?sc.score)?+?1?as?px?
from?Score?as?sc?order?by?sc.cid,?px;
求解，這段代碼中是怎么實現的排序功能，子查詢看不懂，能否詳細解析下，謝謝啊
1 回答
已采納
qq_章魚高_03773279
TA貢獻1條經驗 獲得超1個贊
select?sc.*,?( --?子查詢的意思是在score表中找出與當前記錄cid相同，但score大于當前記錄的score(sc.score）的數目+1。 --?那樣的話：如果是0，?則證明該條記錄是表示的是該班的第一名，如果是1，在表示該班的第二名，以此類推。 ??select?count(*)?from?Score??as?a?where?a.cid?=?sc.cid?and?a.score?>?sc.score --?所以后面加了個1 )?+1??as?px from?Score?as?sc?order?by?sc.cid,?px;
做了個測試：
SET?FOREIGN_KEY_CHECKS=0;
--?----------------------------
--?Table?structure?for?score
--?----------------------------
DROP?TABLE?IF?EXISTS?`score`;
CREATE?TABLE?`score`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`cid`?int(11)?DEFAULT?NULL,
??`score`?int(11)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=8?DEFAULT?CHARSET=utf8;
--?----------------------------
--?Records?of?score
--?----------------------------
INSERT?INTO?`score`?VALUES?('1',?'1',?'80');
INSERT?INTO?`score`?VALUES?('2',?'1',?'70');
INSERT?INTO?`score`?VALUES?('3',?'1',?'90');
INSERT?INTO?`score`?VALUES?('4',?'2',?'80');
INSERT?INTO?`score`?VALUES?('5',?'3',?'70');
INSERT?INTO?`score`?VALUES?('6',?'3',?'60');
INSERT?INTO?`score`?VALUES?('7',?'2',?'50');運行上面的sql查詢，結果是：
mysql>?select?sc.*,?( ????->???select?count(*)?from?Score?where?cid?=?sc.cid?and?score?>?sc.score ????->?)?+?1?as?px ????->?from?Score?as?sc?order?by?sc.cid,?px; +----+------+-------+------+ |?id?|?cid??|?score?|?px???| +----+------+-------+------+ |??3?|????1?|????90?|????1?| |??1?|????1?|????80?|????2?| |??2?|????1?|????70?|????3?| |??4?|????2?|????80?|????1?| |??7?|????2?|????50?|????2?| |??5?|????3?|????70?|????1?| |??6?|????3?|????60?|????2?| +----+------+-------+------+ 7?rows?in?set?(0.00?sec)
添加回答
舉報
0/150
提交
取消