ＳＱＬの窓 イラストAC フリー素材
TOP / DB関連
- 1 2 3 4 5.. >>

2019年06月27日

複数行のグループデータを1行にまとめるSQL

縦のデータを



case で行毎の表示列を作成する

select 
	コード

	,case 行 
		when 1 then 内容
		else null
	end as 内容1

	,case 行 
		when 2 then 内容
		else null
	end as 内容2

	,case 行 
		when 3 then 内容
		else null
	end as 内容3

 from data




後は、group と max で一つの行に

select 
	コード

	,max(case 行 
		when 1 then 内容
		else null
	end) as 内容1

	,max(case 行 
		when 2 then 内容
		else null
	end) as 内容2

	,max(case 行 
		when 3 then 内容
		else null
	end) as 内容3

 from data
 group by コード







CREATE TABLE `data` (
  `コード` int(11) NOT NULL DEFAULT '0',
  `行` int(11) NOT NULL DEFAULT '0',
  `内容` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`コード`,`行`)
)



MySQL でのサンプルですが、SQLServer、PostgreSQL、Oracle でも可能です。

各構文のサンプルはこちらから

※ 分解には、UNION を使います。
タグ：データベース SQL
posted by at 2019-06-27 23:09 | DB関連 | このブログの読者になる | 更新情報をチェックする

2019年02月11日

テーブル設計書作成 : VBScript + Excel.Application + SQLServer

『作成される Excel ブック』のコメントの下で、table_spec_ss.xlsx となっていますが、拡張子を .xls で作成するには Call CreateBook.SaveAs( BookPath ) を Call CreateBook.SaveAs( BookPath, 56 ) に変更する必要があります

※ 実行はコマンドプロンプトから、cscript table-spec.wsf として下さい。
※ Excel でテーブル設計書を作成する部分は他のデータベースでも可能ですが、それ用の SQL を考える必要があります。

関連する記事

VBScript : Excel の新しいBookを作成する
SQLExpress 2005( および 2008 ) の接続設定


1) 対象テーブルリストにテーブル名を列挙する
<RESOURCE id="TableList">
 
社員マスタ
商品マスタ
 
</RESOURCE>

2) table_spec_ss.xls が作成される Excel ブックです
   実行毎に上書きされるので注意して下さい

3) 作成される場所は、このスクリプトが置かれているディレクトリです

4) データベース接続情報には、自分の環境を記述してください
' **********************************************************
' データベース接続情報
' **********************************************************
strSERVER = ".\sqlexpress"
strDATABASE = "lightbox"
strUSER = "sa"
strPASS = "password"

5) Excel に対する処理経過を見たい場合は
   ExcelApp.Visible = True にして下さい

6) strSystemName を変更してタイトルの「システム名」を変更

7) strSubName を変更してタイトルの「サブシステム名」を変更

8) xlPaperB4 は B4 フォーマットです





table-spec.wsf

<JOB>
<RUNTIME>
<DESCRIPTION>
*******************************************************************
 プログラム名 : SQLServer 用表情報のレポート (複数実行)
*******************************************************************
</DESCRIPTION>
<EXAMPLE>

引数無し
</EXAMPLE>
</RUNTIME>

<COMMENT>
************************************************************
 対象テーブルリスト
************************************************************
</COMMENT>
<RESOURCE id="TableList">

社員マスタ
商品マスタ

</RESOURCE>

<COMMENT>
************************************************************
 オブジェクト定義
************************************************************
</COMMENT>
<REFERENCE object="ADODB.Connection" />
<OBJECT id="Cn" progid="ADODB.Connection" />
<OBJECT id="Rs" progid="ADODB.Recordset" />
<OBJECT id="ExcelApp" progid="Excel.Application" />
<OBJECT id="WshShell" progid="WScript.Shell" />
<OBJECT id="Fso" progid="Scripting.FileSystemObject" />

<COMMENT>
************************************************************
 外部スクリプト定義
************************************************************
</COMMENT>

<COMMENT>
************************************************************
 カレントスクリプト
************************************************************
</COMMENT>

<SCRIPT language=VBScript>

Dim str,strMessage,Target,strCurDir

' **********************************************************
' 作成される Excel ブック
' **********************************************************
Target = "table_spec_ss.xlsx"
strCurDir = WScript.ScriptFullName
strCurDir = Replace( strCurDir, WScript.ScriptName, "" )
Target = strCurDir & Target

Dim strSERVER,strDATABASE,strUSER,strPASS

' **********************************************************
' データベース接続情報
' **********************************************************
strSERVER = ".\sqlexpress"
strDATABASE = "lightbox"
strUSER = "sa"
strPASS = "password"

' **********************************************************
' コマンドプロンプトより起動される為の処理
' **********************************************************
str = WScript.FullName
str = Right( str, 11 )
str = Ucase( str )
if str <> "CSCRIPT.EXE" then
	strMessage = "コマンドプロンプトより cscript " & WScript.ScriptFullName
	strMessage = strMessage & " と指定して実行して下さい　　　" & vbCrLf & vbCrLf
	strMessage = strMessage & "( この文字列をクリップボードにコピーしたい場合は"
	strMessage = strMessage & " ctrl+c です )"
	WScript.Echo strMessage
	WScript.Quit
end if

' **********************************************************
' Excel 用定数
' **********************************************************
' https://docs.microsoft.com/ja-jp/dotnet/api/microsoft.office.interop.excel.xlpapersize?view=excel-pia
Const xlPaperB4 = 12

Const xlContinuous = 1
Const xlDash = -4115
Const xlDashDot = 4
Const xlDashDotDot = 5
Const xlDot = -4118
Const xlDouble = -4119
Const xlSlantDashDot = 13
Const xlLineStyleNone = -4142

