** RecyclerView 기본 개념 **


1. 도입과정 및 장.단점

   1. Android 5.0에 처음 소개된 RecyclerView는 안드로이드 ListView의 장/단점을 보완한 고급 위젯이다.

   2. Android Lollipop과 함께 나온 이 위젯은 SupportLibrary에 포함되어 Android Version 7 이상에서 사용이 가능하다.


2. Gradle

dependencies {
    compile 'com.android.support:cardview-v7:21.0.+'
    compile 'com.android.support:recyclerview-v7:21.0.+'
}


3. API 문서

   1. Creating Lists and Cards

   2. RecyclerView

   3. RecyclerView.Adapter



샘플 코드는?

GitHub에 올려두었으며, RecyclerView 샘플입니다.

base로 구분된 부분이 있는데 이는 나중에 supportLibrary 형태로 배포할 예정입니다.

Base는 Kotlin을 기준으로 작성하였고, 샘플은 Java/Kotlin 모두가 존재합니다. 각각 살펴보시면 되겠습니다.


Create Lists

Creating Lists and Cards에 정의된 List 표현입니다.

RecyclerView

Widget인 RecyclerView는 LayoutManager를 통해서 View 그리는 방법을 정의합니다.

RecyclerView.Adapter에서 Data의 ViewHolder 정의에 따라서 UI가 선택되고, 이를 표현하게 됩니다.

  • ViewHolder의 적용으로 View의 재사용을 가능하게 합니다.
  • LayoutManager의 추가로 아이템의 배치 방법을 정의할 수 있다.
    • LinearLayoutManager : 가로/세로 형태로 아이템을 배치한다.

      LinearLayoutManager

    • GridLayoutManager : 한 줄에 1개 이상의 이미지를 표시할 수 있지만, 아이템의 크기는 줄의 첫 번째 아이템의 크기에 따라서 달라질 수 있다.(고정시에는 모두 고정)

      GridLayoutManager

    • StaggeredGridLayoutManager : 그리드 형태에 아이템의 크기를 다양하게 적용할 수 있다.

      StaggeredGridLayoutManager

    • Custom LayoutManager : 3개의 레이아웃 매니저를 상속받아 커스텀 할 수 있다.

  • 많은 데이터의 리스트 형태로 제공이 가능하다.
  • RecyclerView.ItemAnimator을 이용하여 Item의 Animator을 정의할 수 있습니다.


ListView의 장점

  • ListView는 간단하게 리스트를 만드는 부분에 있어서는 장점입니다.
  • 간단한 아이템 형태를 만드는 경우는 빠르게 적용이 가능한 ArrayAdapter을 제공합니다.


ListView의 단점

  • 아이템의 애니메이션 처리가 쉽지 않습니다.
  • 리스트에는 한 개 이상의 View가 필요한 경우가 있지만 커스텀으로 작업하기 쉽지 않습니다.

    • 다음은 한 개 이상의 ViewHolder를 가진 샘플입니다. 위쪽에는 사진이 표시되고, 아래에는 Footer이라는 새로운 ViewHolder가 노출된 상태입니다.

    ViewHolderSample

  • RecyclerView에는 Header/Footer 추가를 직접 구현해야 하지만, ListView는 기본으로 제공합니다.
  • ViewHolder 패턴을 강제적으로 사용하지는 않으므로 고비용의 findViewById가 매번 호출될 수 있다.

구글에서 추천하는 ViewHolder 패턴을 사용하지 않게되면 다음과 같은 코드를 타게 됩니다.

@Override
public View getView(final int position, View convertView, ViewGroup parent) {
    Holder holder = new Holder();
    View rowView = inflater.inflate(R.layout.item_list, null);
    holder.tv = (TextView) rowView.findViewById(R.id.text);
    holder.tv.setText(result[position]);
    return rowView;
}

ItemCount에 따라서 매번 getView을 호출하게 됩니다. 이때 위와 같은 코드는 holder부터 inflater.inflate을 초기화하고, findViewById역시 매번 생성하게 됩니다.

고비용의 findViewById을 매번 하는 것은 성능상 좋지 않고, 메모리의 영향도 받을 수 있습니다.

그래서 다음과 같은 ViewHolder 패턴을 사용할 수 있습니다.

@Override
public View getView(final int position, View convertView, ViewGroup parent) {
    if (convertView == null) {
        rootView = inflater.inflate(R.layout.item_list, null);
        Holder holder = new Holder(); // ViewHolder을 생성
        holder.tv = (TextView) rowView.findViewById(R.id.text);
        rootView.setTag(viewHolder); // setTag
    } else {
        rootView = convertView;
        holder = (Holder) rootView.getTag(); // rootView에서 holder을 꺼내온다
    }

    holder.tv.setText(result[position]);
    return rootView;
}

위와 같은 형태로 제공이 되는데 매번 구현하는 건 귀찮고, 서로 다른 ViewHolder을 여러 개 만들어서 사용하기 쉽지 않습니다.(ItemView의 View가 여러 개 생성될 수 있는 형태)


RecyclerView을 사용하기 위해서

RecyclerView는 supportLibrary와 Android 5.0(API 21 이상)에서 사용이 가능합니다.

API 7부터 사용이 가능한 supportLibrary - recyclerview-v7은 다음과 같이 정의할 수 있습니다.

dependencies {
    compile 'com.android.support:recyclerview-v7:24.2.1'
}


RecyclerView View 정의

RecyclerView의 XML 정의는 다음과 같습니다.

v7의 RecyclerView을 정의합니다.

<android.support.v7.widget.RecyclerView
    android:id="@+id/recycler_view"
    android:layout_width="match_parent"
    android:layout_height="wrap_content" />


layoutManager 정의하기

View의 형태를 정의하기 위해 LayoutManager을 정의해야 합니다.

RecyclerView는 기본 정의가 존재하지 않기 때문에 꼭! 최소 하나씩 설정해주어야 합니다.

설정하지 않으면 화면이 구성되지 않으므로, 헤매지 말고 꼭! 정의 부분을 살펴보시기 바랍니다.

xml에서 정의하기

다음의 app 정의를 사용하기 위해서는 아래의 코드가 추가되어야 합니다.

xmlns:app="http://schemas.android.com/apk/res-auto"

  • app:layoutManager : xml에서 layoutManager을 정의할 수 있습니다.
  • app:spanCount : xml에서 layoutManager에서 사용할 spanCount을 정의할 수 있습니다.

LinearLayoutManager

<android.support.v7.widget.RecyclerView
    app:layoutManager="LinearLayoutManager" />

GridLayoutManager

<android.support.v7.widget.RecyclerView
    app:layoutManager="GridLayoutManager"
    app:spanCount="2" />

LinearLayoutManager

<android.support.v7.widget.RecyclerView
    app:layoutManager="StaggeredGridLayoutManager"
    app:spanCount="3" />

