2021년 11월 4일 목요일

I organized a Dapper Helper class by myself

Link string configuration:

<connectionStrings>
    <add name="db" connectionString="server=.;database=db;uid=sa;pwd=123456;integrated security=false;"/>
  </connectionStrings>

DapperHelper.cs :

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace PullChargeData.Helper
{
    public class DapperHelper<T>
    {
        /// <summary>
        /// Database connection string
        /// </summary>
        private static readonly string connectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
 
        /// <summary>
                 /// Query list (return to DataTable)
        /// </summary>
        /// <returns></returns>
        public static DataTable QueryToDataTable(string sql)
        {
            DataTable table = new DataTable("MyTable");
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                var reader = con.ExecuteReader(sql);
                table.Load(reader);
                return table;
            }
        }
 
        /// <summary>
                 /// Query list
        /// </summary>
                 /// <param name="sql">query sql</param>
                 /// <param name="param">Replace parameters</param>
        /// <returns></returns>
        public static List<T> Query(string sql, object param = null)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList();
            }
        }
 
        /// <summary>
                 /// Query the first data
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirst(string sql, object param = null)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList().First();
            }
        }
 
        /// <summary>
                 /// The first data in the query did not return the default value
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault(string sql, object param = null)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList().FirstOrDefault();
            }
        }
 
        /// <summary>
                 /// Query a single data
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QuerySingle(string sql, object param = null)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList().Single();
            }
        }
 
        /// <summary>
                 /// Query a single piece of data does not return the default value
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T QuerySingleOrDefault(string sql, object param = null)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.Query<T>(sql, param).ToList().SingleOrDefault();
            }
        }
 
        /// <summary>
                 /// Addition, deletion and modification
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns>Number of rows affected</returns>
        public static int Execute(string sql, object param)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.Execute(sql, param);
            }
        }
 
        /// <summary>
                 /// Reader gets data
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static IDataReader ExecuteReader(string sql, object param)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.ExecuteReader(sql, param);
            }
        }
 
        /// <summary>
                 /// Scalar gets data
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, object param)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.ExecuteScalar(sql, param);
            }
        }
 
        /// <summary>
                 /// Scalar gets data
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T ExecuteScalarForT(string sql, object param)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                return con.ExecuteScalar<T>(sql, param);
            }
        }
 
        /// <summary>
                 /// Stored procedure with parameters
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<T> ExecutePro(string proc, object param)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                List<T> list = con.Query<T>(proc,
                    param,
                    null,
                    true,
                    null,
                    CommandType.StoredProcedure).ToList();
                return list;
            }
        }
 
 
        /// <summary>
                 /// Transaction 1-Full SQL
        /// </summary>
                 /// <param name="sqlarr">Multiple SQL</param>
        /// <param name="param">param</param>
        /// <returns></returns>
        public static int ExecuteTransaction(string[] sqlarr)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        int result = 0;
                        foreach (var sql in sqlarr)
                        {
                            result += con.Execute(sql, null, transaction);
                        }
 
                        transaction.Commit();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }
 
        /// <summary>
                 /// Transaction 2-declare parameters
        ///demo:
        ///dic.Add("Insert into Users values (@UserName, @Email, @Address)",
                 /// new {UserName = "jack", Email = "380234234@qq.com", Address = "Shanghai" });
        /// </summary>
                 /// <param name="Key">Multiple SQL</param>
        /// <param name="Value">param</param>
        /// <returns></returns>
        public static int ExecuteTransaction(Dictionary<string, object> dic)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        int result = 0;
                        foreach (var sql in dic)
                        {
                            result += con.Execute(sql.Key, sql.Value, transaction);
                        }
 
                        transaction.Commit();
                        return result;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }
    }
}

Call method

//No parameters
var list = DapperHelper<T_User>.Query("select * from T_User ").ToList();
//Check with parameters
var list = DapperHelper<T_User>.Query("select * from T_User where uid=@uid", new { uid = 1, }).ToList();
//increase
int ins = DapperHelper<T_User>.Execute("insert into T_User (uid,username) value(@uid,@username)", new { uid = 1, username = "Zhang San" });
//change
int upd = DapperHelper<T_User>.Execute("update T_User set username=@username where uid=@uid", new { username = "Li Si", uid = 1});
//delete
int del = DapperHelper<T_User>.Execute("delete from T_User where uid=@uid", new { uid = 1 });

참조 : https://programmerall.com/article/7102414933/

SqlMapperHelper - A Helper Class for Dapper-Dot-Net