Const xlHairline = 1
Const xlMedium = -4138
Const xlThick = 4
Const xlThin = 2

Const xlInsideHorizontal = 12
Const xlInsideVertical = 11
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeBottom = 9
Const xlEdgeLeft = 7
Const xlEdgeRight = 10
Const xlEdgeTop = 8

Const xlAutomatic = -4105

Const xlMaximized = -4137
Const xlMinimized = -4140
Const xlNormal = -4143

Const xlGeneral = 1
Const xlLeft = -4131
Const xlCenter = -4108
Const xlRight = -4152
Const xlFill = 5
Const xlJustify = -4130
Const xlCenterAcrossSelection = 7
Const xlDistributed = -4117
Const xlTop = -4160
Const xlBottom = -4107

Dim aTable,Result,Query,Query2,nCnt,strCol,aCol,nRows,nRowHeight
Dim MyBook,bAction,strSystemName,strSubName
Dim nColX,nColY,strSheetName,strBaseSheet
Dim aData

ExcelApp.Visible = False
strBaseSheet = "テーブル設計雛形"
strSystemName = "販売管理システム"
strSubName = "売上管理"
Query = "sp_columns "

' **********************************************************
' Excel 初期処理
' **********************************************************
aTable = Split( getResource("TableList"), vbCrLf )

' **********************************************************
' ブック作成
' **********************************************************
Set MyBook = CreateBook(Target)
if not IsObject( MyBook ) then
	' 初期処理エラーの為、直接終了
	ExcelApp.Quit
	' エラーメッセージの表示
	Wscript.Echo MyBook
	Wscript.Quit
end if

' **********************************************************
' DB 接続
' **********************************************************
Result = SQS_DBConnect( Cn, strSERVER, strDATABASE, strUSER, strPASS )
if Result <> "" then
	Wscript.Echo Result
	Wscript.Quit
end if

' **********************************************************
' 読み出し
' **********************************************************
Wscript.Echo "処理を開始しました。しばらくお待ち下さい"
bAction = False
For i = 0 to Ubound( aTable )
	if Trim( aTable(i) ) <> "" then
		if DBGet( Cn, Rs, Query & Ss( aTable(i) ), False ) then
			' ************************************************
			' Excel 処理
			' ************************************************
			nColX = 2
			nColY = 6
			if not bAction then
				bAction = True
				Call AddSheetLast( MyBook, strBaseSheet )
				Call DeleteSheet(MyBook,"Sheet1" )
				Call ColumnInit(MyBook,strBaseSheet)
				Wscript.Echo strBaseSheet & " を追加しました"
			end if
			strSheetName = ExcelCopySheet( MyBook, strBaseSheet, aTable(i) )
			strSaveSheetName = strSheetName
			Call ExcelSetCell(MyBook, strSheetName, 2, 2, strSystemName )
			Call ExcelSetCell(MyBook, strSheetName, 4, 2, strSubName )
			Call ExcelSetCell(MyBook, strSheetName, 10, 2, aTable(i) )
			Call ExcelSetCell(MyBook, strSheetName, 2, 4, aTable(i) )

			nCnt = 1
			nRowCnt = 1
			nSheetCount = 1
			Redim aData(0)
			Do While not Rs.EOF
				Redim Preserve aData(nCnt)
				aData(nCnt) = Rs.Fields("COLUMN_NAME").value & ""
				' ************************************************
				' Excel 処理
				' ************************************************
				Call ExcelSetCell(MyBook, strSheetName, nColX, nColY, Rs.Fields("COLUMN_NAME").value & "")
				Call ExcelSetCell(MyBook, strSheetName, nColX+2, nColY, Ucase(Rs.Fields("TYPE_NAME").value & "") )
				if Ucase( Rs.Fields("TYPE_NAME").value & "" ) = "VARCHAR" or _
					Ucase( Rs.Fields("TYPE_NAME").value & "" ) = "CHAR" or _
					Ucase( Rs.Fields("TYPE_NAME").value & "" ) = "NVARCHAR"or _ 
					Ucase( Rs.Fields("TYPE_NAME").value & "" ) = "NCHAR" then
					Call ExcelSetCell(MyBook, strSheetName, nColX+3, nColY, Rs.Fields("PRECISION").value & "")
				end if
				if Ucase( Rs.Fields("TYPE_NAME").value & "" ) = "DECIMAL" then
					Call ExcelSetCell(MyBook, strSheetName, nColX+3, nColY, Rs.Fields("PRECISION") & "")
					Call ExcelSetCell(MyBook, strSheetName, nColX+4, nColY, Rs.Fields("SCALE").value & "")
				end if
				nColY = nColY + 1

				Rs.MoveNext
				nCnt = nCnt + 1
				nRowCnt = nRowCnt + 1
				if nRowCnt > 65 then
					nRowCnt = 1
					nColX = 2
					nColY = 6
					nSheetCount = nSheetCount + 1
					strSheetName = ExcelCopySheet( MyBook, strBaseSheet, aTable(i) & "(" & nSheetCount & ")" )
					Call ExcelSetCell(MyBook, strSheetName, 2, 4, aTable(i) )
				end if
			Loop
			strSheetName = strSaveSheetName

			Result = ""
			Query2 = "sp_helpindex "
			if DBGet( Cn, Rs, Query2 & Ss( aTable(i) ), False ) then
				Do While not Rs.EOF
					if Left( Rs.Fields("index_name").value & "", 1 ) <> "_" then
						if InStr( Rs.Fields("index_description").value & "" , "primary key" ) > 0 then

							nCnt = 1
							strCol = Rs.Fields("index_keys").value & ""
							aCol = Split( strCol, ", " )

							For j = 0 to Ubound( aCol )
								For k = 1 to Ubound( aData )
									if aCol( j ) = aData( k ) then
										Call ExcelSetCell(MyBook, strSheetName, 11, 5 + k, j + 1 )
									end if
								Next
							Next

							Exit Do

						end if

					end if
					Rs.MoveNext
				Loop
			end if

			Wscript.Echo strSheetName & " を追加しました"
		end if
	end if

