【SQL Server(連載)】SELECT文 ORDER BYをやるのを忘れてました -19-

www.pon-x.jp

前回の続き

読者から、ソートの話が出てきているけど、ORDER BYの説明ってやってませんよね！？というご指摘を頂いた。
そのとおり、やっておりませんでした。
誠に失礼しました・・・。ということで、今回はソートの話です。

ORDER BY

ORDER BYは数値だったら小さい順に、文字だったらアルファベット順に並べてくれる。
日本語でのソートはひらがなやカタカナならよいが、漢字交じりであったらおすすめはしない。
では、Person.PersonテーブルのFirstNameでソートしてみる。

SELECT * 
FROM Person.Person
ORDER BY FirstName

こんなかんじにソートできる。

デフォルトは上り順だが、下り順もできる。
がASCが登り順で、DESCが下り順。

試しに下り順

SELECT * 
FROM Person.Person
ORDER BY FirstName DESC

複数のカラムを用いることもできる

例えば、最優先がFirstNameの上りで2番目にLastNameの下りでソートするとこうなる。

SELECT * 
FROM Person.Person
ORDER BY 
    FirstName ASC,
    LastName DESC

こんなかんじ。 ちなみにASCはなくても結果は同じ。

注意

エイリアスを指定している場合、例えばGROUP BYとかを使って集計し、集計結果でソートしたいときなんかは、エイリアス名を指定する。

SELECT 
    PersonType,
    count(BusinessEntityID) as CNT
FROM Person.Person
GROUP BY PersonType
ORDER BY CNT

こんなかんじになる。

実は、カラム順で指定できる。

左から何番目かという指定ができる。
めんどくさがり屋にはとてもよいが、可読性が悪くなるので注意が必要だ。

SELECT 
    PersonType,
    count(BusinessEntityID) as CNT
FROM Person.Person
GROUP BY PersonType
ORDER BY 2

数値の場合はデータ型に気を付けよう。

例えば、文字型の数値をソートすると大小関係ではなく、左から見て1桁目の大小関係を比較してしまう。
試しに、集計結果のカラムを文字型に変えてソートしてみる。

SELECT 
    PersonType,
    convert(varchar,count(BusinessEntityID)) as CNT
FROM Person.Person
GROUP BY PersonType
ORDER BY CNT

こんなかんじになってしまう。

たまに、文字列カラムに数値が入っていることがあるので、そんなときは注意しましょう。

以上、今回はここまで。
ちなみにCASE式を使ったソートは前回やったのでそちらをご参照ください。m( )m

【SQL Server(連載)】CASE式の応用 -18-

www.pon-x.jp

前回の続き

前回、CASE式をやりましたが、今回は応用的な事例を紹介します。

並び替えに使う。

ORDER BY で並び替えができるのですが、CASE式をもっておもしろい使い方ができます。

例えば、以下のようにPerson.PersonテーブルをPersonTypeでソートするにはいかのようになります。

SELECT *
FROM Person.Person
ORDER BY PersonType

このとき、PersonTypeでソートするけれども、データが「IN」のレコードは最優先で上位としたいいったとき、以下のように書けます。

SELECT *
FROM Person.Person
ORDER BY 
    CASE WHEN PersonType='IN' THEN 0 ELSE 1 END,
    PersonType

こんなかんじで実装できます。

UPDATEでつかう。

変更するデータにCASE式を用いることができます。

UPDATE Person.Person_Copy
SET Suffix = CASE WHEN PersonType='IN' THEN 0 ELSE 1 END

画像だと一部だけですが、確認してみます。

SELECT * FROM Person.Person_Copy

こんかかんじで、スカラ(単一)な値であればまだまだこれ以外も多様な使い道があります。
ぜひ使い方を覚えておきましょう。

【SQL Server(連載)】CASE式 -17-

www.pon-x.jp

前回の続き

今回は、条件分岐を目的とするCASE式を紹介します。

CASE式

