カゲンぱぱのイイカゲンメモ帳: sql

ラベル sql の投稿を表示しています。すべての投稿を表示

2019年7月10日水曜日

sql DATE_ADD

select DATE_ADD('2019-01-31', INTERVAL + 1 MONTH);
2019-02-28

select DATE_ADD('2019-01-30', INTERVAL + 1 MONTH);
2019-02-28

select DATE_ADD('2019-01-29', INTERVAL + 1 MONTH);
2019-02-28

select DATE_ADD('2019-01-28', INTERVAL + 1 MONTH);
2019-02-28

select DATE_ADD('2019-01-27', INTERVAL + 1 MONTH);
2019-02-27

2017年3月30日木曜日

INSERT INTO 複数VALUES時のON DUPLICATE KEY UPDATEの書き方

ラベル: duplicate, insert, mysql, sql

CREATE TABLE table_a (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  UNIQUE (a, b)
);

INSERT INTO table_a (a, b, c) VALUES (1, 2, 0),(3, 4, 5) ON DUPLICATE KEY UPDATE c = VALUES(c);

2016年12月6日火曜日

ADOレコーダーがあるのに、RecordCountが「-1」の場合の対処

ラベル: sql, vba, 罠

レコーダーがあるのに、RecordCountが「-1」の場合の対処



connect.CursorLocation = 3 'クライアントカーソルにする

Set rs = execQuery(cn, sql, , adOpenKeyset, adLockOptimistic)

debug.print rs.RecordCount

2016年7月15日金曜日

ado,sqlのunderline…自分かバカすぎで笑えない

ラベル: mysql, sql, vba, 罠

sqlではアンダーライン"_"は、任意の一文字って意味か、ADOでもMysqlでも...、やられた、ADOでは"[]"でエスケープする、sqlでは"\"でエスケープする。
正規表現とごっちゃまぜしちゃダメよ。(;_;)



SELECT * FROM tbl WHERE tname LIKE 'abc[_]%'

SQL



SELECT * FROM tbl WHERE tname LIKE 'abc\_%'

2016年6月30日木曜日

VBAのRoundの罠

ラベル: round, sql, vba, 罠

ROUND(38.5)

結果：38

ROUND(39.5)

結果：40