Next


' **********************************************************
' 接続解除
' **********************************************************
Call DBClose( Rs )
Call DBClose( Cn )

' **********************************************************
' 処理終了
' **********************************************************
Call ExcelSave(MyBook)
Call ExcelQuit(MyBook)

Wscript.Echo "処理が完了しました"

Call ExcelLoad(Target)


' **********************************************************
' Excel 出力用 初期処理
' **********************************************************
Function ColumnInit(MyBook,SheetName)

	' 行数
	nRows = 65
	nRowHeight = 13.5

	Call Format_Table(MyBook,SheetName)
	Call Format_Page(MyBook)

End Function

' **********************************************************
' Excel 処理関数
' **********************************************************
' ******************************************************
' シート名によるシート複写
' ******************************************************
Function ExcelCopySheet(MyBook, strSheetName, strNewSheetName)

	MyBook.Sheets(strSheetName).Copy (MyBook.Sheets(strSheetName))

	on error resume next
	MyBook.ActiveSheet.Name = strNewSheetName
	on error goto 0

	ExcelCopySheet = MyBook.ActiveSheet.Name

End Function

' ************************************************
' Excel ブック作成
' ************************************************
function CreateBook(BookPath)

	ExcelApp.DisplayAlerts = False

	ExcelApp.Workbooks.Add
	nBooks = ExcelApp.Workbooks.Count
	Set CreateBook = ExcelApp.Workbooks( nBooks )
	CreateBook.Activate

	on error resume next
	Call CreateBook.SaveAs( BookPath )
	if Err.Number <> 0 then
		CreateBook = "ERROR:" & Err.Description
	end if
	on error goto 0

End Function

' ******************************************************
' 終了
' ******************************************************
Function ExcelQuit(WorkBook)

	If TypeName(WorkBook) = "Workbook" Then
		' 保存した事にする
		WorkBook.Saved = True
	End If
	If IsObject(ExcelApp) Then
		ExcelApp.Quit
	End If

End Function

' ******************************************************
' セルへのデータセット
' ******************************************************
Function ExcelSetCell(MyBook, strSheetName, x, y, Data)

	MyBook.Sheets(strSheetName).Cells(y, x) = Data

End Function

' ******************************************************
' 先頭にシートを追加
' ******************************************************
function AddSheetTop( MyBook, SheetName )

	Dim Worksheet
	Dim Worksheet2

	Set Worksheet = MyBook.Worksheets( 1 )
	Worksheet.Activate
	Workbook.Worksheets.Add
	Set Worksheet2 = Workbook.ActiveSheet
	on error resume next
	Worksheet2.Name = SheetName
	on error goto 0

	AddSheetTop = Worksheet2.Name

end function

' ******************************************************
' 最後にシートを追加
' ******************************************************
function AddSheetLast( MyBook, SheetName )

	Dim Worksheet
	Dim Worksheet2
	Dim nSheets

	nSheets = MyBook.Worksheets.Count
	Set Worksheet = MyBook.Worksheets( nSheets )
	Worksheet.Activate
	Call MyBook.Worksheets.Add(,Worksheet)
	Set Worksheet2 = MyBook.ActiveSheet
	on error resume next
	Worksheet2.Name = SheetName
	on error goto 0

	AddSheetLast = Worksheet2.Name

end function

' ******************************************************
' Excel 実行 ( NT5.0 以上 )
' ******************************************************
Function ExcelLoad(strPath)

	Call WshShell.Run( "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & _
		strPath )

End Function

' ******************************************************
' 上書き保存
' ******************************************************
Function ExcelSave(MyBook)

	MyBook.Save

End Function

' ******************************************************
' シートを削除
' ******************************************************
function DeleteSheet( MyBook, SheetName )

	Dim Worksheet

	Set Worksheet = MyBook.Worksheets( SheetName )

	on error resume next
	Worksheet.Delete
	on error goto 0

end function

' ******************************************************
' 指定行の高さを設定
' ******************************************************
Function ExcelSetRowHeight(MyBook, strSheetName, row, Height)

	MyBook.Sheets(strSheetName).Rows(row).RowHeight = _
	Height

End Function

' ******************************************************
' 指定列の幅を設定
' ******************************************************
Function ExcelSetColumnWidth(MyBook, strSheetName, column, Width)

	Dim strColumn

	strColumn = Chr(Asc("A") + column - 1)

	MyBook.Sheets(strSheetName).Columns(strColumn).ColumnWidth = _
	Width

End Function

' ******************************************************
' 範囲選択
' ******************************************************
Function ExcelRange(MyBook, strSheetName, nX1, nY1, nX2, nY2 )

	Dim Sheet,strRange

	Set Sheet = MyBook.Sheets(strSheetName)
	Sheet.Select
	strRange = Chr(Asc("A") + nX1 - 1) & nY1 & ":"
	strRange = strRange & Chr(Asc("A") + nX2 - 1) & nY2
	Sheet.Range(strRange).Select

End Function

' ******************************************************
' 範囲の上に罫線
' ******************************************************
Function ExcelLine( nLineType, nWeight )

	With ExcelApp.Selection.Borders(xlEdgeTop)
		.LineStyle = nLineType
		.ColorIndex = xlAutomatic
		.Weight = nWeight
	End With

