「SQLSERVER、ストアドプロシージャ、クエリ」カテゴリーアーカイブ

購入明細テーブルで顧客の古い履歴を求める

P202_SQL実践入門_購入明細で顧客の古い履歴を求める
続きを読む →

P277_SQL実践入門_前日の株価と比較して上下を示す

P277_SQL実践入門_前日の株価と比較して上下を示す
株価を前日と比較して上下同じを示す

考え方はSELECT句にSELECT句を入れて全体の出力に合わせて比較するというC言語でループを考えている人にとっては理解に苦しむSQLクエリである。
ぐるぐる回すループを使わなくてもSELECTでループみたいなことができることを示した例。

基本のテーブル

/*


日付で比較して、
*/
CREATE TABLE Stocks(
brand VARCHAR(8) NOT NULL,
sale_date DATE NOT NULL,
price INT NOT NULL,
--CONSTRAINT pk_Stocks Primary key(sale_date)
)

INSERT INTO Stocks VALUES('A鉄鋼','2008-07-01',1000),
('A鉄鋼','2008-07-04',1200),
('A鉄鋼','2008-08-12',800),
('B商社','2008-06-04',3000),
('B商社','2008-12-31',3000),
('C電気','2008-07-01',9000),
('D産業','2008-06-04',5000),
('D産業','2008-06-05',5000),
('D産業','2008-06-06',4800),
('D産業','2008-12-01',5100)

SELECT brand,sale_date,price,
	CASE SIGN(price - 
		(
		SELECT price
			FROM Stocks S1
			WHERE brand = Stocks.brand
				AND sale_date = 
					(SELECT MAX(sale_date)
						FROM Stocks S2
						WHERE	brand = Stocks.brand
							AND sale_date< Stocks.sale_date
					)
		)
	)
	WHEN -1 THEN '↓'
	WHEN 0 THEN '→'
	WHEN 1 THEN '↑'
	END
FROM Stocks

--drop Table Stocks

日付で比較して、

CREATE TABLE Stocks(

brand VARCHAR(8) NOT NULL,

sale_date DATE NOT NULL,

price INT NOT NULL,

--CONSTRAINT pk_Stocks Primary key(sale_date)

)

INSERT INTO Stocks VALUES('A鉄鋼','2008-07-01',1000),

('A鉄鋼','2008-07-04',1200),

('A鉄鋼','2008-08-12',800),

('B商社','2008-06-04',3000),

('B商社','2008-12-31',3000),

('C電気','2008-07-01',9000),

('D産業','2008-06-04',5000),

('D産業','2008-06-05',5000),

('D産業','2008-06-06',4800),

('D産業','2008-12-01',5100)

SELECT brand,sale_date,price,