Dapper is the micro-ORM developed by Sam Saffron and Marc Gravell. I use it in production code wherever possible, mostly because of its raw speed, and secondly because of it's ease of use. I understand that they use it as a kind of "drop-in" replacement to Linq to SQL at Stackoverflow.

I've written about Dapper before.   When you have a lot of database access in a web site, certain things become evident in production that may not have been obvious when you originally wrote and tested your code. In particular, under heavy load, bigger ORM's such as LINQ to SQL, Entity Framework and NHibernate cannot deliver the kind of warp-speed performance that a micro-ORM like Dapper can do. What happens is that the site slows down, some users get blank white pages, and you've got yourself a nasty production bottleneck.

Of course the judicious use of caching can be a big help, but you cannot always cache everything. Since Dapper supports any database that has an ADO.NET provider, it's a pretty easy choice to make from the start.

As a developer, after a while, one gets a notion of what operations you perform the most often with such a framework, and the logical extension of that is to author some sort of a "helper" class that makes these operations even easier. Such was the case here, and I wrote a SqlMapperUtil class that wraps the SqlMapper classes / methods in Dapper.

My approach here centers around what I typically do and so you may not find everything that you want in it, but it serves me well, and you may find that trying it out will give you some new ideas. I've seen others use a different approach, including authoring extension methods. What I wanted to do is provide simplified ways to do some of the most common operations. Since I use stored procedures a lot, you will find static helper methods to do just about anything with stored procs.

What I'll do here is "show you the code" first, then I'll make some comments, and of course you can download a fully working demo that includes a few stored procedures that you can put into the trusty Northwind database to try it out.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using Dapper;

namespace Dapper
{
public static   class SqlMapperUtil
    {
         // Remember to add <remove name="LocalSqlServer" > in ConnectionStrings section if using this, as otherwise it would be the first one.
        private static string connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;

        /// <summary>
        /// Gets the open connection.
        /// </summary>
        /// <param name="name">The name of the connection string (optional).</param>
        /// <returns></returns>
      public static SqlConnection GetOpenConnection( string name = null)
      {
          string connString = "";
        connString= name==null?connString = ConfigurationManager.ConnectionStrings[0].ConnectionString:connString = ConfigurationManager.ConnectionStrings[name].ConnectionString;
        var connection = new SqlConnection(connString);
        connection.Open();
        return connection;
      }

       public static int InsertMultiple<T>(string sql, IEnumerable<T> entities, string connectionName=null) where T : class, new()
        {
             using (SqlConnection cnn = GetOpenConnection(connectionName ))
            {
                int records = 0;

                foreach (T entity in entities)
                {
                    records += cnn.Execute(sql, entity);
                 }
                 return records;
            }
        }