LayoutManager 코드를 통한 정의

// use a linear layout manager
mLayoutManager = new LinearLayoutManager(this);
mRecyclerView.setLayoutManager(mLayoutManager);
// use a staggered grid layout manager
mGridLayoutManager = new new GridLayoutManager(this, 3);
mRecyclerView.setLayoutManager(mGridLayoutManager);
// use a staggered grid layout manager
mStgaggeredGridLayoutManager = new StaggeredGridLayoutManager(2, StaggeredGridLayoutManager.VERTICAL);
mRecyclerView.setLayoutManager(mStgaggeredGridLayoutManager);
  • 상속받아 Custom 처리


ViewHolder 정의

구글에서 정의해주는 간단한 ViewHolder입니다.

RecyclerView.ViewHolder을 상속받아서 정의합니다.

// Provide a reference to the views for each data item
// Complex data items may need more than one view per item, and
// you provide access to all the views for a data item in a view holder
public class ViewHolder extends RecyclerView.ViewHolder {
    // each data item is just a string in this case
    public TextView mTextView;
    public ViewHolder(TextView v) {
        super(v);
        mTextView = v;
    }
}

제가 주로 사용하는 ViewHolder는 다음과 같습니다.

Context와 ViewGroup을 생성자로 전달받아서 LayoutInflater.form을 통해서 View를 생성합니다.

그리고 itemView를 통해서 View들을 findViewById을 제공합니다.

public class ViewHolder extends RecyclerView.ViewHolder {

  private TextView textView;

  public ViewHolder(Context context, ViewGroup parent) {
      super(LayoutInflater.from(context).inflate(R.layout.item_large_view, parent, false));

      textView = (TextView) itemView.findViewById(R.id.tv_message);
  }
}

코틀린을 통한 베이스를 다음과 같이 작성해두었습니다.

abstract class BaseViewHolder<ITEM>(
        open val adapter: RecyclerView.Adapter<*>, itemView: View) :
        RecyclerView.ViewHolder(itemView) {

    constructor(@LayoutRes layoutRes: Int,
                parent: ViewGroup?, adapter: RecyclerView.Adapter<*>)
    : this(adapter, LayoutInflater.from((adapter as? AbstractRecyclerAdapter<*, *>)?.context).inflate(layoutRes, parent, false))

    init {
        ButterKnife.bind(BaseRecyclerViewHolder@this, itemView)
    }

    // ViewHolder의 View를 정의합니다.
    abstract fun onViewHolder(item: ITEM?, position: Int)

    open protected val context: Context?
        get() = (adapter as? AbstractRecyclerAdapter<*, *>)?.context
}


Adapter 정의

RecyclerView의 Adapter는 ListView의 ArrayAdapter처럼 List<Object>을 기본적으로 가지고 있지 않습니다.

그래서 원하는 형태의 Data 형태도 직접 구현하여야 합니다.

public class MyAdapter extends RecyclerView.Adapter<MyAdapter.ViewHolder> {

    // 아이템 리스트
    private String[] mDataset;

    // Provide a suitable constructor (depends on the kind of dataset)
    public MyAdapter(String[] myDataset) {
        mDataset = myDataset;
    }

    // Create new views (invoked by the layout manager)
    @Override
    public MyAdapter.ViewHolder onCreateViewHolder(ViewGroup parent,
                                                   int viewType) {
        // create a new view
        View v = LayoutInflater.from(parent.getContext())
                               .inflate(R.layout.my_text_view, parent, false);
        // set the view's size, margins, paddings and layout parameters
        ...
        ViewHolder vh = new ViewHolder(v);
        return vh;
    }

    // Replace the contents of a view (invoked by the layout manager)
    @Override
    public void onBindViewHolder(ViewHolder holder, int position) {
        // - get element from your dataset at this position
        // - replace the contents of the view with that element
        holder.mTextView.setText(mDataset[position]);
    }

    // Return the size of your dataset (invoked by the layout manager)
    @Override
    public int getItemCount() {
        return mDataset.length;
    }
}

item 정의

RecyclerView는 아이템에 대해서 직접 정의를 해야 합니다.

해당 Adapter에서 사용할 아이템을 직접 정의해주면 됩니다.

// 아이템 리스트
private String[] mDataset;

그리고 getItemCount을 상속을 기본적으로 받아야 하며, size를 다음과 같이 return 해주면 됩니다.

// Return the size of your dataset (invoked by the layout manager)
@Override
public int getItemCount() {
    return mDataset.length;
}

View를 정의

onCreateViewHolder을 기본적으로 상속받습니다.

이를 통해 ViewHolder을 생성하게 됩니다.

viewType에 따라서 최소 1회만 생성합니다.

만약 viewType이 1개 이상이라면 onCreateViewHolder 역시 1번 이상이 호출됩니다.

// Create new views (invoked by the layout manager)
@Override
public MyAdapter.ViewHolder onCreateViewHolder(ViewGroup parent,
                                               int viewType) {
    // create a new view
    View v = LayoutInflater.from(parent.getContext())
                           .inflate(R.layout.my_text_view, parent, false);
    // set the view's size, margins, paddings and layout parameters
    ...
    ViewHolder vh = new ViewHolder(v);
    return vh;
}

view를 표현하기

onCreateViewHolder을 통해서 생성되면, onBindViewHolder에서 해당 holder의 View에 데이터를 노출을 정의하면 됩니다.

RecyclerView는 ViewHolder을 재사용할 수 있도록 설계되어 있으므로, ViewType이 한번 생성된 이후로는 onBindViewHolder만 호출되게 됩니다.

// Replace the contents of a view (invoked by the layout manager)
@Override
public void onBindViewHolder(ViewHolder holder, int position) {
    // - get element from your dataset at this position
    // - replace the contents of the view with that element
    holder.mTextView.setText(mDataset[position]);
}

viewType을 정의하기

기본 상속을 받는 메소드는 아니지만 viewType을 정의할 수 있습니다.

기본값은 0으로 초기화되지만, 사용에 따라서 getItemViewType을 정의할 수 있습니다.

ViewType이 1개 이상이라면 기본적으로 다음의 getItemViewType을 정의해주시면 되겠습니다.

@Override
public int getItemViewType(int position) {
    return super.getItemViewType(position);
}


RecyclerView에 setAdapter()

RecyclerView에 setAdapter을 정의하여야 합니다.

생성된 Adapter을 다음과 같이 추가해주시면 됩니다.

mRecyclerView = (RecyclerView) findViewById(R.id.my_recycler_view);

  // use this setting to improve performance if you know that changes
  // in content do not change the layout size of the RecyclerView
  mRecyclerView.setHasFixedSize(true);

  // use a linear layout manager
  mLayoutManager = new LinearLayoutManager(this);
  mRecyclerView.setLayoutManager(mLayoutManager);

  // specify an adapter (see also next example)
  mAdapter = new MyAdapter(myDataset);
  mRecyclerView.setAdapter(mAdapter);