CASE SIGN(price -

(

SELECT price

FROM Stocks S1

WHERE brand = Stocks.brand

AND sale_date =

(SELECT MAX(sale_date)

FROM Stocks S2

WHERE brand = Stocks.brand

AND sale_date< Stocks.sale_date

)

WHEN -1 THEN '↓'

WHEN 0 THEN '→'

WHEN 1 THEN '↑'

END

FROM Stocks

--drop Table Stocks

内部結語INNERJOIN

--分解して、同じテーブル同士で比較する
SELECT *
	FROM Stocks S2 inner JOIn Stocks S
	ON	S2.brand = S.brand
		AND S2.sale_date< S.sale_date

--分解して、同じテーブル同士で比較する

SELECT *

FROM Stocks S2 inner JOIn Stocks S

ON S2.brand = S.brand

AND S2.sale_date< S.sale_date

MAX

--Sの最大日がでるが1件のみ
SELECT MAX(S.sale_date)
	FROM Stocks S2 inner JOIn Stocks S
	ON	S2.brand = S.brand
		AND S2.sale_date< S.sale_date

	--Sの最大日がでるので、会社でまとめると会社分が出力される。ただし、同日はでない
	--同日が存在する場合はS2.sale_date<= S.sale_dateにする

--Sの最大日がでるが1件のみ

SELECT MAX(S.sale_date)

FROM Stocks S2 inner JOIn Stocks S

ON S2.brand = S.brand

AND S2.sale_date< S.sale_date

--Sの最大日がでるので、会社でまとめると会社分が出力される。ただし、同日はでない

--同日が存在する場合はS2.sale_date<= S.sale_dateにする

それぞれ
ただし同日は指定していないので、同日は＜＝としる

SELECT MAX(S.sale_date)
	FROM Stocks S2 inner JOIn Stocks S
	ON	S2.brand = S.brand
		AND S2.sale_date < S.sale_date
	Group by S.brand--同日が存在する場合はS2.sale_date <= S.sale_dateにする

SELECT MAX(S.sale_date)

FROM Stocks S2 inner JOIn Stocks S

ON S2.brand = S.brand

AND S2.sale_date < S.sale_date

Group by S.brand--同日が存在する場合はS2.sale_date <= S.sale_dateにする

--S1とS2 でそれぞれ最大日がでる
SELECT MAX(S1.sale_date) ,MAX(S2.sale_date) FROM Stocks as S1
 CROSS JOIN Stocks as S2
WHERE
	S1.brand = S2.brand
	AND S1.sale_date < S2.sale_date--INNER JOINと同じ

--DROP TABLE Stocks

--S1とS2 でそれぞれ最大日がでる

SELECT MAX(S1.sale_date) ,MAX(S2.sale_date) FROM Stocks as S1

CROSS JOIN Stocks as S2

WHERE

S1.brand = S2.brand

AND S1.sale_date < S2.sale_date--INNER JOINと同じ

--DROP TABLE Stocks

--Stocks
--3つのStocksを使っている。
--外側のSTocksと2つ目のStocksで日付を比べて
--外側のStocksと1つ目のStocksで同じ日付で出力する
SELECT brand,sale_date,
	(SELECT S1.sale_date FROM Stocks S1
	WHERE S1.brand = Stocks.brand
		AND 
		sale_date = --Stocks.sale_date
		(SELECT MAX(S2.sale_date) FROM Stocks S2
		WHERE S2.brand = Stocks.brand 
		AND S2.sale_date< Stocks.sale_date
		)
	) as s1_date
FROM Stocks

--Stocks

--3つのStocksを使っている。

--外側のSTocksと2つ目のStocksで日付を比べて

--外側のStocksと1つ目のStocksで同じ日付で出力する

SELECT brand,sale_date,

(SELECT S1.sale_date FROM Stocks S1

WHERE S1.brand = Stocks.brand

AND

sale_date = --Stocks.sale_date

(SELECT MAX(S2.sale_date) FROM Stocks S2

WHERE S2.brand = Stocks.brand

AND S2.sale_date< Stocks.sale_date

)

) as s1_date

FROM Stocks

SQLSERVER SUMの使い方GROUP

SQLSERVER SUMの使い方GROUP
続きを読む →

SQLSERVER　行を列にする方法

クエリを使って行を列にしたい時がある。
例えば、ある分類の中の複数項目、生徒の教科別、商品分類など。
ただし、以下の方法は列にしたい項目が数十個になると面倒になる。
CASE WHENを使う方法とサブクエリを使う方法がある
続きを読む →

SQLSERVER テーブルに存在しない列を作ってデータを表示する

カラムの別名はよく使うけど、存在しない列をSELECT文で追加する

--テーブルに存在しない列を作ってデータを表示する
SELECT 'test' as KAMEI--KAMEI列にtestというデータが表示される
--この方法を使えば、テーブルの出力時にある条件下のデータとして区別できる

----student_idをidとして表示して、もとの列も含めて全部だす
SELECT student_id as id,* FROM ScoreCols--student_idをidとして表示して、もとの列も含めて全部だす

--テーブルに存在しない列を作ってデータを表示する

SELECT 'test' as KAMEI--KAMEI列にtestというデータが表示される

--この方法を使えば、テーブルの出力時にある条件下のデータとして区別できる

----student_idをidとして表示して、もとの列も含めて全部だす

SELECT student_id as id,* FROM ScoreCols--student_idをidとして表示して、もとの列も含めて全部だす

SQL実践P227ウィンドウ関数ROW_NUM

SQL実践P227ウィンドウ関数ROWNUM
続きを読む →

SQLSERVER　テーブルから新規テーブルを作成する

テーブルのデータに間違いがあって、ストアドやクエリから出力されたデータから間違いを探したいときに、出力結果をエクセルに貼り付けるが、大量になると探すのが面倒になる。
出力された結果をテーブルを作成してその中に入れておけばあとからでも見ることができるし調べるのも楽になる。

–SQLSERVER　テーブルから新規テーブルを作成する　SELECT INTOは初回実行のみ、2回めはエラーになるので2回目はINSERT INTO
–tmpa:新規テーブル CREATEで事前に作らなくてもよい
–Proc:既存テーブル
–Procテーブルと同じデータを持つtmpaを作りたい。
–SELECT INTOで作成されるテーブルはIndexや外部キーとかは作成されないらしい。あくまでも列のデータがコピーされる
SELECT * into tmpa FROM Procs–tmpaは新規テーブルでCREATEしていない

–FROM 以降のテーブルをSELECTで出力して新規テーブルに挿入する場合は別名にする
–別名にしないと警告が出る

SELECT * into tmp01 FROM (
	SELECT * FROM Member WHERE Age > 20
) as ab--FROM句のSELECT 出力の場合は別名にしておく

SELECT * into tmp01 FROM (

SELECT * FROM Member WHERE Age > 20

) as ab--FROM句のSELECT 出力の場合は別名にしておく

続きを読む →

IISのログ記録アイコンがない｜ClassicASPでDB接続ができない

IISのログが出力されていなかったWIN10　ログ記録アイコンがない

・結論
[note]コントロールパネルのWindows の機能の有効化または無効化にて
状態と診断のチェックを入れてインストールする[/note]

http://127.0.0.1/hiyoko/stoado01.aspで確認中
サーバ管理マネージャの設定が必要らしい
http://www.putise.com/server/windows-serevr-2008-r2/ii7-w3c-accesslog
でもWin10では
（１）サーバ管理マネージャをダウンロードした
続きを読む →

selectして別のテーブルにINSERTするCREATEなしで、EXCEPT差分

まとめ
（１）新規テーブルに別のテーブルのデータをコピーしたい
select * into newtable from table where (条件)
この場合、同じクエリを実行するとすでにnewtableがあるよエラーになるので初回だけ使える
つまり、すでにnewtableが存在するときは使えない。
CREATE TABLEをしなくてもnewtableが作成されるので手間を省ける

（２）すでに存在するテーブルに別のテーブルからコピーしたい
insert into anotherTable select 列 from table where (条件)
（例）INSERT INTO newMember SELECT * FROM Member

＜(1)の場合の例＞
Memberテーブルがあったとする（name,age,cityの列）
このテーブルと同じテーブルを作りたい。
使い方は、クエリを書いていて、他のクエリに改良したときに、出力内容が同じかを確かめたいときに、それぞれのクエリのSELECT出力を新しいテーブルに入れる。
そして、２つ（もしくは３つとか）のテーブルを下記のEXCEPTで比較して0件なら同じ出力と言える。
数百行程度なら出力内容をコピーしてエクセルに貼り付ければよいが、これが10万件とかだとエクセルに貼り付けて確認するのも面倒になる。
そこで、SQLManagementStudioで確認できればそれが早い

テーブルを作ってデータを入れて確認したいときに、いちいちCREATE TABLEして列と型を書いて、、、とするとイライラするので無駄な手順が省けると非常に助かる。
でも一部の列だけの情報がほしいときは結局はCREATE TABLEでテーブル定義必要だったりする。

member一覧
member：もともとのテーブル
newmember:memberから新しいテーブルを作る

SELECT TOP 10 * FROM MEMBER
SELECT * INTO newMember FROM Member--これでCREATE TABLEを実行しなくても	newmemberテーブルが作成される。なおすでにテーブルが存在すると既にテーブルがあるよエラーになる。
--ただし、インデックスとかもろもろの設定はつかないのであくまでもデータを一時的に使いたいときに使うらしい
select * FROM newMember

SELECT TOP 10 * FROM MEMBER

SELECT * INTO newMember FROM Member--これでCREATE TABLEを実行しなくても newmemberテーブルが作成される。なおすでにテーブルが存在すると既にテーブルがあるよエラーになる。

--ただし、インデックスとかもろもろの設定はつかないのであくまでもデータを一時的に使いたいときに使うらしい

select * FROM newMember

[select * into newtable from table]はすでにテーブルが存在すると既にテーブルがあるよエラーになる。
–すでにテーブルが存在するときは

INSERT INTO newMember SELECT * FROM Member

1	INSERT INTO newMember SELECT * FROM Member

INSERT INTOを使う

すでにテーブルが存在するのにSELECT INTOを使うとエラーになる。
すでにあるよエラー
EXCEPTで差分を調べる。同じ列である必要がある

--EXCEPTで２つのテーブルの差を検索する。これで0件なら一致
SELECT * FROM Member
EXCEPT
SELECT * FROM newMember

--EXCEPTで２つのテーブルの差を検索する。これで0件なら一致

SELECT * FROM Member

EXCEPT

SELECT * FROM newMember

テーブル同士で差分を調べる

--LEFT OUTERで差分を調べる。NULLがあれば差がある
SELECT * FROM Member LEFT OUTER JOIN newMember ON Member.name = newMember.Name

SELECT * FROM Member LEFT OUTER JOIN newMember 
ON Member.name = newMember.Name
WHERE
	Member.name IS NULL or Member.age IS NULL or Member.City IS NULL
	--0件ならさいがないと思う

--LEFT OUTERで差分を調べる。NULLがあれば差がある

SELECT * FROM Member LEFT OUTER JOIN newMember ON Member.name = newMember.Name

SELECT * FROM Member LEFT OUTER JOIN newMember

ON Member.name = newMember.Name

WHERE

Member.name IS NULL or Member.age IS NULL or Member.City IS NULL

--0件ならさいがないと思う

–NewMemberの一部を変更してみた

UPDATE newMember
SET City = 'a'
WHERE
	NAME = '坂本' AND Age = 19 AND City = '東京'

select * from newMember
/*
Name	Age	City
坂本	19	a  >>ここを変えた
中原	50	大阪
山岡	6	東京
赤橋	42	北海道
西	69	大阪
*/

SELECT * FROM Member
EXCEPT
SELECT * FROM newMember
/*
結果はmemberテーブルの坂本さんが違うと出力される
Name	Age	City
坂本	19	東京
*/

UPDATE newMember

SET City = 'a'

WHERE

NAME = '坂本' AND Age = 19 AND City = '東京'

select * from newMember

Name Age City

坂本 19 a >>ここを変えた

中原 50 大阪

山岡 6 東京

赤橋 42 北海道

西 69 大阪

SELECT * FROM Member

EXCEPT

SELECT * FROM newMember

結果はmemberテーブルの坂本さんが違うと出力される

Name Age City

坂本 19 東京

またテーブルのみならず
SELECTで出力した内容を挿入したいときは
–FROM のテーブルをSELECTで出力して新規テーブルに挿入する場合は別名にする
–別名にしないと警告が出る

SELECT * into tmp01 FROM (
	SELECT * FROM Member WHERE Age > 20
) as ab

SELECT * into tmp01 FROM (

SELECT * FROM Member WHERE Age > 20

) as ab

それか、一旦別のテーブルに入れてからさらに、SELECT INTOしてもよいかも

ピクチャ

ピクチャは海外ドラマ、漫画、英単語語呂合わせ法、アイフォンアプリ等の記事を紹介します

「SQLSERVER、ストアドプロシージャ、クエリ」カテゴリーアーカイブ

P131 SQLパズルすべての製品を購入した人

P202_SQL実践入門_購入明細で顧客の古い履歴を求める

P277_SQL実践入門_前日の株価と比較して上下を示す

SQLSERVER SUMの使い方GROUP

SQLSERVER　行を列にする方法

SQLSERVER テーブルに存在しない列を作ってデータを表示する

SQL実践P227ウィンドウ関数ROW_NUM

SQLSERVER　テーブルから新規テーブルを作成する

IISのログ記録アイコンがない｜ClassicASPでDB接続ができない

IISのログが出力されていなかったWIN10　ログ記録アイコンがない

selectして別のテーブルにINSERTするCREATEなしで、EXCEPT差分

IISのログが出力されていなかったWIN10 ログ記録アイコンがない

IISのログが出力されていなかったWIN10　ログ記録アイコンがない