End Function

' ******************************************************
' 範囲の右に罫線（＊追加＊）
' ******************************************************
Function ExcelLineRight( nLineType, nWeight )

	With ExcelApp.Selection.Borders(xlEdgeRight)
		.LineStyle = nLineType
		.ColorIndex = xlAutomatic
		.Weight = nWeight
	End With

End Function

' ******************************************************
' 範囲に罫線
' ******************************************************
Function ExcelBox( nLineType, nWeight )

	With ExcelApp.Selection.Borders(xlEdgeTop)
		.LineStyle = nLineType
		.ColorIndex = xlAutomatic
		.Weight = nWeight
	End With
	With ExcelApp.Selection.Borders(xlEdgeLeft)
		.LineStyle = nLineType
		.ColorIndex = xlAutomatic
		.Weight = nWeight
	End With
	With ExcelApp.Selection.Borders(xlEdgeRight)
		.LineStyle = nLineType
		.ColorIndex = xlAutomatic
		.Weight = nWeight
	End With
	With ExcelApp.Selection.Borders(xlEdgeBottom)
		.LineStyle = nLineType
		.ColorIndex = xlAutomatic
		.Weight = nWeight
	End With

End Function

' ******************************************************
' 文字列の横位置を指定（＊追加＊）
' ******************************************************
Function ExcelHAlign()

	ExcelApp.Selection.HorizontalAlignment = xlCenterAcrossSelection

End Function
Function ExcelHAlign2()

	ExcelApp.Selection.HorizontalAlignment = xlCenter

End Function

' ******************************************************
' 文字列の縦位置を指定（＊追加＊）
' ******************************************************
Function ExcelVAlign()

	ExcelApp.Selection.VerticalAlignment = xlCenter

End Function

' ******************************************************
' シート名によるシート選択
' ******************************************************
Function ExcelSelectSheet(MyBook, strSheetName)

	MyBook.Sheets(strSheetName).Select

End Function

' ******************************************************
' テーブル設計書用フォーマット
' ******************************************************
Function Format_Table(MyBook,SheetName)

	'テーブル設計書フォーマット作成
	Call ExcelSelectSheet(MyBook, SheetName)
'	Call Format_Page(MyBook)

	Call ExcelSize_Table(MyBook, SheetName)
	Call ExcelLine_Table(MyBook, SheetName)
	Call ExcelSetText_Table(MyBook, SheetName)

End Function

' ******************************************************
' セルサイズの設定
' ******************************************************
Function ExcelSize_Table(MyBook, Target)

	Dim i

	'セルの高さ合わせ
	Call ExcelSetRowHeight(MyBook, Target, 1, 13.50)
	Call ExcelSetRowHeight(MyBook, Target, 2, 24.50)
	Call ExcelSetRowHeight(MyBook, Target, 3, 13.50)
	Call ExcelSetRowHeight(MyBook, Target, 4, 24.50)
	Call ExcelSetRowHeight(MyBook, Target, 5, 20.25)

	For i = 6 To nRows + 5
		Call ExcelSetRowHeight(MyBook, Target, i, nRowHeight)
	Next

	'セルの幅合わせ
	Call ExcelSetColumnWidth(MyBook, Target, 1, 3.50)
	Call ExcelSetColumnWidth(MyBook, Target, 2, 27.00)
	Call ExcelSetColumnWidth(MyBook, Target, 3, 0.75)
	Call ExcelSetColumnWidth(MyBook, Target, 4, 15.00)
	Call ExcelSetColumnWidth(MyBook, Target, 5, 4.50)
	Call ExcelSetColumnWidth(MyBook, Target, 6, 3.00)
	Call ExcelSetColumnWidth(MyBook, Target, 7, 0.75)
	Call ExcelSetColumnWidth(MyBook, Target, 8, 13.50)
	Call ExcelSetColumnWidth(MyBook, Target, 9, 0.75)
	Call ExcelSetColumnWidth(MyBook, Target, 10, 24.00)
	Call ExcelSetColumnWidth(MyBook, Target, 11, 12.00)

End Function

' ******************************************************
' 罫線の設定
' ******************************************************
Function ExcelLine_Table(MyBook, Target)

	Dim i

	' BOX罫線
	Call ExcelRange(MyBook, Target, 1, 1, 11, nRows + 5 )
	Call ExcelBox(xlContinuous, xlMedium)

	' 上罫線
	Call ExcelRange(MyBook, Target, 1, 2, 11, 2 )
	Call ExcelLine(xlDot, xlThin)

	Call ExcelRange(MyBook, Target, 1, 3, 11, 3 )
	Call ExcelLine(xlContinuous, xlThin)

	Call ExcelRange(MyBook, Target, 1, 4, 11, 4 )
	Call ExcelLine(xlDot, xlThin)

	Call ExcelRange(MyBook, Target, 1, 5, 11, 5 )
	Call ExcelLine(xlContinuous, xlMedium)

	Call ExcelRange(MyBook, Target, 1, 6, 11, 6 )
	Call ExcelLine(xlContinuous, xlMedium)

	for i = 7 to nRows + 5
		Call ExcelRange(MyBook, Target, 1, i, 11, i )
		Call ExcelLine(xlDot, xlThin)
	Next

	' 右罫線
	Call ExcelRange(MyBook, Target, 1, 6, 1, nRows + 5 )
	Call ExcelLineRight(xlDot, xlThin)

	Call ExcelRange(MyBook, Target, 2, 1, 2, 2 )
	Call ExcelLineRight(xlContinuous, xlThin)

	Call ExcelRange(MyBook, Target, 2, 5, 2, nRows + 5 )
	Call ExcelLineRight(xlContinuous, xlThin)

	Call ExcelRange(MyBook, Target, 4, 5, 4, nRows + 5 )
	Call ExcelLineRight(xlContinuous, xlThin)

	Call ExcelRange(MyBook, Target, 5, 6, 5, nRows + 5 )
	Call ExcelLineRight(xlDot, xlThin)

	Call ExcelRange(MyBook, Target, 6, 3, 6, nRows + 5 )
	Call ExcelLineRight(xlContinuous, xlThin)

	Call ExcelRange(MyBook, Target, 8, 1, 8, 4 )
	Call ExcelLineRight(xlContinuous, xlThin)

	Call ExcelRange(MyBook, Target, 10, 1, 10, nRows + 5 )
	Call ExcelLineRight(xlContinuous, xlThin)

