• Q&A
  • 회원가입
  • 로그인

[mysql] Node.js - MySQL Order By (정렬 순서)

10  

ORDER BY - 결과 정렬.

 

ORDER BY 컬럼명 ASC

컬럼명 오름차순 기준으로 결과 정렬. 

※ 기본값이라, ASC 안 적어도 됨.

 

ORDER BY 컬럼명 DESC

컬럼명 내림차순 기준으로 결과 정렬.

 

주의: 문자형 (varchar 타입 등)으로 저장된 경우, 숫자형으로 변환 필요.

 

ORDER BY 컬럼명 ASC - 오름차순 정렬 (기본값)

1. C:\User\사용자명\hz.js 생성

 

var mysql = require('mysql');


var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "autoset",

  database: "hz"

});


con.connect(function(err) {

  if (err) throw err;

  var sql = 'SELECT * FROM hz_member ORDER BY mb_level';

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 


2. CMD 모드에서 아래 명령어 입력해 시작 설정.

 

C:\User\사용자명>node hz.js



[결과값]

 

[

  RowDataPacket { mb_id: 2, mb_name: 'AAA', mb_level: '1' },

  RowDataPacket { mb_id: 1, mb_name: 'homzzang', mb_level: '10' },

  RowDataPacket { mb_id: 3, mb_name: 'BBB', mb_level: '2' },

  RowDataPacket { mb_id: 4, mb_name: 'CCC', mb_level: '2' },

  RowDataPacket { mb_id: 5, mb_name: 'DDD', mb_level: '3' },

  RowDataPacket { mb_id: 6, mb_name: 'EEE', mb_level: '3' },

  RowDataPacket { mb_id: 7, mb_name: 'FFF', mb_level: '3' },

  RowDataPacket { mb_id: 8, mb_name: 'GGG', mb_level: '4' },

  RowDataPacket { mb_id: 9, mb_name: 'HHH', mb_level: '4' },

  RowDataPacket { mb_id: 10, mb_name: 'III', mb_level: '4' },

  RowDataPacket { mb_id: 11, mb_name: 'JJJ', mb_level: '4' },

  RowDataPacket { mb_id: 12, mb_name: 'KKK', mb_level: '5' },

  RowDataPacket { mb_id: 13, mb_name: 'LLL', mb_level: '5' },

  RowDataPacket { mb_id: 14, mb_name: 'MMM', mb_level: '5' },

  RowDataPacket { mb_id: 15, mb_name: 'NNN', mb_level: '5' },

  RowDataPacket { mb_id: 16, mb_name: 'OOO', mb_level: '5' }

]

 

 


ORDER BY 컬럼명 DESC - 내림차순 정렬

1. C:\User\사용자명\hz.js 생성.

 

var mysql = require('mysql');


var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "autoset",

  database: "hz"

});


con.connect(function(err) {

  if (err) throw err;

  var sql = 'SELECT * FROM hz_member ORDER BY mb_level DESC';

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 


2. CMD 모드에서 아래 명령어 입력해 시작 설정.

 

C:\User\사용자명>node hz.js



[결과값]

 

[

  RowDataPacket { mb_id: 12, mb_name: 'KKK', mb_level: '5' },

  RowDataPacket { mb_id: 13, mb_name: 'LLL', mb_level: '5' },

  RowDataPacket { mb_id: 14, mb_name: 'MMM', mb_level: '5' },

  RowDataPacket { mb_id: 15, mb_name: 'NNN', mb_level: '5' },

  RowDataPacket { mb_id: 16, mb_name: 'OOO', mb_level: '5' },

  RowDataPacket { mb_id: 8, mb_name: 'GGG', mb_level: '4' },

  RowDataPacket { mb_id: 9, mb_name: 'HHH', mb_level: '4' },

  RowDataPacket { mb_id: 10, mb_name: 'III', mb_level: '4' },

  RowDataPacket { mb_id: 11, mb_name: 'JJJ', mb_level: '4' },

  RowDataPacket { mb_id: 5, mb_name: 'DDD', mb_level: '3' },

  RowDataPacket { mb_id: 6, mb_name: 'EEE', mb_level: '3' },

  RowDataPacket { mb_id: 7, mb_name: 'FFF', mb_level: '3' },

  RowDataPacket { mb_id: 3, mb_name: 'BBB', mb_level: '2' },

  RowDataPacket { mb_id: 4, mb_name: 'CCC', mb_level: '2' },

  RowDataPacket { mb_id: 1, mb_name: 'homzzang', mb_level: '10' },

  RowDataPacket { mb_id: 2, mb_name: 'AAA', mb_level: '1' }

]

 

주의: 레벨이 문자열로 저장되어 10이 엉뚱한 위치에 위치.

 

 

CAST(컬럼명 AS int)  :  문자형 → 숫자형 변환

 

(문자형 → 숫자형) 변환 후, 내림차순 정렬.

ORDER BY CAST(컬럼명 AS int) ASC

 

(문자형 → 숫자형) 변환 후, 내림차순 정렬.

ORDER BY CAST(컬럼명 AS int) DESC

 

주의: 컬럼 자체의 데이터 타입이 바뀐 것은 아님.


[오름차순]

 

var mysql = require('mysql');


var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "autoset",

  database: "hz"

});


