T-SQL

Last-modified: 2025-10-21 (火) 15:13:38

Transact-SQL

ＧＵＩツール

  • A5:SQL Mk-2 - フリーの汎用SQL開発環境/ER図ツール 
    複雑化するデータベース開発を支援するために開発されたフリーのSQL開発環境です。
高機能かつ軽量で、使い方が分かりやすいことを目標に開発されています。
SQLを実行したり、テーブルを編集するほかに、SQLの実行計画を取得したり、ER図を作成したりすることが出来ます

基本情報

  • 対応状況 
    SQL Server 2005 , SQL Server 2008 , SQL Server 2000でも動いたよ。
パスワードは空パスでは駄目のようだ。絶対パスワードが設定してあること。

書式、並び順の指定

例、文字列の項目を数字型のように並び変えたい時の対処方法
'1', '10', '2', '20'　→　1, 2, 10, 20

cast(No As int)
 
amazon.gif
 

初期設定/エラー情報

  • SSMS で「変更の保存が許可されていません」というエラー メッセージが表示される 参考 
    原因：SQL Server Management Studio で [テーブルの再作成が必要になる変更を保存しない] オプションが既定で有効になっているため
対策：
1.SQL Server Management Studio を開きます。
2.[ツール] メニューの [オプション] をクリックします。
3.[オプション] ウィンドウのナビゲーション ウィンドウで、[デザイナー] をクリックします。
4.[テーブルの再作成が必要になる変更を保存しない] チェック ボックスをオンまたはオフにして、[OK] をクリックします。
  • 各テーブルのレコード数を表示する 
    SELECT
   t.name AS TableName,
   p.rows AS RecordCount
FROM
   sys.tables AS t
INNER JOIN
   sys.partitions AS p ON t.object_id = p.object_id
WHERE
   p.index_id IN (0, 1)
ORDER BY
   t.name;
  • 各テーブルのカラム数を求める
SELECT
   TABLE_SCHEMA,
   TABLE_NAME,
   COUNT(*) AS COLUMN_COUNT
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   TABLE_SCHEMA = 'dbo'
GROUP BY
   TABLE_SCHEMA,
   TABLE_NAME
ORDER BY
   TABLE_SCHEMA,
   TABLE_NAME

サンプル

  • 今日の日付から2か月前の伝票番号を求める
DECLARE @Ymd nchar(6); -- 西暦　YYMMDD
DECLARE @PickNo nchar(11);
DECLARE @MonthAgo datetime;
  • 今日から2ヶ月前のPickNo求める
set @MonthAgo = dateadd(mm,-2,GETDATE())
set @Ymd = CONVERT(varchar, @MonthAgo, 12)
set @PickNo = @Ymd + '00000'
print @PickNo
  • 求めたPickNo以前のデータをテーブルを削除する 
    DELETE FROM [PickDB].[dbo].[BoxListDB] Where PickNo < @PickNo
DELETE FROM [PickDB].[dbo].[JusinDB] Where PickNo < @PickNo
DELETE FROM [PickDB].[dbo].[FG_PickNo] Where PickNo < @PickNo
DELETE FROM [PickDB].[dbo].[janDB] Where PickNo < @PickNo
結果:11090100000(2011/11/01の場合)
  • 移動平均（Null未対応）参考
    販売数の今日から過去10日のVol平均 
    SELECT saledate
,code
,AVG(vol) OVER(ORDER BY saledate ROWS BETWEEN 10 preceding AND  current row) AS "販売数の10日平均"
FROM sample
  • over partition by の使い方 
    select
 年
,年月
,出費
,sum(出費) over(partition by 年 order by 年月
                rows between 1 preceding and 1 preceding ) as "1行前の出費"
,sum(出費) over(partition by 年 order by 年月
                rows between 1 following and 1 following ) as "1行後の出費"
,count(*)  over(partition by 年 order by 年月
                rows between unbounded preceding and current row) as "現在の行数"
,count(*)  over(partition by 年 order by 年月
                rows between 1 following and unbounded following ) as "残り行数"
from データ;
  • 最大値のあるレコードの列項目を参照する方法 参考
  • ROW_NUMBER() の使い方
    グループごとに連番を振りなおす 
    SELECT