End Function

' ******************************************************
' セルのテキストの設定
' ******************************************************
Function ExcelSetText_Table(MyBook, Target)

	Dim i

	Call ExcelRange(MyBook, Target, 1, 1, 7, 49 )
	Call ExcelVAlign()

	' １行目
	Call ExcelSetCell(MyBook, Target, 1, 1, "　システム名")

	Call ExcelSetCell(MyBook, Target, 3, 1, "　サブシステム名")

	Call ExcelSetCell(MyBook, Target, 9, 1, "　テーブルID")
	Call ExcelRange(MyBook, Target, 9, 1, 10, 1 )
	Call ExcelHAlign()
	Call ExcelRange(MyBook, Target, 9, 2, 10, 1 )
	Call ExcelVAlign()

	Call ExcelSetCell(MyBook, Target, 11, 1, "ページ")
	Call ExcelRange(MyBook, Target, 11, 1, 11, 1 )
	Call ExcelHAlign()
	Call ExcelRange(MyBook, Target, 11, 2, 11, 2 )
	Call ExcelHAlign()
	Call ExcelRange(MyBook, Target, 11, 2, 11, 2 )
	Call ExcelVAlign()

	' ２行目
	Call ExcelSetCell(MyBook, Target, 11, 2, "／")
	Call ExcelRange(MyBook, Target, 11, 2, 11, 2 )

	' ３行目
	Call ExcelSetCell(MyBook, Target, 1, 3, "　テーブル名")

	Call ExcelSetCell(MyBook, Target, 7, 3, "種別")
	Call ExcelRange(MyBook, Target, 7, 3, 8, 3 )
	Call ExcelHAlign()

	Call ExcelSetCell(MyBook, Target, 9, 3, "作成日")
	Call ExcelRange(MyBook, Target, 9, 3, 10, 3 )
	Call ExcelHAlign()

	Call ExcelSetCell(MyBook, Target, 11, 3, "作成者")
	Call ExcelRange(MyBook, Target, 11, 3, 11, 3 )
	Call ExcelHAlign()

	' ４行目
	Call ExcelRange(MyBook, Target, 8, 4, 8, 4 )
	Call ExcelHAlign2()
	Call ExcelVAlign()

	Call ExcelRange(MyBook, Target, 10, 4, 10, 4 )
	Call ExcelHAlign2()
	Call ExcelVAlign()

	Call ExcelRange(MyBook, Target, 11, 4, 11, 4 )
	Call ExcelHAlign2()
	Call ExcelVAlign()

	' ５行目
	Call ExcelSetCell(MyBook, Target, 1, 5, "　列名")

	Call ExcelSetCell(MyBook, Target, 3, 5, "データ型")
	Call ExcelRange(MyBook, Target, 3, 5, 4, 5 )
	Call ExcelHAlign()

	Call ExcelSetCell(MyBook, Target, 5, 5, "サイズ")
	Call ExcelRange(MyBook, Target, 5, 5, 6, 5 )
	Call ExcelHAlign()

	Call ExcelSetCell(MyBook, Target, 7, 5, "　説明")

	Call ExcelSetCell(MyBook, Target, 11, 5, "主キー")
	Call ExcelRange(MyBook, Target, 11, 5, 11, 5 )
	Call ExcelHAlign()
	Call ExcelVAlign()

	for i = 6 to nRows + 5
		Call ExcelSetCell(MyBook, Target, 1, i, i - 5)
		Call ExcelRange(MyBook, Target, 1, i, 1, i )
		Call ExcelHAlign()
		Call ExcelRange(MyBook, Target, 11, i, 11, i )
		Call ExcelHAlign()
	next

	Call ExcelRange(MyBook, Target, 1, 1, 1, 1 )

End Function

' ******************************************************
' ヘッダー,余白の指定
' ******************************************************
Function Format_Page(MyBook)

	on error resume next

	With MyBook.ActiveSheet.PageSetup
		.CenterHeader = "&18&A"
		.PaperSize = xlPaperB4
'		.LeftMargin = ExcelApp.InchesToPoints(0.393700787401575)
'		.RightMargin = ExcelApp.InchesToPoints(0.196850393700787)
'		.TopMargin = ExcelApp.InchesToPoints(0.551181102362205)
'		.BottomMargin = ExcelApp.InchesToPoints(0.393700787401575)
'		.HeaderMargin = ExcelApp.InchesToPoints(0.196850393700787)
'		.FooterMargin = ExcelApp.InchesToPoints(0.196850393700787)
	End With

	on error goto 0

End Function

' ******************************************************
' シングルクォートで囲む
' ******************************************************
Function Ss( strValue )

	Ss = "'" & strValue & "'"

End Function

