본문 바로가기

MSSQL6

[예제로 배우는 MS-SQL] 일련번호 생성 함수 - ROW_NUMBER [MSSQL] ROW_NUMBER 일련번호를 생성하는 함수입니다. - PARTITION BY : 일련번호 생성의 그룹을 지정합니다. (생략가능) - ORDER BY : 일련번호를 생성할 순번을 지정합니다. 【예제】 SELECT ROW_NUMBER() OVER(PARTITION BY GroupCode ORDER BY AreaCode ASC, DialCount DESC) AS 'Order' 【데이타】 GroupCode AreaCode DialCount A 1 0 A 1 1 A 2 1 A 3 2 B 1 0 B 1 1 B 2 1 B 3 1 B 1 1 【실행】 SELECT ROW_NUMBER() OVER(PARTITION BY GroupCode ORDER BY AreaCode ASC, DialCount DESC).. 2011. 3. 22.
[예제로 배우는 MS-SQL] 숫자를 엔표시로 변환 - REPLACE, CONVERT, CAST DROP FUNCTION ToMoneyJp GO /*****************************************************/ /* 引数を円として表示 */ /*****************************************************/ CREATE FUNCTION ToMoneyJp(@STR varchar(50)) RETURNS VARCHAR(50) AS BEGIN SET @STR = replace(convert(varchar(18),cast(@STR as money),1),'.00','') + '円' RETURN @STR END GO GRANT EXECUTE ON ToMoneyJp TO af_user GO 2009. 11. 20.
[예제로 배우는 MS-SQL] 전각문자 공백제거 - REPLACE, LTRIM, RTRIM DROP FUNCTION ToBothTrim GO /*************************************************/ /* 文字列の両方Trimする関数 */ /*************************************************/ /* 引数 : @STR → 文字列 */ /* @DIVISION → half(半角), full(全角) */ /* */ /* ※ 結果 */ /* △→半角スペース、□→全角スペース */ /* */ /* ① 半角Trim */ /* 引数 : △△A△B△C△△, half */ /* 結果 : A△B△C */ /* ① 全角Trim */ /* 引数 : △□文△字□列△□, full */ /* 結果 : 文□字□列 */ /* */ /**************.. 2009. 11. 20.
[VBA] 엑셀에서 MSSQL Server 접속하기 ADODB.Connection ADODB.Recordset ADODB.Command 참조링크 http://www.red.oit-net.jp/tatsuya/vb/ADO.htm http://acvb.s19.xrea.com/doc/015.html http://blogs.msdn.com/nakama/archive/2008/10/16/ado.aspx http://www.geocities.jp/cbc_vbnet/ADO/recordset.html http://www.red.oit-net.jp/tatsuya/vb/ADO.htm http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/vb_s12.htm VBでADO利用EXCEL接続 http://support.microsoft.com/kb/.. 2009. 11. 20.
[VBA] 엑셀 -> MS SQL 엑셀 -> MS SQL [大阪府、時間差] Private Const TAGET_SHEET As String = "通計対象_CTI着信ログ" '通計対象シート Private Const DIALBEGIN_ROW As String = "G2" 'DialBeginDatetimeの最初のデータ位置 Private Const OPCONNECT_ROW As String = "K2" 'OpConnectDateTimeの最初のデータ位置 Private Const PRINT_ROW As String = "A6" '出力基準位置 Private Const NOT_CONNECT As String = "1970/01/01" '未応対 Private Const RESULT_ARR As Long = 9 '通計区分数 Private Const RESU.. 2009. 11. 20.
[VBA] 엑셀 -> MS-SQL 엑셀 -> MS-SQL データ取込み ' 注意 ' ツール > 参照設定 > Microsoft ActiveX Data Objects x.x Library 設置が必要 Private Const DB_Connection As String = "Provider=SQLOLEDB.1;Password=암호;Persist Security Info=True;User ID=아이디;Initial Catalog=DB명;Data Source=서버이름" Private Const INSERT_COLUMN As String = "(ID, JobID, TableName, FieldName, LabelName, TabIndex, ViewIndex, SortOrder, ConditionFlag, ColmunIndex, LabelForeColo.. 2009. 11. 20.

티스토리툴바