達人に学ぶ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