Kotlin으로 작성한 BaseAdapter

kotlin으로 작성한 BaseAdapter입니다. itemList에 대해서 정의를 하였고,

getItem/removeItem/setItem/clear을 처리하는 부분을 model 형태로 처리하였습니다.

abstract class AbstractRecyclerAdapter<ITEM, VIEW_TYPE : RecyclerView.ViewHolder?>(open val context: Context) :
        RecyclerView.Adapter<VIEW_TYPE>(), BaseRecyclerModel<ITEM> {

    private val itemList: MutableList<ITEM> = ArrayList()

    abstract fun onItemViewType(position: Int): Int

    override fun getItemViewType(position: Int)
            = onItemViewType(position)

    override fun getItemCount()
            = itemList.size

    override fun addItem(item: ITEM) {
        itemList.add(item)
    }

    override fun addItem(position: Int, item: ITEM) {
        itemList.add(position, item)
    }

    override fun addItems(items: List<ITEM>) {
        itemList.addAll(items)
    }

    override fun clear() {
        itemList.clear()
    }

    override fun removeItem(item: ITEM) {
        itemList.remove(item)
    }

    override fun removeItem(position: Int) {
        itemList.removeAt(position)
    }

    /**
     * GetItem null or ITEM
     */
    override fun getItem(position: Int)
            = itemList.getOrNull(position)

    override fun getItems() = itemList
}


마무리

저는 BaseRecyclerAdapter을 만들어서 사용하고 있습니다.

ListView의 ArrayAdapter을 최대한 유사하게 사용하기 위해서 만들었습니다.

Header/Footer을 포함하는 RecyclerView도 추가로 만들어서 사용 중입니다.

ListView는 Header/Footer을 간단하게 추가할 수 있지만, RecyclerView는 제공하지 않습니다. 대신 직접 구현해서 사용할 수 있습니다.

처음에 만들기가 귀찮긴 하지만.. 만들어두면 사용하기 편합니다.

간단하게 사용하기에는 ListView가 편하지만, View의 커스텀이 많아지면 질수록 RecyclerView가 관리하기 편합니다.


샘플 코드는?

GitHub에 올려두었으며, RecyclerView 샘플입니다.

base로 구분된 부분이 있는데 이는 나중에 supportLibrary 형태로 배포할 예정입니다.

Base는 Kotlin을 기준으로 작성하였고, 샘플은 Java/Kotlin 모두가 존재합니다. 각각 살펴보시면 되겠습니다.



 

참고 url : http://thdev.tech/androiddev/2016/11/01/Android-RecyclerView-intro.html


'[Android] - 개념 > RecyclerView' 카테고리의 다른 글

RecyclerView와 CardView 사용하기  (0) 2017.01.18
RecyclerView, Adapter, ViewHolder 개념  (0) 2017.01.18
Posted by 농부지기
,
** MySql-ON DUPLICATE KEY  (oracle의 merge into) **

1. 자료가 PK기준으로 미 존재 하면 INSERT, 존재 하면 UPDATE

2. insert, update기준으로 PK를 기준으로 함

3. 예문)


Posted by 농부지기
,

** MySql - AUTO_INCREMENT. sequece 관리 **



1. 테이블 생성 시

   - PK컬럼에 AUTO_INCREMENT  정의

   - 맨 아래에 반드시 PRIMARY KEY (컬럼명) 를 정의 해야 됨


   - CREATE TABLE 테이블명

           ( user_no          INT   NOT NULL  AUTO_INCREMENT  COMMENT '회원번호'

           , ....

           , INSERT_DATE   DATE  NOT NULL

           , PRIMARY KEY (user_no)

           );



2. AUTO_INCREMENT 초기값 정의

   - alter table 테이블명 auto_increment=초기값;  


3. 최종 생성하고 INSERT된 값 얻기
   - SELECT LAST_INSERT_ID()


9. MyBatis에서 사용하기



   **. java단에서 [user_no]값 받기

       - dao.insert("TPlayerPool.insertUser", playerHm);

       - playerHm.get("user_no");

       - insert를 한후에 자동으로 hashmap(playerHm)에 user_no가 들어가서 java단에서 바로 쓸 수 있다.


Posted by 농부지기
,

**  MySQL 날짜 더하기와 빼기 **



1. 날짜 빼기

   . 한달전    SELECT date_add(now(), interval -1 month)  
   . 하루전    SELECT date_add(now(), interval -1 day)  
   . 한시간전 SELECT date_add(now(), interval -1 hour)  


2. 등록된지 24시간이 이전 자료 조회
   
select D_time from 테이블명 where date_col > date_sub(now(), interval 1 day); 

'(DB) MySql > Date 함수' 카테고리의 다른 글

MySql - sysdate (curdate, newo, curtime)  (0) 2017.01.18
MySql - DATE_FORMAT  (0) 2017.01.18
Date형식을 String형식으로 변환  (0) 2017.01.17
Posted by 농부지기
,

** Date형식을 String형식으로 변환 **


1. Type : DATE_FORMAT( now() , '%Y-%m-%d' ) >=reg_date