「銀行家の丸め (bankers' rounding)」、「銀行丸め」ともいう。
5が切り捨てられたり切り上げられたりするので「五捨五入」と呼ばれたり、
端数がちょうど0.5の場合に整数部分が偶数なら切り捨て奇数なら切り上げる
ので「偶捨奇入」と呼ばれたりもする。

本当の「四捨五入」したい場合は



WorksheetFunction.Round()

が有効だそうです。
しかし、ADODB,Jet.OLEDBなどでQuery（クエリー）で運用時は使えないので



sql = "UPDATE MyTable SET `Ammount` = ROUND(35*1.1)"

Call csv_cn.Execute(sql)



=ROUND(38.5) = 38

↓



sql = "UPDATE MyTable SET `Ammount` = FORMAT(35*1.1,""#"")"

Call csv_cn.Execute(sql)



=ROUND(38.5) = 39

ちなみに、小数点以下2位の四捨五入は



sql = "UPDATE MyTable SET `Ammount` = FORMAT(35*1.111,""#.00"")"

Call csv_cn.Execute(sql)

2014年10月22日水曜日

SQLでbit演算,合計値格納項目のクエリー方法

ラベル: mysql, sql

DB定義書ではこのような項目がありました

my_field NULL →　1:ああああ　2:いいいい　4:うううう　8:ええええ　16:おおおお　(合計値格納)

つまり複数選択可能な列です。
例えば「1:ああああ　2:いいいい」が選択されてDBに登録したらmy_fieldの値は1+2=「3」です。

そうするとクエリーの書き方は：
例えば「8:ええええ」に含まれるレコードを抽出したいときは：



......... where my_field & 8 = 8

でOKです。

2014年6月27日金曜日

SQLクエリー、リレーションテーブルの行を列にして、マトリックスを作る

ラベル: mysql, sql

こういうことをやりたい：

ダイナミックで縦列を作成するにはこのスレ
シンプルで手作業でいいんなら

まずサンプルデータを作る



create table user (id int , name varchar(10));

insert into user values

(1,'AA'),

(2,'BB'),

(3,'CC'),

(4,'DD'),

(5,'EE'),

(6,'FF'),

(7,'GG');



create table role (id int ,role varchar(10));

insert into role values

(1,'FW'),(2,'DF'),(3,'GK'),(4,'MF') ;



create table user_role (user_id int ,role_id int);

insert into user_role values

(1,1),(1,2),(2,1),(2,3),(3,4),(4,2),(5,1),(5,2),(6,2),(6,3),(7,1),(7,4);

そしてクエリーは



select 

id,

name,

coalesce(max(t.FW),'No') as FW,

coalesce(max(t.DF),'No') as DF,

coalesce(max(t.GK),'No') as GK,

coalesce(max(t.MF),'No') as MF

from user u

left join (

  select 

  case 

    when r.id = 1 AND ur.role_id is not null then 'Yes'

    else null

  end `FW`,

  

  case 

    when r.id = 2 AND ur.role_id is not null then 'Yes'

    else null 

  end `DF`,

  

  case 

    when r.id = 3 AND ur.role_id is not null then 'Yes'

    else null

  end `GK`,

  

  case 

    when r.id = 4 AND ur.role_id is not null then 'Yes'

    else null

  end `MF`,

  user_id

  from role r

  left join user_role ur on ur.role_id = r.id

)t

on t.user_id = u.id

group by u.id

クエリー結果

ID	NAME	FW	DF	GK	MF
1	AA	Yes	Yes	No	No
2	BB	Yes	No	Yes	No
3	CC	No	No	No	Yes
4	DD	No	Yes	No	No
5	EE	Yes	Yes	No	No
6	FF	No	Yes	Yes	No
7	GG	Yes	No	No	Yes

質問したURL
http://stackoverflow.com/questions/24425234/how-can-i-get-a-matrix-table-from-two-related-table-by-one-query-statement/24426021#24426021
Abhik Chakrabortyさん、ありがとうございました。

DEMO

クエリーシミュレーション、すげー使えるサイト
http://sqlfiddle.com

2014年3月6日木曜日

mysqlの日付の比較メソッド,datediffとperiod_diff

ラベル: date, datediff, mysql, sql



select abs(datediff('2013-01-01','2014-01-01'));

#365

select period_diff(date_format('2014-03-06', '%Y%m'), date_format('2012-03-06', '%Y%m')) as month;

#24

2012年11月22日木曜日

SQLOLEDB INSERT トランザクション付き

ラベル: sql, vba

Sub InsertDB()
    Global cnn As New ADODB.Connection
    cnn.Open "Provider=SQLOLEDB;" & _
             "Data Source=" & srvName & ";" & _
             "Initial Catalog=" & databaseName & ";", user, pass

    Dim cmdIns As ADODB.Command
    Set cmdIns = New ADODB.Command
    cmdIns.ActiveConnection = cnn
    cmdIns.CommandTimeout = 0
    cmdIns.CommandType = adCmdText
    cmdIns.CommandText = "INSERT INTO myTable(id,name,price,bigmoney) VALUES(?,?,?,?)"

    'DB定義: id int
    Set param = cmdIns.CreateParameter("id", adInteger, adParamInput)
    cmdIns.Parameters.Append param

    'DB定義: name nvarchar(50)
    Set param = cmdIns.CreateParameter("name", adVarWChar, adParamInput, 100)
    cmdIns.Parameters.Append param

    'DB定義: price decimal(12, 6)
    Set param = cmdIns.CreateParameter("price", adDecimal, adParamInput)
    param.Precision = 12 '全桁数
    param.NumericScale = 6 '小数桁数　こんな感じ：123456.123456
    cmdIns.Parameters.Append param

    'DB定義: price bigmoney(12, 0)
    Set param = cmdIns.CreateParameter("bigmoney", adDecimal, adParamInput)
    param.Precision = 12 '全桁数
    param.NumericScale = 0 '小数桁数　こんな感じ：123456123456
    cmdIns.Parameters.Append param

    cmdIns.Parameters("id").Value = 1
    cmdIns.Parameters("name").Value = "かげんぱぱ"
    cmdIns.Parameters("price").Value = 1999.123
    cmdIns.Parameters("bigmoney").Value = 10000000000

    cnn.BeginTrans 'トランザクション開始
    cmdIns.Execute 'インサート実行
    'cnn.RollbackTrans 'ロールバック
    cnn.CommitTrans 'コミット
End Sub

2012年8月17日金曜日

sql、カレンダー作成

ラベル: mysql, sql

2010/4/1から2012/5/31までのカレンダーを作成



Declare @date table(d datetime)

Declare @d datetime



set @d='20100401'



While @d<='20120531'

Begin

 Insert into @date values (@d)

 set @d=@d+1

End

Select d as date_ymd into calendar from @date

エクセルでデータベースを参照するサンプル、SQL ServerとmySQL対応

ラベル: excel, mysql, sql, vba

シート1の名前を「"テーブル一覧"」にして、ボタンを追加する
そして、I列の1から5行目、下記の情報を入れる
サーバー
DB名
ユーザ
パスワード
接続DBタイプ["SQL Server","MySQL ODBC 5.1 DRIVER"]
※今のところ、MS SQL Serverとmysqlしか対応していない
シート"テーブル一覧"は

Private Sub CommandButton1_Click()
 Call getTables
End Sub

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    'MsgBox Target.TextToDisplay
    getTableData Target.TextToDisplay
End Sub

モジュールに下記のコード

Public con As ADODB.Connection
Public wb As Workbook
Public st As Worksheet
Public gDB As String
Public gConDriver As String
Dim connectionString As String


Sub initDBcon()


Set wb = ThisWorkbook
Set st = wb.Worksheets("テーブル一覧")
'接続文字列
gConDriver = st.Range("I5")


Select Case gConDriver
    Case "SQL Server"
        connectionString = "Provider=Sqloledb;" _
                & " Data Source=" & st.Range("I1") & ";" _
                & " Initial Catalog=" & st.Range("I2") & ";" _
                & " Connect Timeout=15;" _
                & " user id=" & st.Range("I3") & ";" _
                & " password=" & st.Range("I4")
    Case "MySQL ODBC 5.1 DRIVER"
        connectionString = "Driver={" & gConDriver & "};" _
                & " SERVER=" & st.Range("I1") & ";" _
                & " DATABASE=" & st.Range("I2") & ";" _
                & " USER=" & st.Range("I3") & ";" _
                & " PASSWORD=" & st.Range("I4") & ";"
End Select

gDB = st.Range("I2")

'ADODB.Connection生成
Set con = New ADODB.Connection
On Error GoTo Err

'MySQLに接続
con.Open connectionString
Exit Sub
Err:
    Set con = Nothing
    MsgBox (Err.Description)

End Sub
Sub getTables()

Dim rs As ADODB.Recordset


Dim sqlStr As String

Dim rowNo As Integer
Dim colNo As Integer
Dim item As Variant


Call initDBcon
If con Is Nothing Then
    Exit Sub
End If
Application.DisplayAlerts = False
For Each stname In wb.Sheets
    If stname.Name <> "テーブル一覧" Then
        stname.Delete
    End If
Next
Application.DisplayAlerts = True

'SQL文
Select Case gConDriver
    Case "SQL Server"
        sqlStr = "select table_name from information_schema.tables;"
    Case "MySQL ODBC 5.1 DRIVER"
        sqlStr = "show tables;"
End Select


'SQL文実行
Set rs = con.Execute(sqlStr)

'シートデータクリア
'st.Cells.Clear
st.Columns("A:A").Clear
rowNo = 1
Do While rs.EOF = False
    'データ抽出
    For Each item In rs.Fields
        st.Range("A" & rowNo).Value = item.Value
        st.Range("A" & rowNo).Hyperlinks.Add Anchor:=st.Range("A" & rowNo), _
        Address:="", TextToDisplay:=item.Value
    Next
    rowNo = rowNo + 1
    '次のレコード
    rs.MoveNext
Loop

'クローズ
con.Close
Set rs = Nothing
Set con = Nothing

Exit Sub


End Sub

Sub getTableData(ByVal srtTableName As String)
Dim stname As Variant
Dim qt As QueryTable

Call initDBcon
    For Each stname In wb.Sheets
        If stname.Name = srtTableName Then
            Exit Sub
        End If
    Next
    wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)).Name = srtTableName
    