CASE式は、もしデータが、〇〇だったら××、△△だったら□□みたいなかんじに読み替えてあげることを目的とします。
便利なのは、SELECT文をはじめ、UPDATE、DELETE、INSERTなどででも使え、さらにSELECT句、WHERE句、ORDER句、などなどスカラであらわされる部分なら大抵は使えるのが特徴です。

文法はこんな感じ

CASE 
    WHEN [比較元データ] = [比較データ]
    THEN [出力したいデータ]

    WHEN [元データ] = [比較データ]
    THEN [出力したいデータ]

    ・・・

    ELSE [出力したいデータ]
END

もしくは、

CASE 
    [比較元データ] 
    WHEN [比較データ]
    THEN [出力したいデータ]

    WHEN [比較データ]
    THEN [出力したいデータ]

    ・・・

    ELSE [出力したいデータ]
END

ちなみに、ELSEは指定しなくてもよいが、指定しないでどの条件にも当てはまらないと、NULL出力になります。

実践してみる

Person.Personテーブルについて、PersonTypeがEMならAを、GCならBを、それ以外ならCを表示するようにしてみます。
※　比較しやすいように、PersonTypeカラムも表示して、かつ重複削除しています。

SELECT 
    DISTINCT
    PersonType,
    CASE PersonType
        WHEN 'EM' THEN 'A'
        WHEN 'GC' THEN 'B'
        ELSE 'C'
    END AS TEST_CASE
FROM Person.Person

結果はこんな感じ

こんなかんじにけっこう多用するCASE式について、次回は使い方の応用を紹介したいと思います。

【SQL Server(連載)】INSERT文(SELECT文の利用) -16-

www.pon-x.jp

前回の続き

前回からINSERT文に入りました。
今回は、それの応用です。

SELECT文を使った、INSERT文

例えば特定のレコードの内容の一部のカラムを変えてレコードを追加したい。なんてことがたまにある。

今回の目的は、そんなときにSELECT文を使ってデータを挿入することです。
まずはPerson.PersonPhone テーブルについて、以下の結果を見てみてください。

SELECT * 
FROM Person.PersonPhone 
WHERE BusinessEntityID = 1

こんな感じに出力されます。

これを利用して、PhoneNumberTypeIDだけ変更したデータを挿入することができます。
文法はこんなかんじ。

INSERT INTO [テーブル名]
SELECT [カラム名] ,[カラム名] ... FROM [テーブル名]

実際に書くとこんなかんじ。

INSERT INTO Person.PersonPhone
SELECT 
    BusinessEntityID,
    PhoneNumber,
    2,
    SYSDATETIME() 
FROM Person.PersonPhone 
WHERE BusinessEntityID = 1

結果を確認してみましょう。

SELECT * FROM Person.PersonPhone WHERE BusinessEntityID = 1

追加したデータが確認できました。

今回は、同じテーブルからデータを作成して追加しましたが、別のテーブルを利用したり、JOINしたりなんかもできます。

最後に練習用データなので、消しておきます。

DELETE FROM Person.PersonPhone WHERE BusinessEntityID = 1 AND PhoneNumberTypeID = 2

以上、今回はここまで。

【SQL Server(連載)】INSERT文 -15-

www.pon-x.jp

前回の続き

今回は、データの挿入INSERTをやりたいと思います。

INSERT

INSERT文はレコードを挿入するために使います。
文法はこんな感じ

INSERT INTO [テーブル名]
VALUES([データ1], [データ2]…)

例えば、Sales.SalesReasonテーブルにデータを追加してみましょう。
なお、SalesReasonカラムは自動採番になっているので、データを指定しなくてもよいものになっています。

INSERT INTO Sales.SalesReason
VALUES('Radio Advertisement','Other',SYSDATETIME())

以下のように1件データが追加されました。

念のため確認しますが、たしかにレコードが追加されています。

なお、今回は適当なデータを作ったのでデータを消しておきましょう。