2. 예문)

   1.DATE_FORMAT(now(), '%Y-%m-%d'// 2011-06-14
   2.DATE_FORMAT(now(), '%Y-%M-%D'// 2011-June-14th
   3.DATE_FORMAT(now(), '%H:%i:%s')  // 22:26:11  ( 24시간 표현 )
   4.DATE_FORMAT(now(), '%h:%i:%s')  // 10:26:11 ( 12시간 표현 )



3. DATE_FORMAT(date,format) (참고URL : http://blog.tini4u.net/entry/MYSQL-날짜-데이터-타입)

format 스트링에 따라서date 값을 포맷한다.

아래에 나와 있는 지정자 (specifier)들은 format 스트링안에서 사용할 수 있다. ‘%’ 문자는 지정자 문자를 포맷하기 전에 필요한 것이다.

Specifier

Description

%a

Abbreviated weekday name (Sun..Sat)

%b

Abbreviated month name (Jan..Dec)

%c

Month, numeric (0..12)

%D

Day of the month with English suffix (0th1st2nd3rd, …)

%d

Day of the month, numeric (00..31)

%e

Day of the month, numeric (0..31)

%f

Microseconds (000000..999999)

%H

Hour (00..23)

%h

Hour (01..12)

%I

Hour (01..12)

%i

Minutes, numeric (00..59)

%j

Day of year (001..366)

%k

Hour (0..23)

%l

Hour (1..12)

%M

Month name (January..December)

%m

Month, numeric (00..12)

%p

AM or PM

%r

Time, 12-hour (hh:mm:ss followed by AM or PM)

%S

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour (hh:mm:ss)

%U

Week (00..53), where Sunday is the first day of the week

%u

Week (00..53), where Monday is the first day of the week

%V

Week (01..53), where Sunday is the first day of the week; used with %X

%v

Week (01..53), where Monday is the first day of the week; used with %x

%W

Weekday name (Sunday..Saturday)

%w

Day of the week (0=Sunday..6=Saturday)

%X

Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V

%x

Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v

%Y

Year, numeric, four digits

%y

Year, numeric (two digits)

%%

A literal ‘%’ character

%x

x, for any ‘x’ not listed above

달 및 날짜 지정자를 위한 범위는 0에서부터 시작을 한다..

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

        -> 'Saturday October 1997'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');

        -> '22:23:00'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

                          '%D %y %a %d %m %b %j');

        -> '4th 97 Sat 04 10 Oct 277'

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',

                          '%H %k %I %r %T %S %w');

        -> '22 22 10 10:23:00 PM 22:23:00 00 6'

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');

        -> '1998 52'

mysql> SELECT DATE_FORMAT('2006-06-00', '%d');

        -> '00'

  • DAY(date)

DAY() is a synonym for DAYOFMONTH().

  • DAYNAME(date)

date에 대한 주간 요일 이름을 리턴한다.

mysql> SELECT DAYNAME('1998-02-05');

        -> 'Thursday'

  • DAYOFMONTH(date)

0에서 31 사이의 달별 날짜를 리턴한다.

mysql> SELECT DAYOFMONTH('1998-02-03');

        -> 3

  • DAYOFWEEK(date)

date에 대한 주간 요일 인덱스를 리턴한다 (1 = Sunday, 2 = Monday, …, 7 = Saturday). 이러한 인덱스들은 ODBC 표준을 따른다.

mysql> SELECT DAYOFWEEK('1998-02-03');

        -> 3

  • DAYOFYEAR(date)

1에서 366 사이의 date에 해당하는 일수를 리턴한다.

mysql> SELECT DAYOFYEAR('1998-02-03');

        -> 34

  • EXTRACT(unit FROM date)

EXTRACT() 함수는 DATE_ADD() 또는 DATE_SUB()과 같은 종류의 유닛 지정자를 사용하지만날짜 산술식을 실행하는 것이 아닌 날짜에서 부분을 추출하는 기능을 수행한다..

mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');

       -> 1999

mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');

       -> 199907

mysql> SELECT EXTRACT(DAY_MINUTE FROM '1999-07-02 01:02:03');

       -> 20102

mysql> SELECT EXTRACT(MICROSECOND

    ->                FROM '2003-01-02 10:30:00.00123');

        -> 123

  • FROM_DAYS(N)

주어진 날짜 숫자 N에 대해서 DATE 값을 리턴한다.

mysql> SELECT FROM_DAYS(729669);

        -> '1997-10-07'

  • FROM_UNIXTIME(unix_timestamp)FROM_UNIXTIME(unix_timestamp,format)

unix_timestamp 인수에 대한 표현식을 이 함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다unix_timestamp는 UNIX_TIMESTAMP() 함수가 만들어 내는 것과 같은 내부 타임 스탬프 값이다.

만일 format 을 주게 되면그 결과는 format 스트링에 따라서 포맷이 되는데이것은 DATE_FORMAT() 함수에 대한 엔트리에 목록화 되는 방식과 같은 방식을 사용한다.

mysql> SELECT FROM_UNIXTIME(875996580);

        -> '1997-10-04 22:23:00'

mysql> SELECT FROM_UNIXTIME(875996580) + 0;

        -> 19971004222300

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),

    ->                      '%Y %D %M %h:%i:%s %x');

        -> '2003 6th August 06:22:58 2003'

  • GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL')

포맷 스트링을 리턴한다이 함수는 DATE_FORMAT() 및 STR_TO_DATE() 함수를 결합하는데 있어서 매우 유용한 것이다.

첫 번째 인수와 두 번째 인수에 대해서 몇 가지 포맷 스트링 값이 사용 가능하다이 값들은 ISO 9075를 참조한다.

Function Call

Result

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

GET_FORMAT(DATETIME,'USA')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'JIS')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'ISO')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'EUR')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'INTERNAL')

'%Y%m%d%H%i%s'

GET_FORMAT(TIME,'USA')

'%h:%i:%s %p'

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

GET_FORMAT(TIME,'EUR')

'%H.%i.%S'

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

TIMESTAMP는 또한 GET_FORMAT() 함수의 첫 번째 인수로 사용이 가능한데이와 같은 경우함수는 DATETIME 함수와 같은 값을 리턴한다.

mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));

        -> '03.10.2003'

mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));

        -> '2003-10-31'

  • HOUR(time)

time에 대해서 시간 (hour)를 리턴한다일별 시간에 대해서는 0에서 23 사이의 값을 가진다하지만TIME 값의 실제 범위는 이것보다 훨씬 크기 때문에HOUR 23 보다 큰 값을 리턴할 수 있다.

mysql> SELECT HOUR('10:05:03');

        -> 10

mysql> SELECT HOUR('272:59:59');

        -> 272

  • LAST_DAY(date)

날짜 또는 데이트타임 값을 가져와서 그 달의 가장 마지막 날짜에 해당하는 값을 리턴한다만일 인수가 올바르지 않으면 NULL을 리턴한다.

mysql> SELECT LAST_DAY('2003-02-05');

        -> '2003-02-28'

mysql> SELECT LAST_DAY('2004-02-05');

        -> '2004-02-29'

mysql> SELECT LAST_DAY('2004-01-01 01:01:01');

        -> '2004-01-31'

mysql> SELECT LAST_DAY('2003-03-32');

        -> NULL

  • LOCALTIMELOCALTIME()

LOCALTIME 및 LOCALTIME()는 NOW()과 동일한 것이다.

  • LOCALTIMESTAMPLOCALTIMESTAMP()

LOCALTIMESTAMP 및 LOCALTIMESTAMP()는 NOW()과 동일하다.

  • MAKEDATE(year,dayofyear)

주어진 연도 및 연도별 날짜 값을 가지고서해당하는 날짜를 리턴한다dayofyear 인수는 0 보다 커야 하며그렇지 않을 경우에는 NULL을 리턴한다.

mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);

        -> '2001-01-31', '2001-02-01'

mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);

        -> '2001-12-31', '2004-12-30'

mysql> SELECT MAKEDATE(2001,0);

        -> NULL

  • MAKETIME(hour,minute,second)

hourminute그리고 second 인수를 가지고 계산된 시간 값을 리턴한다.

mysql> SELECT MAKETIME(12,15,30);

        -> '12:15:30'

  • MICROSECOND(expr)

시간 또는 데이트타임 수식 expr 에서 마이크로 세컨드(microsecond) 값을 리턴하는데그 범위는0에서 999999 사이가 된다.