        public static DataTable ToDataTable<T>(this IList<T> list)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
             for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                 table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in list)
            {
                 for (int i = 0; i < values.Length; i++)
                    values[i] = props[i].GetValue(item) ?? DBNull.Value;
                 table.Rows.Add(values);
            }
            return table;
        }

     public static DynamicParameters GetParametersFromObject( object obj, string[] propertyNamesToIgnore)
     {
         if(propertyNamesToIgnore ==null)propertyNamesToIgnore = new string[]{String.Empty};
         DynamicParameters p = new DynamicParameters();
         PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

         foreach (PropertyInfo prop in properties)
         {
             if(   !propertyNamesToIgnore.Contains(prop.Name ))
             p.Add("@" + prop.Name, prop.GetValue(obj, null));
         }
         return p;
     }

         public static void SetIdentity<T>(IDbConnection connection, Action<T> setId)
        {
            dynamic identity = connection.Query("SELECT @@IDENTITY AS Id").Single();
            T newId = (T)identity.Id;
            setId(newId);
        }


     public static object GetPropertyValue(object target, string propertyName )
     {
         PropertyInfo[] properties = target.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

         object theValue = null;
            foreach (PropertyInfo prop in properties)
            {
                 if (string.Compare(prop.Name, propertyName, true) == 0)
                {
                    theValue= prop.GetValue(target, null);
                }
            }
         return theValue;
     }

    public static void SetPropertyValue(object p, string propName, object value)
     {
         Type t = p.GetType();
         PropertyInfo info = t.GetProperty(propName);
         if (info == null)
             return ;
         if (!info.CanWrite)
             return;
         info.SetValue(p, value, null);
     }

         /// <summary>
        /// Stored proc.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="procname">The procname.</param>
        /// <param name="parms">The parms.</param>
        /// <returns></returns>
      public  static List<T> StoredProcWithParams<T>(string procname, dynamic parms, string connectionName = null)
        {
             using (SqlConnection connection = GetOpenConnection(connectionName))
             {
                 return connection.Query<T>(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList();
            }

        }

       /// <summary>
      /// Stored proc with params returning dynamic.
      /// </summary>
      /// <param name="procname">The procname.</param>
      /// <param name="parms">The parms.</param>
      /// <param name="connectionName">Name of the connection.</param>
      /// <returns></returns>
      public static List<dynamic> StoredProcWithParamsDynamic(string procname, dynamic parms, string connectionName=null)
      {
           using (SqlConnection connection = GetOpenConnection(connectionName))
           {
               return connection.Query(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList();
          }
      }

      /// <summary>
      /// Stored proc insert with ID.
      /// </summary>
      /// <typeparam name="T">The type of object</typeparam>
      /// <typeparam name="U">The Type of the ID</typeparam>
      /// <param name="procName">Name of the proc.</param>
      /// <param name="parms">instance of DynamicParameters class. This should include a defined output parameter</param>
      /// <returns>U - the @@Identity value from output parameter</returns>
      public static U StoredProcInsertWithID<T,U>(string procName, DynamicParameters  parms, string connectionName=null)
      {
           using (SqlConnection connection = SqlMapperUtil.GetOpenConnection(connectionName))
          {
              var x = connection.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure);
              return parms.Get<U>("@ID");
          }
     }

      /// <summary>
      /// SQL with params.
      /// </summary>
      /// <typeparam name="T"></typeparam>
      /// <param name="sql">The SQL.</param>
      /// <param name="parms">The parms.</param>
      /// <returns></returns>
       public  static List<T> SqlWithParams<T>(string sql, dynamic parms,string connectionnName=null)
        {
             using (SqlConnection connection = GetOpenConnection( connectionnName))
             {
                 return connection.Query<T>(sql, (object)parms).ToList();
            }
        }

       /// <summary>
       /// Insert update or delete SQL.
       /// </summary>
       /// <param name="sql">The SQL.</param>
       /// <param name="parms">The parms.</param>
       /// <returns></returns>
       public static int InsertUpdateOrDeleteSql(string sql, dynamic parms, string connectionName=null)
        {
           using (SqlConnection connection = GetOpenConnection(connectionName))
             {
                 return connection.Execute(sql, (object)parms);
            }
        }

       /// <summary>
       /// Insert update or delete stored proc.
       /// </summary>
       /// <param name="procName">Name of the proc.</param>
       /// <param name="parms">The parms.</param>
       /// <returns></returns>
       public static int InsertUpdateOrDeleteStoredProc(string procName, dynamic parms, string connectionName =null)
        {
             using (SqlConnection connection = GetOpenConnection( connectionName))
             {
                 return connection.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure );
            }
        }

       /// <summary>
       /// SQLs the with params single.
       /// </summary>
       /// <typeparam name="T"></typeparam>
       /// <param name="sql">The SQL.</param>
       /// <param name="parms">The parms.</param>
       /// <param name="connectionName">Name of the connection.</param>
       /// <returns></returns>
     public static T SqlWithParamsSingle<T>( string sql, dynamic parms, string connectionName=null)
       {
           using (SqlConnection connection = GetOpenConnection(connectionName))
           {
                 return connection.Query<T>(sql, (object) parms).FirstOrDefault();
           }
       }

     /// <summary>
     ///  proc with params single returning Dynamic object.
     /// </summary>
     /// <typeparam name="T"></typeparam>
     /// <param name="sql">The SQL.</param>
     /// <param name="parms">The parms.</param>
     /// <param name="connectionName">Name of the connection.</param>
     /// <returns></returns>
     public static System.Dynamic.DynamicObject DynamicProcWithParamsSingle<T>(string sql, dynamic parms, string connectionName=null)
     {
         using (SqlConnection connection = GetOpenConnection(connectionName))
         {
             return connection.Query(sql, (object)parms,commandType: CommandType.StoredProcedure ).FirstOrDefault();
         }
     }

     /// <summary>
     /// proc with params returning Dynamic.
     /// </summary>
     /// <typeparam name="T"></typeparam>
     /// <param name="sql">The SQL.</param>
     /// <param name="parms">The parms.</param>
     /// <param name="connectionName">Name of the connection.</param>
     /// <returns></returns>
     public static IEnumerable<dynamic> DynamicProcWithParams<T>(string sql, dynamic parms, string connectionName=null)
     {
         using (SqlConnection connection = GetOpenConnection(connectionName))
         {
             return connection.Query(sql, (object)parms, commandType: CommandType.StoredProcedure);
         }
     }

     /// <summary>
     /// Stored proc with params returning single.
     /// </summary>
     /// <typeparam name="T"></typeparam>
     /// <param name="procname">The procname.</param>
     /// <param name="parms">The parms.</param>
     /// <param name="connectionName">Name of the connection.</param>
     /// <returns></returns>
     public static T StoredProcWithParamsSingle<T>(string procname, dynamic parms, string connectionName=null)
     {
         using (SqlConnection connection = GetOpenConnection(connectionName))
         {
            return connection.Query<T>(procname, (object) parms, commandType: CommandType.StoredProcedure).SingleOrDefault();
         }
     }
    }
}

    You can see there is a method to return an open connection, with an optional parameter for the connection string name. The default value of this is null, and if it is, it returns the first connection string element in the configuration file. To use this you must add the <remove name="LocalSqlServer" /> directive at the beginning of your connectionStrings section. Otherwise, it will return the connection string by name.

    The InsertMultiple method accepts an IEnumerable of your type, iterates over it, and performs an insert on every one, all on the same connection.

    The ToDatatable method is a convenience method to convert an IList of your objects to a DataTable. I rarely use it, but I keep it in the class for convenience.

    The GetParametersFromObject method accepts your type and returns a set of Dapper DynamicParameters, optionally excluding a list of property names to ignore. This is useful since many of the underlying Dapper methods want DynamicParameters. Of course you can create your own DynamicParameters, optionally specifying properties such as a parameter being an output parameter.

    The SetIdentity method is useful when you are doing an insert and want to get back the @@IDENTITY value of the new row. You would call this method immediately after the line of code that performs your insert, and before the connection is closed.

    The GetPropertyValue and SetPropertyValue methods are, again, convenience methods. I don't often use these, but as above, I keep the methods in the class so I don't need to go hunting around for code when I do.

    The StoredProcWithParams<T> method executes a named stored proc, accepting an instance of dynamic containing the parameters, and an optional connection name.

    The   U StoredProcInsertWithID<T,U> method executes a stored proc and expects a defined output parameter to return the value to the caller.

    The SqlWithParams<T> method executes a specified SQL string with dynamic parameters and returns a List<T> of the type specified.

    The InsertUpdateOrDeleteSql method will perform an insert, Update, or Delete with the specified SQL with dynamic parameters and returns the integer result of the operation.

    The InsertUpdateOrDeleteStoredProc  method does the same thing but is used to execute a named stored procedure.

    The T SqlWithParamsSingle<T> executes the specified SQL with the supplied dynamic for parameters, and returns a single instance of the type T.

    The DynamicProcWithParamsSingle<T> executes a stored proc and returns type DynamicObject.

    The IEnumerable<dynamic> DynamicProcWithParams<T> does the same and returns an IEnumerable of type dynamic.

    The T StoredProcWithParamsSingle<T> executes a stored procedure with the specified dynamic for params and returns a single of type T that was specified.

    There are other variations of these that can be created; the idea is simply to "wrap" the lines of code needed to do some operation and make it very easy to use.

    In the downloadable demo solution, I have four sample stored procs that you can put into the Northwind Database in SQL Server; these are used in the demo code.

    There are three classes in the demo: Employee, EmployeeTerritory, and Territory. One interesting use of these is to perform mapping of subobjects. My Employee class has a property of type:

        public List<Territory> Territories { get; set; }