DELETE FROM Sales.SalesReason WHERE SalesReasonID > 10

カラムを指定して挿入する。

例えば、データをNULLで挿入していい場合は、わざわざNULLというデータを書く必要はありません。
この場合は、カラムを指定してINSERTすればよいのです。

INSERT INTO [テーブル名]
([カラム1], [カラム2]…)
VALUES([データ1], [データ2]…)

こんな感じに書きます。
では次は、Production.ProductCostHistoryテーブルを使います。
Production.ProductCostHistoryは、EndDateがNULLのデータを許容しているので、これらのデータを指定しないでデータを挿入してみます。

INSERT INTO Production.ProductCostHistory
(ProductID,StartDate,StandardCost,ModifiedDate)
VALUES(999,SYSDATETIME(),0,SYSDATETIME())

確認してみたところ確かに挿入されており、指定していないカラムはNULLが入っていました。

練習データを消しておきましょう。

DELETE FROM Production.ProductCostHistory WHERE ProductID = 999 and StandardCost = 0

以上、今回はここまで。
次回もINSERT文をやります！！

【SQL Server(連載)】DELETE文 -14-

www.pon-x.jp

前回の続き

今回から、レコードの削除を行う、DELETE文を紹介します。

DELETE

ここまで、SELECT文やUPDATE文をやってきましたが、これらの内容が抑えられていればそんなに難しいものではありません。

DELETE文はこんな感じに書きます。

DELETE FROM [テーブル名]

これだけで、テーブル内のデータを全削除できます。
レコード単位に削除するので、カラム名は関係ないため指定しません。
しかし、削除したいデータというのは、常に条件指定があるものです。

WHERE句の利用

WHERE句を用いると、レコードを抽出して削除することができます。

例えば、Person.Person_CopyテーブルのPersonTypeがEMのレコードだけ削除してみます。

DELETE Person.Person_Copy
WHERE PersonType = 'EM'

結果はこんな感じです。

273件のみ削除できました。

JOINの活用

他のテーブルと結合して、そのカラムのデータによってレコードを抽出して削除することもできます。
ここでは、Person.Person_CopyとPerson.PersonPhoneを内部結合してPhoneNumberの1桁目が「１」のレコードを削除してみます。

こんなかんじでできます。

DELETE PPC
FROM Person.Person_Copy PPC
    INNER JOIN Person.PersonPhone PPP
        ON PPC.BusinessEntityID = PPP.BusinessEntityID
WHERE PhoneNumber LIKE '1%'

UPDATEで結合したときと同じ書き方ですよね。






以上今回はここまで

【SQL Server(連載)】UPDATE文 (JOINの活用) -13-

www.pon-x.jp

前回の続き

前回からUPDATE文に入り、WHERE句を活用までやりました。

今回は、他のテーブルと結合してデータを更新する方法を紹介します。

JOINを活用する。

あるカラムのデータについて、そのテーブルと別のテーブルを結合させて、別のテーブルのカラムの値に書き換えたいということはたまにある。

こんなかんじに書くことになります。

UPDATE TBL1
SET [カラム名] = [変更したい値]
FROM [テーブル名] TBL1
    INNER JOIN [テーブル名] TBL2
        ON TBL1.KEY = TBL2.KEY

それでは、Person.Person_CopyとPerson.PersonPhoneを内部結合させ、Suffixに、PhoneNumberを代入させてみます。

UPDATE PPC
SET Suffix = PhoneNumberTypeID
FROM Person.Person_Copy PPC
    INNER JOIN Person.PersonPhone PPP
        ON PPC.BusinessEntityID = PPP.BusinessEntityID

2つのカラムが同一の値になっているか確認してみる。

SELECT
    Suffix,
    PhoneNumberTypeID
FROM Person.Person_Copy PPC
    INNER JOIN Person.PersonPhone PPP
        ON PPC.BusinessEntityID = PPP.BusinessEntityID

一緒になっているのが確認できます。

今回はここまで、