mysql> SELECT MICROSECOND('12:00:00.123456');

        -> 123456

mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');

        -> 10

  • MINUTE(time)

time에서 분 (minute)에 해당하는 값을 리턴하는데그 범위는 0에서 59 사이가 된다.

mysql> SELECT MINUTE('98-02-03 10:05:03');

        -> 5

  • MONTH(date)

date에서 월에 해당하는 값을 리턴하는데그 범위는 0에서 12 사이가 된다.

mysql> SELECT MONTH('1998-02-03');

        -> 2

  • MONTHNAME(date)

date에 해당하는 월을 전체 이름으로 표시한다.

mysql> SELECT MONTHNAME('1998-02-05');

        -> 'February'

  • NOW()

이 함수가 사용되는 문장에 따라서 그 형태를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS로 해서 현재의 날짜 및 시간을 리턴한다.

mysql> SELECT NOW();

        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

        -> 19971215235026

NOW()는 이 명령문이 실행을 시작하는 시점을 나타내는 시간을 상수 값으로 리턴한다. (스토어드 루틴 또는 트리거 안에서는NOW()는 루틴 또는 트리거링 명령문이 실행되는 시점 값을 리턴한다.) 이 함수는 SYSDATE()와는 차이점을 가지는데후자의 경우는 5.0.13 이후에는 함수가 실행된 정확한 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW()               | SLEEP(2) | NOW()               |

+---------------------+----------+---------------------+

2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

+---------------------+----------+---------------------+

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

+---------------------+----------+---------------------+

2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

+---------------------+----------+---------------------+

이 두 함수간의 차이점에 대해서는 SYSDATE() 함수 설명을 참고로 하기 바란다.

  • PERIOD_ADD(P,N)

N  (month)을 기간 P 에 추가한다 (YYMM 또는 YYYYMM 포맷). 리턴되는 값은 YYYYMM포맷을 가진다기간 인수 P 는 날짜 값이 아니라는 점을 유의한다.

mysql> SELECT PERIOD_ADD(9801,2);

        -> 199803

  • PERIOD_DIFF(P1,P2)

P1 P2 사이의 월별 간격을 리턴하는데여기에서 P1 과 P2 는 YYMM 또는 YYYYMM형태가 되어야 한다인수 P1 과 P2 는 날짜 값이 아니라는 점을 유의한다.

mysql> SELECT PERIOD_DIFF(9802,199703);

        -> 11

  • QUARTER(date)

Date에 해당하는 분기를 리턴하는데범위는에서 4 사이가 된다

mysql> SELECT QUARTER('98-04-01');

        -> 2

  • SECOND(time)

time에서 초 부분을 리턴하는데범위는 0 에서 59 사이가 된다.

mysql> SELECT SECOND('10:05:03');

        -> 3

  • SEC_TO_TIME(seconds)

seconds 인수를 리턴하는데이 함수가 사용되는 문장에 따라서 'HH:MM:SS' 또는 HHMMSS 포맷의 값으로 시간초로 변환을 시킨다.

mysql> SELECT SEC_TO_TIME(2378);

        -> '00:39:38'

mysql> SELECT SEC_TO_TIME(2378) + 0;

        -> 3938

  • STR_TO_DATE(str,format)

이 함수는 DATE_FORMAT() 함수와는 정 반대 값을 리턴한다이 함수는 스트링 str 을 가져와서 스트링 format형태로 포맷을 한다STR_TO_DATE()포맷 스트링이 날짜 및 시간 부분을 모두 가지고 있는 경우에는 DATETIME 값을또는 스트링이 날짜 또는 시간 부분만을 가지고 있는 경우에는 DATE 또는 TIME 값을 리턴한다.

Str에 포함되어 있는 날짜시간또는 데이트타임 값은 ormat에 의해 지정되는 포맷으로 주어져야 한다format에서 사용될 수 있는 지정자의 경우는 DATE_FORMAT() 함수 설명을 참조하기 바란다만일 str이 올바르지 않은 날짜시간또는 데이트타임 값을 가지고 있다면STR_TO_DATE()는 NULL을 리턴한다. MySQL 5.0.3 이후에서는 경고문도 함께 발생한다.

날짜 값의 범위 검사는 Section 11.3.1, “DATETIMEDATE그리고 TIMESTAMP타입에서 설명을 하고 있다예를 들면이것은, “제로 (zero)” 날짜 또는 날짜 부분에 0을 가지고 있는 날짜는 SQL 모드가 이러한 값을 허용하지 않도록 설정되지 않는 한 사용 가능하다는 것을 의미한다.

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');

        -> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');

        -> '2004-04-31'

  • SUBDATE(date,INTERVAL expr unit)SUBDATE(expr,days)

두 번째 인수에 대해서 INTERVAL 폼을 사용해서 호출을 한다면SUBDATE()는 DATE_SUB()과 동일한 값을 리턴하게 된다.

mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);

        -> '1997-12-02'

두 번째 형태는 days에 대해서 정수 값을 사용할 수 있도록 하고 있다이와 같은 경우날짜 또는 데이트타입 수식 expr에서 빼기가 되어야 하는 날짜 수로 해석이 된다.

mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);

        -> '1997-12-02 12:00:00'

Note"%X%V" 형태를 사용해서 년-주간 스트링을 날짜로 변환 시킬수는 없는데그 이유는 연도와 주간의 결합은 해당 주간이 두 개의 달에 걸쳐 있을 경우에는 연도 및 달을 구분할 수 없기 때문이다연도-주간 값을 날짜로 변환하기 위해서는주간 요일 (weekday)을 함께 지정해 주어야 한다:

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');

        -> '2004-10-18'

  • SUBTIME(expr1,expr2)

SUBTIME() 함수는 expr1 – expr2 수식의 결과 값을 리턴하느데그 포맷은 expr1을 따른다.  expr1 은 시간 또는 데이트 타임 수식을 사용할 수 있으며expr2은 시간 수식이 된다.

mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

        -> '1997-12-30 22:58:58.999997'

mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

        -> '-00:59:59.999999'

  • SYSDATE()

함수가 사용된 문장에 따라서 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS포맷으로 현재 날짜 및 시간을 리턴한다.

MySQL 5.0.13 이후부터는SYSDATE()은 이것이 실행된 시간을 리턴한다이 함수는 NOW()과 차이를 가지는데후자의 경우는 명령문이 실행을 시작한 상수 시간을 리턴한다.

mysql> SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW()               | SLEEP(2) | NOW()               |

+---------------------+----------+---------------------+

2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |

+---------------------+----------+---------------------+

 

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE()           | SLEEP(2) | SYSDATE()           |

+---------------------+----------+---------------------+

2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |

+---------------------+----------+---------------------+