con.connect(function(err) {

  if (err) throw err;

  var sql = 'SELECT * FROM hz_member ORDER BY CAST(mb_level AS int) ASC';

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 


 

[

  RowDataPacket { mb_id: 2, mb_name: 'AAA', mb_level: '1' },

  RowDataPacket { mb_id: 3, mb_name: 'BBB', mb_level: '2' },

  RowDataPacket { mb_id: 4, mb_name: 'CCC', mb_level: '2' },

  RowDataPacket { mb_id: 5, mb_name: 'DDD', mb_level: '3' },

  RowDataPacket { mb_id: 6, mb_name: 'EEE', mb_level: '3' },

  RowDataPacket { mb_id: 7, mb_name: 'FFF', mb_level: '3' },

  RowDataPacket { mb_id: 8, mb_name: 'GGG', mb_level: '4' },

  RowDataPacket { mb_id: 9, mb_name: 'HHH', mb_level: '4' },

  RowDataPacket { mb_id: 10, mb_name: 'III', mb_level: '4' },

  RowDataPacket { mb_id: 11, mb_name: 'JJJ', mb_level: '4' },

  RowDataPacket { mb_id: 12, mb_name: 'KKK', mb_level: '5' },

  RowDataPacket { mb_id: 13, mb_name: 'LLL', mb_level: '5' },

  RowDataPacket { mb_id: 14, mb_name: 'MMM', mb_level: '5' },

  RowDataPacket { mb_id: 15, mb_name: 'NNN', mb_level: '5' },

  RowDataPacket { mb_id: 16, mb_name: 'OOO', mb_level: '5' },

  RowDataPacket { mb_id: 1, mb_name: 'homzzang', mb_level: '10' }

]

 


[내림차순]

 

var mysql = require('mysql');


var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "autoset",

  database: "hz"

});


con.connect(function(err) {

  if (err) throw err;


  var sql = 'SELECT * FROM hz_member ORDER BY CAST(mb_level AS int) DESC';

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 

 

 

[

  RowDataPacket { mb_id: 1, mb_name: 'homzzang', mb_level: '10' },

  RowDataPacket { mb_id: 12, mb_name: 'KKK', mb_level: '5' },

  RowDataPacket { mb_id: 13, mb_name: 'LLL', mb_level: '5' },

  RowDataPacket { mb_id: 14, mb_name: 'MMM', mb_level: '5' },

  RowDataPacket { mb_id: 15, mb_name: 'NNN', mb_level: '5' },

  RowDataPacket { mb_id: 16, mb_name: 'OOO', mb_level: '5' },

  RowDataPacket { mb_id: 8, mb_name: 'GGG', mb_level: '4' },

  RowDataPacket { mb_id: 9, mb_name: 'HHH', mb_level: '4' },

  RowDataPacket { mb_id: 10, mb_name: 'III', mb_level: '4' },

  RowDataPacket { mb_id: 11, mb_name: 'JJJ', mb_level: '4' },

  RowDataPacket { mb_id: 5, mb_name: 'DDD', mb_level: '3' },

  RowDataPacket { mb_id: 6, mb_name: 'EEE', mb_level: '3' },

  RowDataPacket { mb_id: 7, mb_name: 'FFF', mb_level: '3' },

  RowDataPacket { mb_id: 3, mb_name: 'BBB', mb_level: '2' },

  RowDataPacket { mb_id: 4, mb_name: 'CCC', mb_level: '2' },

  RowDataPacket { mb_id: 2, mb_name: 'AAA', mb_level: '1' }

]

 

 

PS.

컬럼 자체를  (문자형 → 숫자형) 변환.

 

처음 mb_level 필드 타입을 VARCHAR로 지정해서 문자형으로 저장.

타입 자체를 숫자로 변경하려면, INT 형 등 숫자 타입으로 컬럼 수정.

 


(예)

 

ALTER TABLE `hz_member` CHANGE `mb_level` `mb_level` INT(4) NULL DEFAULT NULL;

 



방문자 수

오늘 1,478
어제 2,350
최대 2,397
이달 34,536
전체 982,282
Since 2012