I do this because one employee can have more than one territory. Here is how I do the mapping:

  public static List<Employee>  GetEmployeesWithRegionAndTerritory()
        {
            List<Employee> employees;
             using (SqlConnection conn = SqlMapperUtil.GetOpenConnection("local"))
            {
                List<EmployeeTerritory> employeeTerritories;
                List<Territory> territories;

                 using (var multi = conn.QueryMultiple("GetEmployeesWithTerritory", null, commandType: CommandType.StoredProcedure))
                {
                    employees = multi.Read<Employee>().ToList();
                    employeeTerritories = multi.Read<EmployeeTerritory>().ToList(); // this is the junction table
                    territories = multi.Read<Territory>().ToList();
                    foreach (var emp in employees)
                    {
                        emp.Territories = new List<Territory>();
                            foreach (var empter in employeeTerritories)
                            {
                                foreach (var ter in territories)
                                  {
                                       if (empter.EmployeeId == emp.EmployeeID && ter.TerritoryID == empter.TerritoryId)
                                          emp.Territories.Add(ter);
                                }
                            }
                        }
                    } // end using multi
                } // end using conn

               return employees;

            } // end  method

This uses the Dapper QueryMultiple method, which returns multiple select results. You can also do this with LINQ, but I'm leaving that as an exercise for the reader.  Dapper can also support Table Valued Parameters which is a very efficient was to perform an insert of multiple objects in one go.