또한SET TIMESTAMP 명령문은 NOW()가 리턴하는 값에는 영향을 주지만SYSDATE()가 리턴하는 값에는 영향을 주지 않는다이것이 의미하는 것은 바이너리 로그에서 설정된 타임 스탬프가 SYSDATE() 함수 호출에는 영향을 주지 않는다는 것을 나타내는 것이다.

SYSDATE()는 동일한 명령문 안에서도 서로 다른 값을 리턴하고SET TIMESTAMP에 의해서도 영향을 받지 않기 때문에이 함수는 논-디터미니스틱 (non-deterministic)이며 따라서 리플리케이션에서는 안전하지가 못하게 된다만일 이것이 문제가 된다면서버를 --sysdate-is-now 옵션과 함께 구동 시킴으로서 SYSDATE()가 NOW()의 별칭으로 동작하도록 할 수는 있다.

  • TIME(expr)

시간 또는 데이트 타임 수식 expr 에서 시간 부분을 추출하고 그 값을 스트링으로 리턴한다.

mysql> SELECT TIME('2003-12-31 01:02:03');

        -> '01:02:03'

mysql> SELECT TIME('2003-12-31 01:02:03.000123');

        -> '01:02:03.000123'

  • TIMEDIFF(expr1,expr2)

TIMEDIFF()은 expr1 – expr2 수식의 결과를 시간 값으로 리턴한다expr1 및 expr2 는 시간 또는 날짜-시간 수식이 될 수 있지만양쪽 모두 동일한 타입이어야 한다.

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',

    ->                 '2000:01:01 00:00:00.000001');

        -> '-00:00:00.000001'

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',

    ->                 '1997-12-30 01:01:01.000002');

        -> '46:58:57.999999'

  • TIMESTAMP(expr)TIMESTAMP(expr1,expr2)

단일 인수를 사용하게 되면이 함수는 날짜 또는 데이트 타임 수식 expr 을 데이트 타임 값으로 리턴한다두 개의 인수를 사용하게 되면이 함수는 시간 수식 expr2 를 날짜 또는 데이트 타임 수식 expr1 에 추가를 하게 되고 그 결과를 데이트타임 값 형태로 리턴한다.

mysql> SELECT TIMESTAMP('2003-12-31');

        -> '2003-12-31 00:00:00'

mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

        -> '2004-01-01 00:00:00'

  • TIMESTAMPADD(unit,interval,datetime_expr)

정수 수식 interval 를 날짜 또는 데이트타임 수식 datetime_expr에 추가를 한다interval 에 대한 유닛은 unit 인수에 의해 주어지는데이 인수는 다음의 값 중에 하나가 되어야 한다FRAC_SECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTER, or YEAR.

unit 값은 위에 나와 있는 키워드중의 하나를 사용하거나 또는 지정될 수 있다SQL_TSI_의 접두어를 사용해서 지정할 수가 있다예를 들면DAY 및 SQL_TSI_DAY 는 모두 유효하다.

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');

        -> '2003-01-02 00:01:00'

mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');

        -> '2003-01-09'

TIMESTAMPADD() 5.0.0 이후에 사용 가능해졌다.

  • TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

날짜 또는 데이트 타임 수식 datetime_expr1 및 datetime_expr2간의 정수 차이를 리턴한다그 결과에 대한 유닛은 unit 인수에 의해 주어진다unit 에 대한 유효 값은 TIMESTAMPADD() 함수에서 설명된 리스트 값과 같다.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

TIMESTAMPDIFF() 5.0.0 이후에 사용 가능해졌다.

  • TIME_FORMAT(time,format)

이 함수는 DATE_FORMAT() 함수와 비슷하게 사용되지만format 스트링은 시간그리고 초에만 해당하는 지정자를 가질 수도 있다다른 지정자들을 사용하면 NULL 값 또는 0이 나오게 된다.

만일 time 값이 23 보다 큰 시간 부분을 가진다면%H 및 %k 시간 포맷 지정자 0에서 23 보다 큰 값을 만들게 된다다른 시간 포맷 지정자는 시간 값 모듈로 12를 만든다.

mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');

        -> '100 100 04 04 4'

  • TIME_TO_SEC(time)

time 인수를 초로 변환해서 리턴한다.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

  • TO_DAYS(date)

주어진 날짜 date에 대해서연도 0에서부터 계산된 날짜 숫자를 리턴한다.

mysql> SELECT TO_DAYS(950501);

        -> 728779

mysql> SELECT TO_DAYS('1997-10-07');

        -> 729669

  • UNIX_TIMESTAMP()UNIX_TIMESTAMP(date)

만일 아무런 인수를 지정하지 않고 호출을 한다면부호를 사용하지 않은 유닉스 타임 스탬프를 리턴한다 ('1970-01-01 00:00:00' UTC 이후의 초). 만일 UNIX_TIMESTAMP()가 date 인수를 사용해서 호출되었다면이 함수는 '1970-01-01 00:00:00' UTC 이후의 초 형태로 인수 값을 리턴한다Date는 DATE 스트링DATETIME 스트링TIMESTAMP또는 YYMMDD 또는 YYYYMMDD에 있는 숫자가 될 수도 있다서버는 date를 현재의 타임 존에 있는 값으로 해석을 해서 UTC의 내부 값으로 변환을 시킨다클라이언트는 자신의 타임 존을 Section 5.11.8, “MySQL 서버 타임  지원에서 설명한 방식으로 설정을 한다.

mysql> SELECT UNIX_TIMESTAMP();

        -> 882226357

mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');

        -> 875996580

UNIX_TIMESTAMP를 TIMESTAMP 컬럼에서 사용하는 경우이 함수는 내부 타임 스탬프 값을 직접 리턴하고의미적인 (implicit) “스트링에서 유닉스 타임 스탬프로 변환을 실행하지 않는다만일 날짜 범위를 벗어난 값을 UNIX_TIMESTAMP()에 주게 되면함수는 0을 리턴한다.

  • UTC_DATEUTC_DATE()

이 함수가 사용된 문장에 따라서현재의 UTC 날짜를 'YYYY-MM-DD' 또는 YYYYMMDD포맷으로 리턴한다.

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

        -> '2003-08-14', 20030814

  • UTC_TIMEUTC_TIME()

이 함수가 사용된 문장에 따라서현재의 UTC 시간을 'HH:MM:SS' 또는 HHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

        -> '18:07:53', 180753

  • UTC_TIMESTAMPUTC_TIMESTAMP()

이 함수가 사용된 문장에 따라서현재의 UTC 날짜를 'YYYY-MM-DD HH:MM:SS' 또는 YYYYMMDDHHMMSS 포맷으로 리턴한다.

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

        -> '2003-08-14 18:08:04', 20030814180804

  • WEEK(date[,mode])