' ******************************************************
' DB接続(SQLServer)
' ******************************************************
Function SQS_DBConnect( _
	Connection, _
	Server, _
	DB, _
	User, _
	Pass _
)

	Dim ConnectionString

	ConnectionString = _
		"Provider=SQLOLEDB;" & _
		"Data Source=" & Server & ";" & _
		"Initial Catalog=" & DB & ";" & _
		"User ID=" & User & ";" & _
		"Password=" & Pass & ";"
	
	if IsEmpty( Connection ) then
		Set Connection = CreateObject( "ADODB.Connection" )
	end if

	on error resume next
	Connection.Open ConnectionString
	if Err.Number <> 0 then
		SQS_DBConnect = "ERROR:" & Err.Description
	else
		SQS_DBConnect = ""
	end if
	on error goto 0

End Function

' ******************************************************
' DB終了処理(接続を閉じる)
' ******************************************************
Function DBClose( _
	CnRs _
)
	
	On Error Resume Next
	If CnRs.State >= 1 Then
		CnRs.Close
	End If
	On Error Goto 0

	DBClose = True

End Function

' ******************************************************
' DB読込み
' 【戻り値】: True(データ有り),False(データ無し)
' ******************************************************
Function DBGet( _
	Connection, _
	Record, _
	SqlQuery, _
	bUpadateFlg _
)
	if IsEmpty( Record ) then
		Set Record = CreateObject( "ADODB.Recordset" )
	end if

	Dim ConstCheck

	' 閉じていない時は閉じる
	If Record.State >= 1 Then
		Record.Close
	End If

	' 更新処理に使用する場合は、レコード単位の共有的ロック
	If bUpadateFlg Then
		ConstCheck = adLockOptimistic
		if IsEmpty( ConstCheck ) then
			Record.LockType = 3
		else
			Record.LockType = adLockOptimistic
		end if
	else
		ConstCheck = adLockOptimistic
		if IsEmpty( ConstCheck ) then
			Record.LockType = 1
		else 
			Record.LockType = adLockReadOnly
		end if
	End If
	
	' レコードセット作成
	Record.Open SqlQuery, Connection
	If Record.State >= 1 Then
		If Record.EOF Then
			DBGet = False
		Else
			DBGet = True
		End If
	else
		DBGet = False
	end if

End Function

</SCRIPT>
</JOB>
posted by at 2019-02-11 10:11 | DB関連 | このブログの読者になる | 更新情報をチェックする

2016年11月16日

select 社員コード,氏名,(select count(*) from 得意先マスタ where 担当者 = 社員コード) as 件数 from 社員マスタ : 件数を where で使いたい場合 

改めて書くと以下の SQL ですが、欲しい結果は『得意先担当を持たない社員の一覧』です。ですが、このままでは得意先担当を持つ社員も表示されるので、件数が 0 の行を取り出したいわけです。

select
	社員コード,
	氏名,
	(select count(*) from 得意先マスタ where 担当者 = 社員コード) as 件数
from 社員マスタ


しかし、この SQL の最後に where 件数 = 0 とは書けないので、この内容を view にしてしまえばいいわけです。ただ、実際問題 view を定義しなくとも、from に このクエリを書けば解決します。
( Oracle で言うところのインラインビュー / 今でもそう言うのだろうか? )


select * from 

(select 社員コード,氏名,(select count(*) from 得意先マスタ where 担当者 = 社員コード) as 件数

from 社員マスタ) as 別名

where 件数 = 0

ここで注意なのは、SQLServer では別名が必要なところ、Oracle では別名は省略できるはずです。

ちょっと確認したのが古いのですが、こちらが他の RDBMS を含めた情報です。
posted by at 2016-11-16 22:17 | DB関連 | このブログの読者になる | 更新情報をチェックする

2014年12月16日

SQL Server とそのコンポーネントのバージョンとエディションを確認

とても詳細に記述されている、Microsoft のドキュメントがありました。

SQL Server とそのコンポーネントのバージョンとエディションを確認する方法

ですが、中をみた結果以下のクエリで確認するのが最も有効のようです。

Select @@version


あるいは、コマンドプロンプトから以下を実行します( SQLEXPRESS は環境によって違います )

sqlcmd -E -S .\SQLEXPRESS -Q "Select @@version"

※ -Q は大文字です。小文字にするとコマンドを実行した後、quit しません
※ sqlcmd ユーティリティ

関連する記事

SQLExpress 2005( および 2008 ) の接続設定

※ 追記
以下のプロシージャで全体環境の詳細が表示されます

master.dbo.xp_msver
IndexNameInternal_ValueCharacter_Value
1ProductNameMicrosoft SQL Server
2ProductVersion65541010.50.2500.0
3Language1041日本語 (日本)
4PlatformNT INTEL X86
5CommentsSQL
6CompanyNameMicrosoft Corporation
7FileDescriptionSQL Server Windows NT
8FileVersion2009.0100.2500.00 ((KJ_PCU_Main).110617-0026 )
9InternalNameSQLSERVR
10LegalCopyrightMicrosoft Corp. All rights reserved.
11LegalTrademarksMicrosoft SQL Server is a registered trademark of Microsoft Corporation.
12OriginalFilenameSQLSERVR.EXE
13PrivateBuild
14SpecialBuild163840000
15WindowsVersion4981393986.1 (7601)
16ProcessorCount22
17ProcessorActiveMask300000003
18ProcessorType586PROCESSOR_INTEL_PENTIUM
19PhysicalMemory35753575 (3748982784)
posted by at 2014-12-16 13:44 | DB関連 | このブログの読者になる | 更新情報をチェックする

2014年07月02日

land.to での PHP + PostgreSQLでの接続テスト

land.to では、もうかなり前から新規申込は停止していますが、既にアカウントを持っている場合は MySQL と PostgreSQL が使用できます。MySQL はめずらしくありませんが、PostgreSQL はけっこう珍しいのでは無いでしょうか。