On Error GoTo Err
Select Case gConDriver
Case "MySQL ODBC 5.1 DRIVER"
    With wb.Worksheets(srtTableName).ListObjects.Add(SourceType:=xlSrcExternal, Source:=Array(Array( _
        "ODBC;" & connectionString), Array("E=db35211_CustomerMasterList;DefaultTable=Customers;")), _
        Destination:=wb.Sheets(srtTableName).Range("$A$1")).QueryTable
        .CommandText = Array("Select * FROM " & srtTableName)
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "テーブル_" & gDB & "_" & srtTableName
        .Refresh BackgroundQuery:=False
    End With
Case "SQL Server"
    With wb.Worksheets(srtTableName).ListObjects.Add(SourceType:=xlSrcExternal, Source:=Array( _
        "OLEDB;" & connectionString), Destination:=wb.Sheets(srtTableName).Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("""" & gDB & """.""dbo"".""" & srtTableName & """")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "テーブル_" & gDB & "_" & srtTableName
        .Refresh BackgroundQuery:=False
    End With
End Select
    Exit Sub
    
Err:
    Set con = Nothing
    MsgBox (Err.Description)

End Sub

2012年6月28日木曜日

SQL日付フォーマットの変換

ラベル: sql

SQL日付フォーマットの変換



[yyyyMMdd]

SELECT CONVERT(char(8), GETDATE(), 112);



[yyyyMM]

SELECT CONVERT(char(6), GETDATE(), 112);



[hhmmss]

SELECT REPLACE(CONVERT(varchar(8), GETDATE(), 8), ':', '');



[hhmmssfff]

SELECT REPLACE(CONVERT(varchar(12), GETDATE(),114), ':', '');



[次月yyyyMMdd]

SELECT CONVERT(char(8), DateAdd(Month, 1 , GETDATE()), 112);

引用、ありがとう！

2012年6月25日月曜日

sql serverのinsert文では複数組valuesを対応しない

ラベル: sql

sql serverでは、複数insert文はできないことを知った＿|￣|● ｶﾞｯｸﾘ
mysqlなら下のコードはすんなり行ったと思うが



insert into mytable (id,name,sex,age) values (1,'john','male',22),(2,'jane','female',21);

sql serverではinsert文を分けるしかないか



insert into mytable (id,name,sex,age) values (1,'john','male',22);

insert into mytable (id,name,sex,age) values (2,'jane','female',21);

まぁ関係ない関係ない.

2011年12月28日水曜日

ラベル: sql

fund_indicesは銘柄の基準価額、純資産や分配金の履歴テーブル、巨大なテーブルです。
やりたいことは重複なしでランダムで３銘柄IDを抽出したい。fund_idじゃなくidはプライマリーキーです。
fund_idは重複している。データ全部で280万くらい。

id	fund_id	as_of_date	value
1	529	20111130	5394
2	529	20111129	5384
3	529	20111128	5323
4	529	20111125	5312
5	529	20111124	5312
6	529	20111122	5366
7	529	20111121	5397
8	545	20111129	5374
9	545	20111128	5373
10	545	20111125	5372
11	545	20111124	5312
12	545	20111122	5376
13	545	20111121	5377
...	...	...	...

mysqlのorder by rand()を使ったが、凄く遅い！なんと1分もかかります。



sql> select distinct fund_id from fund_indices order by rand() limit 3;

fund_idを絞るサブクエリを入れると凄く早くなった



sql> select fund_id from (select distinct fund_id from fund_indices) order by rand() limit 3;

表示中の列 0 - 2 (~31 合計, クエリの実行時間 0.0495 秒)

ID	NAME	FW	DF	GK	MF
1	AA	Yes	Yes	No	No
2	BB	Yes	No	Yes	No
3	CC	No	No	No	Yes
4	DD	No	Yes	No	No
5	EE	Yes	Yes	No	No
6	FF	No	Yes	Yes	No
7	GG	Yes	No	No	Yes

ID	NAME	FW	DF	GK	MF
1	AA	Yes	Yes	No	No
2	BB	Yes	No	Yes	No
3	CC	No	No	No	Yes
4	DD	No	Yes	No	No
5	EE	Yes	Yes	No	No
6	FF	No	Yes	Yes	No
7	GG	Yes	No	No	Yes

ID	NAME	FW	DF	GK	MF
1	AA	Yes	Yes	No	No
2	BB	Yes	No	Yes	No
3	CC	No	No	No	Yes
4	DD	No	Yes	No	No
5	EE	Yes	Yes	No	No
6	FF	No	Yes	Yes	No
7	GG	Yes	No	No	Yes