이 함수는 date에 해당하는 주간 숫자를 리턴한다WEEK() 함수에 두 개의 인수를 사용하면 해당 주가 일요일 또는 월요일에 시작을 하는지를 지정할 수 있으며 또한 리턴되는 값이 0에서 53 사이 또는 1에서 53 사이에 있는지를 지정할 수가 있게 된다만일 mode 인수를 생략한다면default_week_format 시스템 변수가 사용된다. Section 5.2.2, “서버 시스템변수를 참조할 것.

아래의 테이블은 mode 인수가 어떻게 동작을 하는지를 보여주는 것이다.

 

First day

 

 

Mode

of week

Range

Week 1 is the first week …

0

Sunday

0-53

with a Sunday in this year

1

Monday

0-53

with more than 3 days this year

2

Sunday

1-53

with a Sunday in this year

3

Monday

1-53

with more than 3 days this year

4

Sunday

0-53

with more than 3 days this year

5

Monday

0-53

with a Monday in this year

6

Sunday

1-53

with more than 3 days this year

7

Monday

1-53

with a Monday in this year

mysql> SELECT WEEK('1998-02-20');

        -> 7

mysql> SELECT WEEK('1998-02-20',0);

        -> 7

mysql> SELECT WEEK('1998-02-20',1);

        -> 8

mysql> SELECT WEEK('1998-12-31',1);

        -> 53

만일 어떤 날짜가 바로 전년도의 마지막 주에 있다면여러분이 옵션 인수MySQL은 0을 리턴한다:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

        -> 2000, 0

  • WEEKDAY(date)

Date에 해당하는 주간 요일 인덱스를 리턴한다 (0 = Monday, 1 = Tuesday, … 6 = Sunday).

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');

        -> 1

mysql> SELECT WEEKDAY('1997-11-05');

        -> 2

  • WEEKOFYEAR(date)

1에서 53 사이의 달력 주간 숫자를 리턴한다.

mysql> SELECT WEEKOFYEAR('1998-02-20');

        -> 8

  • YEAR(date)

1000에서 9999 사이의 date에 해당하는 연도를 리턴하거나또는 제로” 날짜일 경우에는 0을 리턴한다.

mysql> SELECT YEAR('98-02-03');

        -> 1998

  • YEARWEEK(date)YEARWEEK(date,start)

해당되는 연도 및 주를 리턴한다start 인수는 WEEK() 함수에서 사용되는 것과 동일하게 동작을 한다결과에 나오는 연도는 날짜 인수에 표시되어 있는 연도와 다르게 나올 수도 있다.

mysql> SELECT YEARWEEK('1987-01-01');

        -> 198653


'(DB) MySql > Date 함수' 카테고리의 다른 글

MySql - sysdate (curdate, newo, curtime)  (0) 2017.01.18
MySql - DATE_FORMAT  (0) 2017.01.18
MySQL 날짜 더하기와 빼기  (0) 2017.01.17
Posted by 농부지기
,
** mysql rank 구하기 (팀이 존재 하는 동일등수) ** 


1. 정의
   1. 반별로 석차 구하기
   2. 팀별 동일점수이며  같은 석차가 부여되어야 함
   3. 팀별로 동일석차가 존재 할 수 있음.

2. 자료 예제
-------------------------------------------
반 :   이름      : 팀명   :   점수    : 석차
-------------------------------------------
1반   강감찬      A        100        1
1반   차명이      A        100        1

1반   이순신      B         96         2
1반   골동품      B         96         2

1반   홍길동      C         96         2
1반   결사대      C         96         2

1반   김유신      D         90         4
1반   김유신      D         90         4

-------------------------------------------
2반   말똥고리   A        80        1
2반   구리구리   A        80        1
2반   마리마리   A        80        1

2반   장수말벌   B         70       2
2반   벌똥말똥   B         70       2
2반   쉬벌버벌   B         70       2

2반    꿀벌        C        60        3
2반    말벌        C        60        3
2반    쇠벌        C        60        3

2반     나비       D         60       3
2반     너비       D         60       3
2반     고비       D         60       3
-------------------------------------------------------

3. SQL 예제


'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (동일등수)  (1) 2017.01.13
Group 별로 상위 n개의 레코드 얻기  (0) 2017.01.12
mysql Rownum 구현하기  (0) 2017.01.12
Posted by 농부지기
,
** mysql rank 구하기 (동일등수) **

 

첫번째 방법 

   - 테이블을 3번 사용해서 구하기  (가장 무식한 방법)

 

 


----------------------------------
   반   :  이름     :   점수    : 등수
   1반   강감찬       100        1
   1반   이순신        96        2
   1반   홍길동        96        2
   1반   김유신        90        4
----------------------------------
   2반    말똥고리     80       1
   2반    장수말벌     70       2
   2반    꿀벌          60      3
   2반    나비          60      3
------------------------------------

두번째 방법

    - 테이블을 2번 사용해서 구하기  - 속도 개선

 

 

세번째 방법

    - 변수를 활용해서 구하기 - 속도 짱

Posted by 농부지기
,

** Group 별로 상위 n개의 레코드 얻기 **

 

 

Step 1: getting the top

We already know how to get a single column's value for the top country, as presented in the aforementioned post:

SELECT Continent
     , SUBSTRING_INDEX(ROUP_CONCAT(Name ORDER BY SurfaceArea DESC),',', 1) AS Name
  FROM Country
 GROUP BY Continent;
+---------------+--------------------+
| Continent     | Name               |
+---------------+--------------------+
| Asia          | China              |
| Europe        | Russian Federation |
| North America | Canada             |
| Africa        | Sudan              |
| Oceania       | Australia          |
| Antarctica    | Antarctica         |
| South America | Brazil             |
+---------------+--------------------+

Step 2: adding columns

This part is easy: just throw in the rest of the columns (again, only indicating the top country in each continent)

SELECT
 Continent,
 SUBSTRING_INDEX(
   GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),
   ',', 1) AS Name,
 SUBSTRING_INDEX(
   GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),
   ',', 1) AS SurfaceArea,
 SUBSTRING_INDEX(
   GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),
   ',', 1) AS Population
FROM
 Country
GROUP BY
 Continent
;

+---------------+--------------------+-------------+------------+
| Continent     | Name               | SurfaceArea | Population |
+---------------+--------------------+-------------+------------+
| Asia          | China              | 9572900.00  | 1277558000 |
| Europe        | Russian Federation | 17075400.00 | 146934000  |
| North America | Canada             | 9970610.00  | 31147000   |
| Africa        | Sudan              | 2505813.00  | 29490000   |
| Oceania       | Australia          | 7741220.00  | 18886000   |
| Antarctica    | Antarctica         | 13120000.00 | 0          |
| South America | Brazil             | 8547403.00  | 170115000  |
+---------------+--------------------+-------------+------------+

Step 3: casting

