| MSDE FunClub |
|
最終更新日 : 2000/07/08 |
|
Microsoft Data Engine FunClub
|
Since 2000.07.08
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第２章１２０ｐ 〜 １２３ｐ掲載】 |
-- [SQL文の基礎（例題２１番）EX-21.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- これから操作する既定のデータベースを MySampleTest にします
USE MySampleTest
GO
-- ( 件処理されました)の表示を抑制する
SET NOCOUNT ON
GO
-- [受注]テーブルの存在チェック
IF( ( object_id('受注') IS NULL ) OR
OBJECTPROPERTY( object_id('受注') , 'IsTable' ) = 0 )
BEGIN
PRINT '****Error!!****'
PRINT 'AccessのNorthwindデータベースの[受注]テーブルを使います'
PRINT '[受注]テーブルを転送してください'
RAISERROR( '[受注]テーブルが見つかりません',16,127)
RETURN
END
GO
-- *****************
-- 考え方−１番目
-- *****************
-- SQL文を個別に発行して考える方法です
-- 作業用変数は、バッチ内で有効です
DECLARE @Max money -- 最大の受注金額
DECLARE @Cd int -- 最大金額を受注した受注コード
DECLARE @Ps int -- 社員番号(社員番号は、テーブル設計上 int になっている)
PRINT '【考え方−１番目】'
-- 手順１：最大の受注金額を取得します
SELECT @Max = (SELECT MAX(単価*数量*(100-割引)/100) FROM 受注明細)
PRINT '最大受注金額 = ' + STR(@Max)
-- 手順２：最大の受注金額に対応する受注コードを求める
-- この受注コードは複数個存在するため、作業用変数１個に代入することはできない
-- そのためカーソル処理を使うことになる
DECLARE CODE_Cur CURSOR FOR
SELECT 受注コード FROM 受注明細
WHERE ( 単価*数量*(100-割引)/100 ) = @Max
-- カーソルを開く
OPEN CODE_Cur
-- カーソル先頭行を取得し、その受注コードの値を@Cdに代入する
FETCH NEXT FROM CODE_Cur INTO @Cd
-- @@FETCH_STATUS = 0 の間、カーソルにはレコードがあります
WHILE @@FETCH_STATUS = 0
BEGIN
-- 最大の販売金額に対応する受注コードの表示
PRINT 'その時の受注コード= ' + STR(@Cd)
-- 手順３：受注コードに対応する社員コードを検索する
SELECT @Ps = (SELECT 社員コード FROM 受注 WHERE 受注コード = @Cd)
-- 手順４：社員情報を表示する
SELECT 社員コード,氏名 FROM 社員 WHERE (社員コード = @Ps)
-- 次の値を取得する
FETCH NEXT FROM CODE_Cur INTO @Cd
END
-- カーソルを閉じる
CLOSE CODE_Cur
-- 指定カーソルの資源全体の破棄
-- DECLARE宣言で得られたカーソル情報資源を解放するため
-- 再びOPENすることはできない
DEALLOCATE CODE_Cur
GO
-- *****************
-- 考え方−２番目
-- *****************
-- SQL文を２回の発行で済ませる方法です
-- 最大金額を求めた後に、表の結合で取得する
DECLARE @Max money -- 最大の受注金額
PRINT '【考え方−２番目】'
-- 手順１：最大の受注金額を取得します
SELECT @Max = (SELECT MAX(単価*数量*(100-割引)/100) FROM 受注明細)
-- 手順２：３個の表を等結合させます
SELECT 受注.社員コード,社員.氏名
FROM ( 受注明細 INNER JOIN 受注 ON 受注明細.受注コード = 受注.受注コード)
INNER JOIN 社員 ON 受注.社員コード = 社員.社員コード
WHERE ((100-受注明細.割引)/100*受注明細.単価*受注明細.数量)=@Max
GO
-- *****************
-- 考え方−３番目
-- *****************
-- １回のSQL文で求める
PRINT '【考え方−３番目】'
SELECT 受注.社員コード,社員.氏名
FROM ( 受注明細 INNER JOIN 受注 ON 受注明細.受注コード = 受注.受注コード)
INNER JOIN 社員 ON 受注.社員コード = 社員.社員コード
WHERE (
((100-受注明細.割引)/100*受注明細.単価*受注明細.数量) =
(SELECT MAX(単価*数量*(100-割引)/100) FROM 受注明細)
)
GO
-- *****************
-- 考え方−４番目
-- *****************
-- IN述語をうまく使用した１回のSQL文で求める
PRINT '【考え方−４番目】'
SELECT 社員.社員コード , 社員.氏名 FROM 社員
WHERE (
社員.社員コード
IN (
SELECT 受注.社員コード FROM 受注
WHERE (
受注.受注コード
IN (
SELECT 受注明細.受注コード FROM 受注明細
WHERE (
((100-受注明細.割引)/100*受注明細.単価*受注明細.数量)
= (SELECT MAX(単価*数量*(100-割引)/100) FROM 受注明細)
)
)
)
)
)
GO