*  , ROW_NUMBER() OVER(PARTITION BY day1 ORDER BY day1,gengetu desc) Num
FROM [Kato].[dbo].[g]
order  by day1,Gengetu desc

実行結果例

CdGengetuDay1End1Vol1Num
591Dec-242024/2/9976351301
591Oct-242024/2/997582782
591Aug-242024/2/99761363
591Jun-242024/2/99761374
591Apr-242024/2/9976115
591Feb-242024/2/99750206
  • 前行のデータを表示する 
    Select  日付, 始値
,Lag(日付,1) OVER ( ORDER BY 日付) as '前日'
,Lag(始値,1) OVER ( ORDER BY 日付) as '前日225始値'
From Nk225
  • 1日単位でまとめて、最初、最高、最低、最終の価格と時刻を表示する
WITH CTE AS (
   SELECT
       trade_date,
       interval_time,
       high_price,
       low_price,
       FIRST_VALUE(interval_time) OVER (PARTITION BY trade_date ORDER BY interval_time) AS 最初時刻,
       LAST_VALUE(interval_time) OVER (PARTITION BY trade_date ORDER BY interval_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 終了時刻
   FROM
       [F].[dbo].[KijikaMin1]
)
SELECT
   trade_date,
   MIN(最初時刻) AS 最初時刻,
	(select top 1 open_price FROM [F].[dbo].[KijikaMin1] WHERE trade_date = CTE.trade_date AND interval_time = (SELECT MIN(interval_time) FROM [F].[dbo].[KijikaMin1] WHERE trade_date = cte.trade_date)) AS 最初価格,
	(SELECT TOP 1 interval_time FROM [F].[dbo].[KijikaMin1] WHERE trade_date = CTE.trade_date AND high_price = (SELECT MAX(high_price) FROM [F].[dbo].[KijikaMin1] WHERE trade_date = cte.trade_date)) AS 最高時刻,
   MAX(high_price) AS 最高価格,
	(SELECT TOP 1 interval_time FROM [F].[dbo].[KijikaMin1] WHERE trade_date = CTE.trade_date AND low_price = (SELECT Min(low_price) FROM [F].[dbo].[KijikaMin1] WHERE trade_date = cte.trade_date)) AS 最低時刻,
   MIN(low_price) AS 最低価格,
		MAX(終了時刻) AS 終了時刻,
	(select top 1 close_price FROM [F].[dbo].[KijikaMin1] WHERE trade_date = CTE.trade_date AND interval_time = (SELECT Max(interval_time) FROM [F].[dbo].[KijikaMin1] WHERE trade_date = cte.trade_date)) AS 終了価格
FROM
   CTE
GROUP BY
   trade_date;
  • テーブルに日付と金額のデータのうち、各月の最初の日と最後の日のデータのみを表示する
WITH DateRanked AS (
   SELECT
       日付,
       金額,
       ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(日付, '%Y-%m') ORDER BY 日付 ASC) AS RankAsc,
       ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(日付, '%Y-%m') ORDER BY 日付 DESC) AS RankDesc
   FROM テーブルA
)
SELECT
   日付,
   金額
FROM DateRanked
WHERE RankAsc = 1 OR RankDesc = 1
ORDER BY 日付;

DB設定関係

  • バックアップ 
    BACKUP DATABASE PickDB TO DISK = N'D:\BACKUP\PickDB.BAK' WITH INIT
  • インデックス再構成
USE [PickDB] ' DB指定
GO
SET STATISTICS TIME ON
 'インデックス再構成
ALTER INDEX [Index_janDB] ON [dbo].[janDB] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Idx_JusinDB] ON [dbo].[JusinDB] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [index_BoxListDB] ON [dbo].[BoxListDB] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [PK_FG_PickNo] ON [dbo].[FG_PickNo] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [IX_FG_PickNo] ON [dbo].[FG_PickNo] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Idx_sZMMAKER] ON [dbo].[sZMMAKER] REORGANIZE WITH ( LOB_COMPACTION = ON )
ALTER INDEX [Idx_sZMHIN] ON [dbo].[sZMHIN] REORGANIZE WITH ( LOB_COMPACTION = ON )
SET STATISTICS TIME OFF
GO

参考・読み物

参考書籍

 
amazon.gif

サイト内リンク

SQL Server系

その他DB系

その他

 
 
amazon.gif