You'll notice that the Population column from this last execution is aligned to the left. This is because it is believed to be a string. The GROUP_CONCAT clause concatenates values in one string, and SUBSTRING_INDEX parses a substring. The same applies to the SurfaceArea column. We'll cast Population as UNSIGNED and SurfaceArea as DECIMAL:

SELECT
  Continent,
  SUBSTRING_INDEX(
    GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),
    ',', 1) AS Name,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),
      ',', 1)
    AS DECIMAL(20,2)
    ) AS SurfaceArea,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),
      ',', 1)
    AS UNSIGNED
    ) AS Population
FROM
 Country
GROUP BY
 Continent
;
+---------------+--------------------+-------------+------------+
| Continent     | Name               | SurfaceArea | Population |
+---------------+--------------------+-------------+------------+
| Asia          | China              |  9572900.00 | 1277558000 |
| Europe        | Russian Federation | 17075400.00 |  146934000 |
| North America | Canada             |  9970610.00 |   31147000 |
| Africa        | Sudan              |  2505813.00 |   29490000 |
| Oceania       | Australia          |  7741220.00 |   18886000 |
| Antarctica    | Antarctica         | 13120000.00 |          0 |
| South America | Brazil             |  8547403.00 |  170115000 |
+---------------+--------------------+-------------+------------+

Step 4: top n records

It's time to use string walking. Examples for string walking (described in the excellent SQL Cookbook) can be found here, here and here. We'll be using a numbers table: a simple table which lists ascending integer numbers. For example, you can use the following:

DROP TABLE IF EXISTS `tinyint_asc`;

CREATE TABLE `tinyint_asc` (
 `value` tinyint(3) unsigned NOT NULL default '0',
 PRIMARY KEY (value)
) ;

INSERT INTO `tinyint_asc` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);

The trick is to apply the same technique as used above, not for a single row, but for several rows. Here's how to present the top 5 countries:

SELECT
  Continent,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),
      ',', value),
    ',', -1)
    AS Name,
  CAST(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),
        ',', value),
      ',', -1)
    AS DECIMAL(20,2)
    ) AS SurfaceArea,
  CAST(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),
        ',', value),
      ',', -1)
    AS UNSIGNED
    ) AS Population
FROM
  Country, tinyint_asc
WHERE
  tinyint_asc.value >= 1 AND tinyint_asc.value <= 5
GROUP BY
  Continent, value
;
+---------------+----------------------------------------------+-------------+------------+
| Continent     | Name                                         | SurfaceArea | Population |
+---------------+----------------------------------------------+-------------+------------+
| Asia          | China                                        |  9572900.00 | 1277558000 |
| Asia          | India                                        |  3287263.00 | 1013662000 |
| Asia          | Kazakstan                                    |  2724900.00 |   16223000 |
| Asia          | Saudi Arabia                                 |  2149690.00 |   21607000 |
| Asia          | Indonesia                                    |  1904569.00 |  212107000 |
| Europe        | Russian Federation                           | 17075400.00 |  146934000 |
| Europe        | Ukraine                                      |   603700.00 |   50456000 |
| Europe        | France                                       |   551500.00 |   59225700 |
| Europe        | Spain                                        |   505992.00 |   39441700 |
| Europe        | Sweden                                       |   449964.00 |    8861400 |
| North America | Canada                                       |  9970610.00 |   31147000 |
| North America | United States                                |  9363520.00 |  278357000 |
| North America | Greenland                                    |  2166090.00 |      56000 |
| North America | Mexico                                       |  1958201.00 |   98881000 |
| North America | Nicaragua                                    |   130000.00 |    5074000 |
| Africa        | Sudan                                        |  2505813.00 |   29490000 |
| Africa        | Algeria                                      |  2381741.00 |   31471000 |
| Africa        | Congo                                        |  2344858.00 |   51654000 |
| Africa        |  The Democratic Republic of the              |  1759540.00 |    5605000 |
| Africa        | Libyan Arab Jamahiriya                       |  1284000.00 |    7651000 |
| Oceania       | Australia                                    |  7741220.00 |   18886000 |
| Oceania       | Papua New Guinea                             |   462840.00 |    4807000 |
| Oceania       | New Zealand                                  |   270534.00 |    3862000 |
| Oceania       | Solomon Islands                              |    28896.00 |     444000 |
| Oceania       | New Caledonia                                |    18575.00 |     214000 |
| Antarctica    | Antarctica                                   | 13120000.00 |          0 |
| Antarctica    | French Southern territories                  |     7780.00 |          0 |
| Antarctica    | South Georgia and the South Sandwich Islands |     3903.00 |          0 |
| Antarctica    | Heard Island and McDonald Islands            |      359.00 |          0 |
| Antarctica    | Bouvet Island                                |       59.00 |          0 |
| South America | Brazil                                       |  8547403.00 |  170115000 |
| South America | Argentina                                    |  2780400.00 |   37032000 |
| South America | Peru                                         |  1285216.00 |   25662000 |
| South America | Colombia                                     |  1138914.00 |   42321000 |
| South America | Bolivia                                      |  1098581.00 |    8329000 |
+---------------+----------------------------------------------+-------------+------------+

 

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (팀이 존재 하는 동일등수)  (0) 2017.01.16
mysql rank 구하기 (동일등수)  (1) 2017.01.13
mysql Rownum 구현하기  (0) 2017.01.12
Posted by 농부지기
,

** mysql 관리 Tool **

 

 

1. URL : http://bryan7.tistory.com/109

'(DB) MySql > 설치 및 초기DB작업' 카테고리의 다른 글

5. MySQL - TABLE 생성  (0) 2017.01.22
4. MySQL - DB 접속  (0) 2017.01.22
3. MySQL - DB 생성  (0) 2017.01.22
2. MySQL - Workbench 설치  (0) 2017.01.22
1. MySql 설치  (0) 2017.01.18
Posted by 농부지기
,

** Join 후 Update하기 **

 

1. 기본 예문

   UPDATE 테이블명 as A_table

          SET 업데이트할필드명  = (SELECT 선택필드

                                                     FROM 테이블명 as B_table

                                                   WHERE A_table.공통ID = B_table.공통ID);

2. Join후 Update구문

    UPDATE [테이블A] , [테이블B] b

          SET a.컬럼1 = b.컬럼1

     WHERE a.컬럼2 = b.컬럼2 ;

    

 

3. Join후 Update구문

    UPDATE 후원테이블 A INNER JOIN 회원테이블 B
                      ON A.회원아이디 = B.회원아이디
           SET B.회원등급 = 7
      WHERE B.회원등급 = 9 AND A.후원금 >= 10000 ;

 

 

4. mysql에서는 서브쿼리의 form절과 업데이트 target 모두를 같은 테이블로 사용할 수 없다
      update tbl_a
           set no = 'a'
         where seq in (
select * from ( select seq from tbl_a where x = 'b' ) as t )
     한번더 감싸준다.

 

 

 

Posted by 농부지기
,