general notes

いろいろなまとめ

達人に学ぶSQL徹底指南書 1-2 自己結合の使い方

重複順列・順列・組み合わせ

テーブル定義

use PracticeSQL

create table tbl1_2_1_Products(
    prod_name nvarchar(10),
    price int
)

insert into tbl1_2_1_Products values
    ('りんご', 100),
    ('みかん', 50),
    ('バナナ', 80)

-- prod_nameの重複順列
select p1.prod_name as name1, p2.prod_name as name2
from tbl1_2_1_Products p1, tbl1_2_1_Products p2

--name1    name2
--りんご    りんご
--みかん    りんご
--バナナ    りんご
--りんご    みかん
--みかん    みかん
--バナナ    みかん
--りんご    バナナ
--みかん    バナナ
--バナナ    バナナ
-- prod_nameの順列
select p1.prod_name as name1, p2.prod_name as name2
from tbl1_2_1_Products p1, tbl1_2_1_Products p2
where p1.prod_name <> p2.prod_name

--name1    name2
--みかん    りんご
--バナナ    りんご
--りんご    みかん
--バナナ    みかん
--りんご    バナナ
--みかん    バナナ
-- prod_nameの組み合わせ
select p1.prod_name as name1, p2.prod_name as name2
from tbl1_2_1_Products p1, tbl1_2_1_Products p2
where p1.prod_name > p2.prod_name

--name1    name2
--りんご    みかん
--りんご    バナナ
--みかん    バナナ

内容重複行の抽出

テーブル定義

use PracticeSQL

create table tbl1_2_2_Products(
    rowid int identity(1, 1),
    prod_name nvarchar(10),
    price int
)

insert into tbl1_2_2_Products(prod_name, price) values
    ('りんご', 100),
    ('みかん', 50),
    ('みかん', 50),
    ('みかん', 50),
    ('バナナ', 80)

--重複行の抽出
select * from tbl1_2_2_Products p1
where rowid < (
    select max(p2.rowid) from tbl1_2_2_Products p2
    where p1.prod_name = p2.prod_name and p1.price = p2.price
)

--rowid    prod_name   price
--2    みかん   50
--3    みかん   50

部分的に不一致なキーの検索

テーブル定義

use PracticeSQL

create table tbl1_2_3_Addresses(
    name nvarchar(10),
    family_id int,
    address nvarchar(30)
)

insert into tbl1_2_3_Addresses values
    ('前田義明', 100, '東京都港区虎ノ門3-2-29'),
    ('前田由美', 100, '東京都港区虎ノ門3-2-92'),
    ('加藤茶', 200, '東京都新宿区西新宿2-8-1'),
    ('加藤勝', 200, '東京都新宿区西新宿2-8-1'),
    ('ホームズ', 300, 'ベイカー街221B'),
    ('ワトソン', 400, 'ベイカー街221B')

--family_idが同じでaddressが異なるレコードを抽出
select a1.name, a1.address from tbl1_2_3_Addresses a1, tbl1_2_3_Addresses a2
where a1.family_id = a2.family_id and a1.address <> a2.address

--name address
--前田義明 東京都港区虎ノ門3-2-29
--前田由美 東京都港区虎ノ門3-2-92

ランキング

テーブル定義

use PracticeSQL

create table tbl1_2_4_Products(
    name nvarchar(10),
    price int
)

insert into tbl1_2_4_Products values
    ('りんご', 50),
    ('みかん', 100),
    ('ぶどう', 50),
    ('スイカ', 80),
    ('レモン', 30),
    ('バナナ', 50)

-- ランキング算出(OLAP)
select
    name,
    price,
    RANK() over (order by price desc) as rank_1,
    DENSE_RANK() over(order by price desc) as rank_2
from
    tbl1_2_4_Products

--name price   rank_1  rank_2
--みかん    100 1   1
--スイカ    80  2   2
--ぶどう    50  3   3
--りんご    50  3   3
--バナナ    50  3   3
--レモン    30  6   4
-- RANK(自己非等値結合)
select 
    P1.name,
    P1.price,
    (select count(P2.price)
     from tbl1_2_4_Products P2
     where P2.price > P1.price) + 1 as rank_1   
from
    tbl1_2_4_Products P1

--name price   rank_1
--りんご    50  3
--みかん    100 1
--ぶどう    50  3
--スイカ    80  2
--レモン    30  6
--バナナ    50  3
-- DENSE_RANK(自己非等値結合)
select 
    P1.name,
    P1.price,
    (select count(distinct P2.price)
     from tbl1_2_4_Products P2
     where P2.price > P1.price) + 1 as rank_1   
from
    tbl1_2_4_Products P1

--name price   rank_1
--りんご    50  3
--みかん    100 1
--ぶどう    50  3
--スイカ    80  2
--レモン    30  4
--バナナ    50  3
--RANK(外部結合)
select 
    P1.name,
    max(P1.price) as price,
    count(P2.name) + 1 as rank_1    
from
    tbl1_2_4_Products P1
left outer join
    tbl1_2_4_Products P2
on
    P1.price < P2.price
group by
    P1.name

--name price   rank_1
--スイカ    80  2
--バナナ    50  3
--ぶどう    50  3
--みかん    100 1
--りんご    50  3
--レモン    30  6