There's a lot more to Dapper-Dot-Net, but I hope this provides enough material to get you interested. Remember: raw speed is your friend! The SqlMapper.cs Dapper file included is the latest one as of the date of this article on 12/22/2011.

You can download the full Visual Studio 2010 demo solution here.

참조 : http://www.nullskull.com/a/10399923/sqlmapperhelper--a-helper-class-for-dapperdotnet.aspx

2021년 6월 22일 화요일

C# - Request.ServerVariables (URL, IP주소 등등)

Request Object인 ServerVariables Collection의 전체 값을 확인해보자

ServerVariables의 함수를 사용하여 IP주소, 도메인 주소 등 많은 요소들의 정보를 알아낼 수 있다.

// 클라이언트(사용자) IP 주소 (xxx.xxx.xxx.xxx)

Request.ServerVariables["REMOTE_HOST"];

// 서버 IP 주소 (xxx.xxx.xxx.xxx)

Request.ServerVariables("LOCAL_ADDR");

// 도메인 주소 (ggmouse.tistory.com)

Request.ServerVariables["HTTP_HOST"];

// 현재 경로 (/Test/ikTest.aspx)

Request.ServerVariables["PATH_INFO"];

이외에 전체 요소를 한번에 확인해보자

int loop1, loop2;

System.Collections.Specialized.NameValueCollection coll;

coll = Request.ServerVariables;

String[] arr1 = coll.AllKeys;

for (loop1 = 0; loop1 < arr1.Length; loop1++)

{

Response.Write("<pre> " + arr1[loop1] + "</pre>");

String[] arr2 = coll.GetValues(arr1[loop1]);

for (loop2 = 0; loop2 < arr2.Length; loop2++)

{

Response.Write("<pre>" + Server.HtmlEncode(arr2[loop2]) + "</pre>");

}

Response.Write("<br>");

}

2020년 11월 24일 화요일

MSSQL - RAND(), NEWID(), RANK(), ROW_NUMBER()

1. SQL SERVER에서 랜덤값을 만드는 방법

RAND()

RAND() 함수는 무작위 값을 반환.

ex) 0.9382734723487234

언제나 소수점을 포함한 무작위 값을 반환하기 때문에 정수값이 필요한 경우 ROUND() 함수와 함께 사용하는 것이 좋습니다.

SELECT ROUND(RAND() * 100, 0)

2. row별 랜덤값을 만드는 방법

RAND() 함수는 단일 랜덤값을 반환.

10개의 행이 있는 테이블에서

SELECT *, rand()

FROM

TABLENAME

를 실행 해 보면 rand 필드는 모두 같은 값을 반환.

rand 함수의 인자에 newid를 포함시켜 주면 행 별로 각각 다른 랜덤값을 조회할 수 있습니다.

SELECT RAND(cast(NEWID() AS varbinary))

(예) order by (case when 필드_SEQUENCE is null then convert(int,RAND(cast(NEWID() AS varbinary)) * 100000) else 필드_SEQUENCE end) asc

3. 중복을 제거한 랭킹을 만드는 방법 (동일 순위 있음)

다음은 랜덤값 -> 랭킹을 구하는 방법.

랭킹 정보를 조회할 때는 중복 포함/제외 여부에서 난이도가 결정 되는데요, 먼저 중복을 포함한 랭킹을 구하는 방법 입니다.

RANK()

RANK()는 랭킹(등수) 정보를 반환.

SELECT *, RANK() OVER (ORDER BY 필드이름 ASC) AS RankOrder

FROM TABLENAME

ORDER BY 필드에 동일한 값이 여러개 존재하는 경우 1등, 2등/2등/2등, 5등 으로 중복되는 순서를 건너 뜁니다.

중복되는 순서를 모두 출력하고 싶은 경우에는 DENSE_RANK 함수를 사용 합니다.

4. 중복을 완전 제거한 랭킹을 만드는 방법 (동일 순위 없음)

마지막으로 중복을 완전 제거한 랜덤값을 만드는 방법.

3. 중복을 제거한 랭킹을 만드는 방법과의 차이점은 정렬 기준이 같은 경우에도 유일한 값을 지정하는 것 입니다.

중복을 완전 제거한 랜덤값을 만들기 위해서는 ROW_NUMBER() 함수와 RANK() 함수를 적절히 응용해야 합니다.

SELECT *, ROW_NUMBER() OVER (ORDER BY round(RAND(cast(newid() as varbinary) * 1000000 ), 0))AS RankOrder