昔は、直接 SQL を実行できる WEB アプリを公開していたのですが、途中でいろいろあって削除し、それ以来も長い間放置していました。

で、久しぶりに PostgreSQL を確認してみるきちんと動作していたので記録です。

PostgreSQL用 クラス

<?
# ********************************
# データベースクラス
# ********************************
class DB {
 
	var $Connect;
	var $Result;
 
# ********************************
# コンストラクタ
# ********************************
	function DB(
		$Server='localhost',
		$DbName='r205',
		$User='r205',
		$Password='パスワード' ) {
		$this->Connect = pg_connect(
			"host=$Server" .
			" port=5432" .
			" dbname=$DbName" .
			" user=$User" .
			" password=$Password"
		);
	}
 
# ********************************
# 接続解除
# ********************************
	function Close( ) {
		pg_close( $this->Connect );
	}
 
# ********************************
# クエリー
# ********************************
	function Query( $SqlQuery ) {
		$ret = pg_query( $this->Connect, $SqlQuery );
		return $ret;
	}
 
# ********************************
# フェッチ
# ********************************
	function Fetch( $Result ) {
		return pg_fetch_array( $Result );
	}
 
# ********************************
# クエリーとフェッチ
# ********************************
	function QueryEx( $SqlQuery='' ) {
 
		if ( $SqlQuery != '' ) {
			$this->Result = $this->Query( $SqlQuery );
			if ( !$this->Result ) {
				return FALSE;
			}
			return $this->Fetch ( $this->Result );
		}
		else {
			return $this->Fetch ( $this->Result );
		}
 
	}
 
# ********************************
# 実行
# ********************************
	function Execute( $SqlExec ) {
		$ret = pg_query( $this->Connect, $SqlExec );
		return $ret;
	}
 
# ********************************
# バージョン文字列取得
# ********************************
	function Version( ) {
		$Field = $this->QueryEx( "SHOW SERVER_VERSION" );
		return $Field["server_version"];
	}
 
}
?>


データベースの接続や処理そのものは特に問題も無く動作しましたが、header 関数で出力したキャラクタセットがブラウザに届かないので、HTML 側の META 要素で指定しなければ化けてしまいます。また、ini_set( 'display_errors', "1" ) で、エラーを表示するようにしないとデバッグができません。以上２点を注意すれば、MySQL であろうが、PostgreSQL であろうが利用可能です

接続テスト

<?php
header( "Content-Type: text/html; Charset=euc-jp" );
header( "pragma: no-cache" );
header( "Expires: Wed, 31 May 2000 14:59:58 GMT" );
header( "Cache-control: no-cache" );

ini_set( 'display_errors', "1" );

require_once( "dbpg.php" );
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=euc-jp">
<title></title>
</head>
<body>
<pre>
<?
$SQL = new DB();
if ( $SQL->Connect === false ) {
	print "接続できませんでした<br>\n";
}
else {
	print "接続されました<br>\n";

	$Column = $SQL->QueryEx( "select * from 社員マスタ" );

	while ( $Column ) {

		$Column = $SQL->QueryEx( );
		print_r( $Column );
	}

	$SQL->Close();
}
?>
</pre>
</body>
</html>
タグ：DB データベース
posted by at 2014-07-02 21:21 | DB関連 | このブログの読者になる | 更新情報をチェックする

2010年06月25日

SQL*PlusだけでCSVを作成する


SQL*Plusだけで CSV を作成する

SQL*Plus のコマンドを中心に細かく説明しています。

Oracle では、CSV の入力ツールとしては SQL*Loader があるのですが、出力となると
専用ツールは無いのでアプリケーションを作成するか、エクスポートとインポートを利用する事
を考えます。( 参考 : エクスポートユーティリティのテーブル単位の出力( コマンドライン ) )

しかし、他のアプリケーションで使用したい場合は CSV のデータが欲しいのでエディタ一つ
で SQL*Plus  を使う方法は誰にでもできます。


関連する 記事

Oracle : SQL*Loader のサンプル
Oracle エクスポートとエクスポートされたファイル内の参照
Oracle インポート : バックアップしてあったデータよりテーブルを復帰
sqlplus セット


関連する Oracle ドキュメント

SQL*Plusコマンド・リファレンス
posted by at 2010-06-25 00:00 | DB関連 | このブログの読者になる | 更新情報をチェックする

2010年05月28日

PHP と MySQL 限定で SQL インジェクションを考えた場合

SQLインジェクションの要点は、'と' の間に現れる文字列をどうするか、と言う事に集約されます。その文字列表現になにも問題無ければ、いかなる文字列も '(シングルクオート)にはさまれた文字列でしか無いはずだからです。

そもそも PHP を扱う場合、もともとWEBアプリケーションで使う事を想定されているので、入力文字がプログラマの意図せぬ変換されている場合があります。ですから、SQLインジェクションを考える場合はこれを排除して考える必要があります。

つまり、最初は「入力されたままの文字列がアプリケーションに渡される」という前提で考えます。その場合最初に気をつけるのが '(シングルクオート) です。

入力値の'(シングルクオート) は、SQLの常識としてそのままでは正しく動かないので、'' に変換します。通常はこれで十分なわけですが、MySQL という RDBMS を扱う上でさらに注意が必要になります。

$query = "select * from 社員マスタ";
$query .= " where 社員コード = '{$_POST['fld1']}'";

//入力値が ' or 1=1# だと、
// select * from 社員マスタ where 社員コード = '' or 1=1#' となり、
// 全件表示されてしまうので
// '' or 1=1# と変換すると入力値はすべて文字列となります

