質問編集履歴

CREATE TABLE部分を修正しました(不要なAUTO INCREMENTを含んでいたため)

2021/07/19 00:30

投稿

hika-rakuyo

スコア15

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -60,7 +60,7 @@
   PRIMARY KEY (`id`)
-) ENGINE=InnoDB AUTO_INCREMENT=320 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -88,7 +88,7 @@
   KEY `shops_project_id_index` (`project_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=320 DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
@@ -128,33 +128,177 @@
   KEY `earnings_shop_id_index` (`shop_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+INSERT INTO `earnings` VALUES
+(1,999,1,'1','リンゴ味','2021-06-15','まあまあ売れた。',10,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
+(2,999,1,'2','ブドウ味','2021-06-20','少し売れた。',5,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
+(3,999,1,'1','リンゴ味','2021-06-30','かなり売れた。',20,'2021-07-01 00:00:00','2021-07-01 00:00:00');
+```
+このうち、`earnings` テーブルには、ある店舗(`shop_id`)のある日(`earned_date`)におけるある商品(`item_id`)の総売上個数(`earning`)を、それひとつのみ入れていく形になります。
+(テーブルの定義で`unique`系の制約もなく、分かりにくくて申し訳ありません…。)
+なお、`item`に関するテーブルもあるのですが、必要な情報は`earnings`テーブルに織り込んでいて結合にも使用しないため、ここでは割愛します。
+### 実行内容
+```SQL
+SELECT
+  `shops`.`id` AS `shop_id`,
+  `E1`.`id` AS `earning_id`,
+  `E1`.`earning` AS `max_earning`,
+  `E1`.`earned_date` AS `earned_date`,
+  `E1`.`comment` AS `comment`
+FROM
+  `shops`
+LEFT JOIN
+  `earnings` AS `E1`
+ON
+  `E1`.`shop_id` = `shops`.`id`
+AND
+  `E1`.`item_id` = 2    # ** POINT **
+WHERE
+  `shops`.`project_id` = 999
+AND
+  NOT EXISTS(
+    SELECT
+      1
+    FROM
+      `earnings` AS `E2`
+    WHERE
+      E1.shop_id = E2.shop_id
+    AND
+      E1.earning < E2.earning
+  )
+;
+```
+### 発生している問題
+上記コードの`** POINT **`行にて、値を1にした場合と2にした場合で実行結果が変わってしまいます。
+<`E1.item_id = 1`の場合>
+![実行結果1](5d455b9697483dc24dde1bbd2199288a.png)
+<`E1.item_id = 2`の場合>
+![実行結果2](b525f13f15af4b2d44fe3aef0fcfe902.png)
+`shop_id=1`でかつ`item_id=2`のレコードは一つ用意されているはずなのに、それがスキップされてしまいます。
+### 試したこと
+**● `item_id` のデータ型が `varchar`になっている点に着目**
+[1] データ型とINSERTを修正する → 実行結果変わらず
+```SQL
+CREATE TABLE `earnings` (
+  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `project_id` int(11) NOT NULL,
+  `shop_id` int(11) NOT NULL,
+  `item_id` int(11) NOT NULL,
+ # `item_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,  ** ここを上記に修正
+  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
+  `earned_date` date NOT NULL,
+  `comment` text COLLATE utf8_unicode_ci NOT NULL,
+  `earning` int(10) unsigned NOT NULL,
+  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  PRIMARY KEY (`id`),
+  KEY `earnings_project_id_index` (`project_id`),
+  KEY `earnings_shop_id_index` (`shop_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 INSERT INTO `earnings` VALUES
-(1,999,1,'1','リンゴ味','2021-06-15','まあまあ売れた。',10,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
+(1,999,1,1,'リンゴ味','2021-06-15','まあまあ売れた。',10,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
-(2,999,1,'2','ブドウ味','2021-06-20','少し売れた。',5,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
+(2,999,1,2,'ブドウ味','2021-06-20','少し売れた。',5,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
-(3,999,1,'1','リンゴ味','2021-06-30','かなり売れた。',20,'2021-07-01 00:00:00','2021-07-01 00:00:00');
+(3,999,1,1,'リンゴ味','2021-06-30','かなり売れた。',20,'2021-07-01 00:00:00','2021-07-01 00:00:00');
+#********↑ ここの''を外す
 ```
-このうち、`earnings` テーブルには、ある店舗(`shop_id`)のある日(`earned_date`)におけるある商品(`item_id`)の総売上個数(`earning`)を、それひとつのみ入れていく形になります。
-(テーブルの定義で`unique`系の制約もなく、分かりにくくて申し訳ありません…。)
+[2] WHEREクエリを文字列検索向けに修正する → 実行結果変わらず
-なお、`item`に関するテーブルもあるのですが、必要な情報は`earnings`テーブルに織り込んでいて結合にも使用しないため、ここでは割愛します。
-### 実行内容
 ```SQL
@@ -184,12 +328,76 @@
 AND
+  `E1`.`item_id` LIKE '2'    # ** ここを修正 **
+WHERE
+  `shops`.`project_id` = 999
+AND
+  NOT EXISTS(
+    SELECT
+      1
+    FROM
+      `earnings` AS `E2`
+    WHERE
+      E1.shop_id = E2.shop_id
+    AND
+      E1.earning < E2.earning
+  )
+;
+```
+**● いじった中での発見…**
+[3] 15,16行目の `project_id`による絞込み制約を消すと、中身は`(null)`だが一応`shop_id=1`の列が立つ
+```SQL
+SELECT
+  `shops`.`id` AS `shop_id`,
+  `E1`.`id` AS `earning_id`,
+  `E1`.`earning` AS `max_earning`,
+  `E1`.`earned_date` AS `earned_date`,
+  `E1`.`comment` AS `comment`
+FROM
+  `shops`
+LEFT JOIN
+  `earnings` AS `E1`
+ON
+  `E1`.`shop_id` = `shops`.`id`
+AND
   `E1`.`item_id` = 2    # ** POINT **
-WHERE
-  `shops`.`project_id` = 999
 AND
   NOT EXISTS(
@@ -216,214 +424,6 @@
 ```
-### 発生している問題
-上記コードの`** POINT **`行にて、値を1にした場合と2にした場合で実行結果が変わってしまいます。
-<`E1.item_id = 1`の場合>
-![実行結果1](5d455b9697483dc24dde1bbd2199288a.png)
-<`E1.item_id = 2`の場合>
-![実行結果2](b525f13f15af4b2d44fe3aef0fcfe902.png)
-`shop_id=1`でかつ`item_id=2`のレコードは一つ用意されているはずなのに、それがスキップされてしまいます。
-### 試したこと
-**● `item_id` のデータ型が `varchar`になっている点に着目**
-[1] データ型とINSERTを修正する → 実行結果変わらず
-```SQL
-CREATE TABLE `earnings` (
-  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-  `project_id` int(11) NOT NULL,
-  `shop_id` int(11) NOT NULL,
-  `item_id` int(11) NOT NULL,
- # `item_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,  ** ここを上記に修正
-  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
-  `earned_date` date NOT NULL,
-  `comment` text COLLATE utf8_unicode_ci NOT NULL,
-  `earning` int(10) unsigned NOT NULL,
-  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
-  PRIMARY KEY (`id`),
-  KEY `earnings_project_id_index` (`project_id`),
-  KEY `earnings_shop_id_index` (`shop_id`)
-) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-INSERT INTO `earnings` VALUES
-(1,999,1,1,'リンゴ味','2021-06-15','まあまあ売れた。',10,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
-(2,999,1,2,'ブドウ味','2021-06-20','少し売れた。',5,'2021-07-01 00:00:00','2021-07-01 00:00:00'),
-(3,999,1,1,'リンゴ味','2021-06-30','かなり売れた。',20,'2021-07-01 00:00:00','2021-07-01 00:00:00');
-#********↑ ここの''を外す
-```
-[2] WHEREクエリを文字列検索向けに修正する → 実行結果変わらず
-```SQL
-SELECT
-  `shops`.`id` AS `shop_id`,
-  `E1`.`id` AS `earning_id`,
-  `E1`.`earning` AS `max_earning`,
-  `E1`.`earned_date` AS `earned_date`,
-  `E1`.`comment` AS `comment`
-FROM
-  `shops`
-LEFT JOIN
-  `earnings` AS `E1`
-ON
-  `E1`.`shop_id` = `shops`.`id`
-AND
-  `E1`.`item_id` LIKE '2'    # ** ここを修正 **
-WHERE
-  `shops`.`project_id` = 999
-AND
-  NOT EXISTS(
-    SELECT
-      1
-    FROM
-      `earnings` AS `E2`
-    WHERE
-      E1.shop_id = E2.shop_id
-    AND
-      E1.earning < E2.earning
-  )
-;
-```
-**● いじった中での発見…**
-[3] 15,16行目の `project_id`による絞込み制約を消すと、中身は`(null)`だが一応`shop_id=1`の列が立つ
-```SQL
-SELECT
-  `shops`.`id` AS `shop_id`,
-  `E1`.`id` AS `earning_id`,
-  `E1`.`earning` AS `max_earning`,
-  `E1`.`earned_date` AS `earned_date`,
-  `E1`.`comment` AS `comment`
-FROM
-  `shops`
-LEFT JOIN
-  `earnings` AS `E1`
-ON
-  `E1`.`shop_id` = `shops`.`id`
-AND
-  `E1`.`item_id` = 2    # ** POINT **
-AND
-  NOT EXISTS(
-    SELECT
-      1
-    FROM
-      `earnings` AS `E2`
-    WHERE
-      E1.shop_id = E2.shop_id
-    AND
-      E1.earning < E2.earning
-  )
-;
-```
 → 実行結果
 ![実行結果3](445391eb7406cb5aeafea78f8d897590.png)

解消しました。他追記もしています。

2021/07/19 00:30

投稿

hika-rakuyo

スコア15

test CHANGED Viewed

	@@ -1 +1 @@
1	- データのMAX値を持つレコードをGROUP BY を使わずに絞込み、別のテーブルに結合させると、思うような結果が得られない
1	+ 【解決】データのMAX値を持つレコードをGROUP BY を使わずに絞込み、別のテーブルに結合させると、思うような結果が得られない

test CHANGED Viewed

@@ -142,9 +142,9 @@
 ```
-このうち、｀earnings｀テーブルには、ある店舗(｀shop_id｀)のある日(｀earned_date｀)におけるある商品(｀item_id｀)の総売上個数(｀earning｀)を、それひとつのみ入れていく形になります。
+このうち、`earnings` テーブルには、ある店舗(`shop_id`)のある日(`earned_date`)におけるある商品(`item_id`)の総売上個数(`earning`)を、それひとつのみ入れていく形になります。
-(テーブルの定義で｀unique｀系の制約がなく、説明がないと分かりにくいですよね…。)
+(テーブルの定義で`unique`系の制約もなく、分かりにくくて申し訳ありません…。)
@@ -432,6 +432,14 @@
-必要であればLaravelのクエリビルダを掲載することも可能です。
+~~必要であればLaravelのクエリビルダを掲載することも可能です。
-ただし、このページとは別の質問としてお伺いする可能性もあります。予めご了承ください。
+ただし、このページとは別の質問としてお伺いする可能性もあります。予めご了承ください。~~
+↑ この質問ページ内でご回答を頂く中で、Laravelでの問題も解消できると確信を持てました。本当にありがとうございました。
+また、念のため申し上げますと、ここでは自分の手元にあるコードを別の例に書き換えて記載しております。
+データの内容などは架空のものになりますので、安心してご検証いただければ幸いです。

2021/07/19 00:21

投稿

hika-rakuyo

スコア15

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -142,6 +142,14 @@
 ```
+このうち、｀earnings｀テーブルには、ある店舗(｀shop_id｀)のある日(｀earned_date｀)におけるある商品(｀item_id｀)の総売上個数(｀earning｀)を、それひとつのみ入れていく形になります。
+(テーブルの定義で｀unique｀系の制約がなく、説明がないと分かりにくいですよね…。)
 なお、`item`に関するテーブルもあるのですが、必要な情報は`earnings`テーブルに織り込んでいて結合にも使用しないため、ここでは割愛します。

体裁を整えました

2021/07/17 16:50

投稿

hika-rakuyo

スコア15

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -356,6 +356,8 @@
 **● いじった中での発見…**
 [3] 15,16行目の `project_id`による絞込み制約を消すと、中身は`(null)`だが一応`shop_id=1`の列が立つ
 ```SQL