重複行削除の際、重複判定に指定したキー列項目以外の列も取得するSQL
SELECT DISTINCTでは重複判定に指定したキー項目しか返されない
指定されたカラム(列)で重複のないデータを返すSQL、SELECT DISTINCT(重複する行を削除するSELECT DISTINCT - 一所懸命に手抜きする)ですが
SELECT DISTINCT 列a,列b FROM テーブルX
とすると、返されるのは列a、列bだけです。
サンプルテーブル buyList
入荷日付 商品番号 商品名 当社原価 相手原価 -------- -------- ------ -------- -------- 20170102 12345 XA-55 3500 3500 20170103 132977 RX98 2980 3280 20170103 349401 NOK0655 2500 2500 20170103 349401 NOK0655 2500 2500 20170104 227291 NOK2355 3500 35000 20170104 227291 NOK2355 3500 35000 20170104 349401 NOK0655 2500 2500 20170105 5288923 JJ2CAN 6000 6200 20170105 132977 RX98 2780 2980
-- SQlite3 SELECT DISTINCT 商品番号,商品名 FROM buyList ORDER BY 商品番号; /* 実行結果 商品番号 商品名 -------- -------- 12345 XA-55 132977 RX98 227291 NOK2355 349401 NOK0655 5288923 JJ2CAN */
このように重複判定に指定したキー項目以外は返されません。
キー項目以外にも重要情報があることも
例えば、132977 RX98は次の2レコードが重複しています。この2行は日付とともに原価が異なっています。原価が引き下げられたということです。商品番号・商品名は重複していても原価の違いは重要です。
入荷日付 商品番号 商品名 当社原価 相手原価 20170103 132977 RX98 2980 3280 20170105 132977 RX98 2780 2980
最新原価を知りたいという場合には商品番号 商品名だけでなく他の項目も必要ですが、SELECT DISTINCTではそれはできません。
重複判定に指定したキー項目以外の列も取得するSQL
まず重複を削除した後に残すレコードの条件を指定する
重複を削除した後に残すレコードはどれでも良いというものではなく、その商品の最終仕入明細データにしたいとします。
まずは仕入順にソートします
商品毎にグループ化し
残すのはグループ内の仕入順の最後(最大行番号)のレコード
この条件に従うと…
サンプルテーブル buyList
入荷日付 商品番号 商品名 当社原価 相手原価 -------- -------- ------ -------- -------- 20170102 12345 XA-55 3500 3500 ←この商品の当月最終仕入 20170103 132977 RX98 2980 3280 20170103 349401 NOK0655 2500 2500 20170103 349401 NOK0655 2500 2500 20170104 227291 NOK2355 3500 35000 20170104 227291 NOK2355 3500 35000 ←最終 20170104 349401 NOK0655 2500 2500 ←最終 20170105 5288923 JJ2CAN 6000 6200 ←最終 20170105 132977 RX98 2780 2980 ←最終
サブクエリで重複行を削除し、メインクエリで項目をselectする
この抽出ロジックをSQLにすると
-- SQlite3 SELECT * FROM buyList WHERE rowid in (SELECT max(rowid) FROM buyList GROUP BY 商品番号 ORDER BY 入荷日付); /* 実行結果 入荷日付 商品番号 商品名 当社原価 相手原価 -------- -------- ------ -------- -------- 20170102 12345 XA-55 3500 3500 20170104 227291 NOK2355 3500 35000 20170104 349401 NOK0655 2500 2500 20170105 5288923 JJ2CAN 6000 6200 20170105 132977 RX98 2780 2980 */
サブクエリでは残す行を条件指定し、残す行の行番号を抽出しています。
商品番号ごとにグループ化して…GROUP BY 商品番号
その中で日付順(ORDER BY 入荷日付)の最大行番号…max(rowid)を求めます。
主クエリでは、サブクエリで求めた行番号に合致するレコードから全項目を抽出しています。
SELECT * の部分に必要な列を指定することで抽出項目を指定することもできます。
In SQL,
SELECT DISTINCT eliminates duplicated records and returns only distinct/unique columns.
If you want to get non-selected columns' value, it's useless to use SELECT DISTINCT.
Indeed, in some case, grouping by columns combination , some other columns' values are needed .
Using sub-query can accomplish such a task.
In sub-query, grouping by some conditions ,SQL extracts representative rows of every group.
In main-query, SQL can return any columns of the representative rows.
example: SQL to extract last purchases of every item
SELECT pchDate,itemNo,itemName,price,mount FROM buyList WHERE rowid in
(SELECT max(rowid) FROM buyList GROUP BY itemNo ORDER BY pchDate);
1.In sub-query ,
grouping by itemNo(itemName) and
sorting purchased date in the ascending order,
SQL returns the rownumber of the last purchase of every item.
2.In main-query ,
SQL returns some selected columns' values of the represented rows - the last purchase of every item- .
SELECT DISTINCT eliminates duplicated records and returns only distinct/unique columns.
If you want to get non-selected columns' value, it's useless to use SELECT DISTINCT.
Indeed, in some case, grouping by columns combination , some other columns' values are needed .
Using sub-query can accomplish such a task.
In sub-query, grouping by some conditions ,SQL extracts representative rows of every group.
In main-query, SQL can return any columns of the representative rows.
example: SQL to extract last purchases of every item
SELECT pchDate,itemNo,itemName,price,mount FROM buyList WHERE rowid in
(SELECT max(rowid) FROM buyList GROUP BY itemNo ORDER BY pchDate);
1.In sub-query ,
grouping by itemNo(itemName) and
sorting purchased date in the ascending order,
SQL returns the rownumber of the last purchase of every item.
2.In main-query ,
SQL returns some selected columns' values of the represented rows - the last purchase of every item- .