これはこれで OK なのですが、\' or 1=1# と入力されてしまうと、\'' or 1=1# と変換されて、\' が '' 扱いになって、結局 以下のようになって全て選択されます( '' の間の \' は、文字列扱いの ' になるので、文字列の終端が変化してしまう )

select * from 社員マスタ where 社員コード = '\'' or 1=1#'

で、それらをふまえた上で、PHP には、MySQL のドキュメントにも出現する API である、mysql_real_escape_string という関数を使うと、一括で危険な文字を変換できます。


MySQL ドキュメント

MySQL :: MySQL 5.1 リファレンスマニュアル :: 23.2.3.53 mysql_real_escape_string()
posted by at 2010-05-28 16:12 | DB関連 | このブログの読者になる | 更新情報をチェックする

2010年05月13日

Oracle11g + Windows7 : InstantClient を使用した軽量接続 ( Win32 )


Oracle 11g : Instant Client を使用した接続( Win32 ) 

Oracle_odbc_ic

10g の時は Visual Studio 2003 で作成されていたらしく、Microsoft の dll
を手にいれるのに苦労したというか、その dll が無い事が原因でエラーが出
る事を調べるの手間取りましたが、今回はあっさりです。

但し、まだ日本語の説明が無いようなので、環境変数にキャラクタセットの
設定をしないと化けます。これは、10g のころの説明にあっもので、化けた
のですぐ設定しましたが、前回の経験が無いとまた、手間を取るところでした。
posted by at 2010-05-13 21:39 | DB関連 | このブログの読者になる | 更新情報をチェックする

2009年11月17日

MySQL:GPLな再配布。プログラムは無しでデータのみ追加


MySQL5.14 販売管理データ登録済みセットのダウンロード


MySQL 5.14 のオリジナルインストールで配置された環境は、開発する者にとっては
とょっと不向きなので、一箇所にまとめて販売管理データを登録してパッケージにしました。
プログラムは無いのですが、GPL なんで、いちおうオリジナルのソースコードもダウンロード
できるようにしています。

てっとり早く言うと、C:\ にダウンロードして解凍して install.bat を実行するだけで使えるようになります。
( ODBC ドライバ同梱、テーブル13、ビュー 4、要サービス起動 )


で、重要なのはこの再配布では無く、


自分で作ったデータをそのまま書庫に固めて持ち運びできるというサンプルです。
もちろん、公開しなければサーバに置いておいてダウンロードして使うというのが本来の
正当な使い方ですし、その場合は何も気にしなくていいですし。
posted by at 2009-11-17 14:41 | DB関連 | このブログの読者になる | 更新情報をチェックする

2009年11月10日

MySQL5.1.4の半手動インストール(インストール場所の変更)


MySQL5.1.4 のインストール後、分散されたインストール場所を手作業で統合

MySQL は、かなり前に 5.1 のベータだったと思いますが、日本語がきちんと対応
されたのでずっと一つに固めて必要な時に自分のサイトからダウンロードしてサービス
に登録して ODBC ドライバをインストールするだけで使えるようなパッケージにしてました。

で、もうあれからかなり経っているのでチェックしてみると、一見そんなに変化は
見られなかったのですが、構成がかなり複雑になっていたので、それじゃあ運用しづらい
ので一箇所にまとめるテストをしてみました。

それ自体はあっさり成功しているので、別マシンでそのままコピーできるかどうかの
テストはいずれやってみようと思います。

以前は SHIFT_JIS で構成していたのですが、いまどきはもう UTF-8 で大丈夫だろうと、
インストールしてみると、ADO のスキーマ呼び出し以外は何もしなくても日本語は化けません。
ですが、スキーマ経由はだいたいにおいて ADO ではエラーになるようなので、MySQLの
必殺 set names 'sjis' を実行しようと、ODBC ドライバの初期設定へ行くと、なんと、
専用の キャラクタセット設定用コンボボックスが付加されてました。

やはり時が経つといろいろいいこともあります。
JET 経由の MDB からのインポートも全く問題無く動いています。
posted by at 2009-11-10 18:08 | DB関連 | このブログの読者になる | 更新情報をチェックする
- 1 2 3 4 5.. >>


Powered by Seesaa
Seesaa の各ページの表示について
Seesaa の 記事がたまに全く表示されない場合があります。その場合は、設定＞ 詳細設定＞ ブログ設定 で 最新の情報に更新の『実行ボタン』で記事やアーカイブが最新にビルドされます。

Seesaa のページで、アーカイブとタグページは要注意です。タグページはコンテンツが全く無い状態になりますし、アーカイブページも歯抜けページはコンテンツが存在しないのにページが表示されてしまいます。

また、カテゴリページもそういう意味では完全ではありません。『カテゴリID-番号』というフォーマットで表示されるページですが、実際存在するより大きな番号でも表示されてしまいます。

※ インデックスページのみ、実際の記事数を超えたページを指定しても最後のページが表示されるようです

対処としては、このようなヘルプ的な情報を固定でページの最後に表示するようにするといいでしょう。具体的には、メインの記事コンテンツの下に『自由形式』を追加し、アーカイブとカテゴリページでのみ表示するように設定し、コンテンツを用意するといいと思います。


※ エキスパートモードで表示しています

アーカイブとカテゴリページはこのように簡単に設定できますが、タグページは HTML 設定を直接変更して、以下の『タグページでのみ表示される内容』の記述方法で設定する必要があります


<% if:page_name eq 'archive' -%>
アーカイブページでのみ表示される内容
<% /if %>

<% if:page_name eq 'category' -%>
カテゴリページでのみ表示される内容
<% /if %>

<% if:page_name eq 'tag' -%>
タグページでのみ表示される内容
<% /if %>


この記述は、以下の場所で使用します