teratail
回答率
85.47%
質問するログイン新規登録
トップMySQLに関する質問SQL 第一言語が取得出来ない。サブクエリが必要か？？

編集履歴

質問編集履歴

5

修正

2022/07/10 13:31

投稿

MS_tt
MS_tt

スコア22

test CHANGED
File without changes
test CHANGED
@@ -149,5 +149,5 @@
149
149
  ('MAR', 'Arabic', 'T', '65.0'),
150
150
  ('NLD', 'Arabic', 'F', '0.9'),
151
151
 
152
-
152
+ 省略〜
153
153
  ```
MySQL

4

テーマ変更。値の追加。

2022/07/10 13:30

投稿

MS_tt
MS_tt

スコア22

test CHANGED
@@ -1 +1 @@
1
- サブクエリが合わせられない。
1
+ SQL 第一言語が取得出来ない。サブクエリが必要か？？
test CHANGED
@@ -7,7 +7,7 @@
7
7
  ### 実行コード
8
8
  name（国名の方）と、languageが一致しません。
9
9
  第一言語を表示とのこと、is_officialがTで、percentageカラムが高いものが第一言語だと思うのですが、表示できないです。
10
- 現コードにサブクエリでpercentageカラムが高いものを指定しないといけないと思うのですが分からずです。
10
+ 現コードに,percentageカラムが高いものが第一言語なのですが、サブクエリで表示できるのでしょうか？
11
11
  分かる方居ましたらお願いします。
12
12
  ```sql
13
13
  SELECT
@@ -36,6 +36,39 @@
36
36
  (13, 'Scarlett Johansson', 'AUT', '1966-01-10 00:00:00', 55, 'model'),
37
37
  (14, 'Heath Ledger', 'USA', '1971-10-15 00:00:00', 50, 'actor'),
38
38
  (15, 'Edward Norton', 'GBR', '1988-03-12 00:00:00', 33, 'actor'),
39
+ (16, 'Keira Knightley', 'GBR', '1980-11-10 00:00:00', 41, 'model'),
40
+ (17, 'Bradley Cooper', 'USA', '1979-08-01 00:00:00', 42, 'fighter'),
41
+ (18, 'Will Ferrell', 'USA', '1966-01-15 00:00:00', 53, 'comedian'),
42
+ (19, 'Julia Roberts', 'GRC', '1964-09-01 00:00:00', 55, 'actor'),
43
+ (20, 'Daniel Craig', 'USA', '1977-12-09 00:00:00', 44, 'actor'),
44
+ (21, 'Ian McKellen', '', '1998-04-15 00:00:00', 23, 'singer'),
45
+ (22, 'Samuel L. Jackson', 'USA', '1960-04-09 00:00:00', 61, 'actor'),
46
+ (23, 'Ben Stiller', 'BRA', '1964-10-15 00:00:00', 57, 'actor'),
47
+ (24, 'Tommy Lee Jones', '', '1970-09-05 00:00:00', 51, 'comedian'),
48
+ (25, 'Antonio Banderas', 'USA', '1958-10-05 00:00:00', 63, 'actor'),
49
+ (26, 'Denzel Washington', 'USA', '1990-10-25 00:00:00', 31, 'model'),
50
+ (27, 'Steve Carell', 'USA', '1991-02-14 00:00:00', 30, 'actor'),
51
+ (28, 'Shia LaBeouf', 'CHL', '1991-10-05 00:00:00', 30, 'actor'),
52
+ (29, 'Megan Fox', 'GRC', '1992-09-23 00:00:00', 29, 'actor'),
53
+ (30, 'James Franco', 'USA', '1955-04-15 00:00:00', 66, 'actor'),
54
+ (31, 'Mel Gibson', 'COL', '1967-03-08 00:00:00', 54, 'singer'),
55
+ (32, 'Vin Diesel', 'USA', '1982-11-15 00:00:00', 39, 'singer'),
56
+ (33, 'Tim Allen', 'ISL', '1962-02-10 00:00:00', 59, 'actor'),
57
+ (34, 'Kevin Spacey', 'USA', '1990-04-15 00:00:00', 31, 'actor'),
58
+ (35, 'Jason Biggs', 'USA', '1979-03-25 00:00:00', 42, 'actor'),
59
+ (36, 'Seann William Scott', 'USA', '1957-02-22 00:00:00', 64, 'actor'),
60
+ (37, 'Jean-Claude Van Damme', 'USA', '1997-01-10 00:00:00', 24, 'model'),
61
+ (38, 'Zach Galifianakis', 'USA', '1969-04-22 00:00:00', 52, 'actor'),
62
+ (39, 'Owen Wilson', 'USA', '1988-06-05 00:00:00', 31, 'actor'),
63
+ (40, 'Christian Bale', 'USA', '1977-12-01 00:00:00', 44, 'actor'),
64
+ (41, 'Peter Jackson', 'USA', '1994-10-25 00:00:00', 27, 'model'),
65
+ (42, 'Sandra Bullock', 'CUA', '1975-07-19 00:00:00', 46, 'actor'),
66
+ (43, 'Drew Barrymore', 'USA', '1993-06-05 00:00:00', 28, 'singer'),
67
+ (44, 'Macaulay Culkin', 'ESP', '1981-07-22 00:00:00', 40, 'comedian'),
68
+ (45, 'Bill Murray', 'USA', '1988-03-09 00:00:00', 33, 'actor'),
69
+ (46, 'Sigourney Weaver', 'FIN', '1954-12-10 00:00:00', 67, 'actor'),
70
+ (47, 'Jake Gyllenhaal', '', '1990-05-05 00:00:00', 31, 'model'),
71
+ (48, 'Jason Statham', 'USA', '1986-06-08 00:00:00', 35, 'actor');
39
72
  ```
40
73
 
41
74
  countries　テーブル
@@ -51,6 +84,23 @@
51
84
  ('ANT', 'Netherlands Antilles', 'North America', 'Caribbean', '800.00', NULL, 217000, '74.7', '1941.00', NULL, 'Nederlandse Antillen', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 33, 'AN'),
52
85
  ('ARE', 'United Arab Emirates', 'Asia', 'Middle East', '83600.00', 1971, 2441000, '74.1', '37966.00', '36846.00', 'Al-Imarat al-´Arabiya al-Muttahida', 'Emirate Federation', 'Zayid bin Sultan al-Nahayan', 65, 'AE'),
53
86
  ('ARG', 'Argentina', 'South America', 'South America', '2780400.00', 1816, 37032000, '75.1', '340238.00', '323310.00', 'Argentina', 'Federal Republic', 'Fernando de la Rúa', 69, 'AR'),
87
+ ('ARM', 'Armenia', 'Asia', 'Middle East', '29800.00', 1991, 3520000, '66.4', '1813.00', '1627.00', 'Hajastan', 'Republic', 'Robert Kotšarjan', 126, 'AM'),
88
+ ('ASM', 'American Samoa', 'Oceania', 'Polynesia', '199.00', NULL, 68000, '75.1', '334.00', NULL, 'Amerika Samoa', 'US Territory', 'George W. Bush', 54, 'AS'),
89
+ ('ATA', 'Antarctica', 'Antarctica', 'Antarctica', '13120000.00', NULL, 0, NULL, '0.00', NULL, '–', 'Co-administrated', '', NULL, 'AQ'),
90
+ ('ATF', 'French Southern territories', 'Antarctica', 'Antarctica', '7780.00', NULL, 0, NULL, '0.00', NULL, 'Terres australes françaises', 'Nonmetropolitan Territory of France', 'Jacques Chirac', NULL, 'TF'),
91
+ ('ATG', 'Antigua and Barbuda', 'North America', 'Caribbean', '442.00', 1981, 68000, '70.5', '612.00', '584.00', 'Antigua and Barbuda', 'Constitutional Monarchy', 'Elisabeth II', 63, 'AG'),
92
+ ('AUS', 'Australia', 'Oceania', 'Australia and New Zealand', '7741220.00', 1901, 18886000, '79.8', '351182.00', '392911.00', 'Australia', 'Constitutional Monarchy, Federation', 'Elisabeth II', 135, 'AU'),
93
+ ('AUT', 'Austria', 'Europe', 'Western Europe', '83859.00', 1918, 8091800, '77.7', '211860.00', '206025.00', 'Österreich', 'Federal Republic', 'Thomas Klestil', 1523, 'AT'),
94
+ ('AZE', 'Azerbaijan', 'Asia', 'Middle East', '86600.00', 1991, 7734000, '62.9', '4127.00', '4100.00', 'Azärbaycan', 'Federal Republic', 'Heydär Äliyev', 144, 'AZ'),
95
+ ('BDI', 'Burundi', 'Africa', 'Eastern Africa', '27834.00', 1962, 6695000, '46.2', '903.00', '982.00', 'Burundi/Uburundi', 'Republic', 'Pierre Buyoya', 552, 'BI'),
96
+ ('BEL', 'Belgium', 'Europe', 'Western Europe', '30518.00', 1830, 10239000, '77.8', '249704.00', '243948.00', 'België/Belgique', 'Constitutional Monarchy, Federation', 'Albert II', 179, 'BE'),
97
+ ('BEN', 'Benin', 'Africa', 'Western Africa', '112622.00', 1960, 6097000, '50.2', '2357.00', '2141.00', 'Bénin', 'Republic', 'Mathieu Kérékou', 187, 'BJ'),
98
+ ('BFA', 'Burkina Faso', 'Africa', 'Western Africa', '274000.00', 1960, 11937000, '46.7', '2425.00', '2201.00', 'Burkina Faso', 'Republic', 'Blaise Compaoré', 549, 'BF'),
99
+ ('BGD', 'Bangladesh', 'Asia', 'Southern and Central Asia', '143998.00', 1971, 129155000, '60.2', '32852.00', '31966.00', 'Bangladesh', 'Republic', 'Shahabuddin Ahmad', 150, 'BD'),
100
+ ('BGR', 'Bulgaria', 'Europe', 'Eastern Europe', '110994.00', 1908, 8190900, '70.9', '12178.00', '10169.00',
101
+
102
+
103
+ 省略〜
54
104
  ```
55
105
  countrylanguages テーブル
56
106
 
@@ -70,4 +120,34 @@
70
120
  ('BEN', 'Aizo', 'F', '8.7'),
71
121
  ('CIV', 'Akan', 'F', '30.0'),
72
122
  ('GHA', 'Akan', 'F', '52.4'),
123
+ ('ALB', 'Albaniana', 'T', '97.9'),
124
+ ('ITA', 'Albaniana', 'F', '0.2'),
125
+ ('MKD', 'Albaniana', 'F', '22.9'),
126
+ ('YUG', 'Albaniana', 'F', '16.5'),
127
+ ('AGO', 'Ambo', 'F', '2.4'),
128
+ ('ETH', 'Amhara', 'F', '30.0'),
129
+ ('TWN', 'Ami', 'F', '0.6'),
130
+ ('TGO', 'Ane', 'F', '5.7'),
131
+ ('ARE', 'Arabic', 'T', '42.0'),
132
+ ('AUS', 'Arabic', 'F', '1.0'),
133
+ ('BEL', 'Arabic', 'F', '1.6'),
134
+ ('BHR', 'Arabic', 'T', '67.7'),
135
+ ('DJI', 'Arabic', 'T', '10.6'),
136
+ ('DNK', 'Arabic', 'F', '0.7'),
137
+ ('DZA', 'Arabic', 'T', '86.0'),
138
+ ('EGY', 'Arabic', 'T', '98.8'),
139
+ ('ESH', 'Arabic', 'T', '100.0'),
140
+ ('FRA', 'Arabic', 'F', '2.5'),
141
+ ('GIB', 'Arabic', 'F', '7.4'),
142
+ ('IRN', 'Arabic', 'F', '2.2'),
143
+ ('IRQ', 'Arabic', 'T', '77.2'),
144
+ ('ISR', 'Arabic', 'T', '18.0'),
145
+ ('JOR', 'Arabic', 'T', '97.9'),
146
+ ('KWT', 'Arabic', 'T', '78.1'),
147
+ ('LBN', 'Arabic', 'T', '93.0'),
148
+ ('LBY', 'Arabic', 'T', '96.0'),
149
+ ('MAR', 'Arabic', 'T', '65.0'),
150
+ ('NLD', 'Arabic', 'F', '0.9'),
151
+
152
+
73
153
  ```
MySQL

3

テーマ変更

2022/07/09 14:57

投稿

MS_tt
MS_tt

スコア22

test CHANGED
@@ -1 +1 @@
1
- 高い順を組み合わせられない
1
+ サブクエリが合わせられない。
test CHANGED
File without changes
MySQL

2

内容の修正。

2022/07/08 09:44

投稿

MS_tt
MS_tt

スコア22

test CHANGED
File without changes
test CHANGED
@@ -6,9 +6,9 @@
6
6
 
7
7
  ### 実行コード
8
8
  name（国名の方）と、languageが一致しません。
9
- 第一言語を表示とのこと、おそらくこのコードにプラスで
9
+ 第一言語を表示とのこと、is_officialがTで、percentageカラムが高いものが第一言語だと思うのですが、表示できないです。
10
- coutrylanguagesテーブルのpercentageカラムが高いのを表示させないといけないと思います。
10
+ 現コードにサブクエリでpercentageカラムが高いものを指定しないといけないと思うのですが分からずです。
11
- 分かる方居ましたらお願い致します。
11
+ 分かる方居ましたらお願いします。
12
12
  ```sql
13
13
  SELECT
14
14
  `celebrities`.`name`, `countries`.`name`, `countrylanguages`.`language`
MySQL

1

画像ファイルからテキストに変更

2022/07/08 04:52

投稿

MS_tt
MS_tt

スコア22

test CHANGED
File without changes
test CHANGED
@@ -19,14 +19,55 @@
19
19
  AND countrylanguages.is_official = TRUE
20
20
  ```
21
21
  celebrities テーブル
22
+ ```sql
23
+ INSERT INTO `celebrities` (`id`, `name`, `country_code`, `birth`, `age`, `occupation`) VALUES
22
- [![Image from Gyazo](https://i.gyazo.com/ddcb641663a7b8e1b5dc0ff7dd78e7bd.png)](https://gyazo.com/ddcb641663a7b8e1b5dc0ff7dd78e7bd)
24
+ (1, 'Emma Charlotte Duerre Watson', 'FRA', '1990-04-15 00:00:00', 31, 'actor'),
25
+ (2, 'Johnny Depp', 'USA', '1963-06-09 00:00:00', 58, 'actor'),
26
+ (3, 'Jim Carrey', 'USA', '1962-01-17 00:00:00', 59, 'actor'),
27
+ (4, 'Daniel Radcliffe', 'GBR', '1989-07-23 00:00:00', 32, 'actor'),
28
+ (5, 'Morgan Freeman', 'USA', '1937-06-01 00:00:00', 84, 'actor'),
29
+ (6, 'Hugh Jackman', 'AUS', '1968-10-12 00:00:00', 53, 'actor'),
30
+ (7, 'Natalie Portman', '', '1981-06-09 00:00:00', 40, 'actor'),
31
+ (8, 'Pierce Brosnan', 'FRA', '1960-10-15 00:00:00', 61, 'actor'),
32
+ (9, 'Sean Connery', 'PER', '1980-05-05 00:00:00', 41, 'singer'),
33
+ (10, 'Dwayne Johnson', 'USA', '1978-12-15 00:00:00', 43, 'fighter'),
34
+ (11, 'Jackie Chan', 'USA', '1992-04-15 00:00:00', 29, 'fighter'),
35
+ (12, 'Adam Sandler', 'FRA', '1976-02-05 00:00:00', 45, 'singer'),
36
+ (13, 'Scarlett Johansson', 'AUT', '1966-01-10 00:00:00', 55, 'model'),
37
+ (14, 'Heath Ledger', 'USA', '1971-10-15 00:00:00', 50, 'actor'),
38
+ (15, 'Edward Norton', 'GBR', '1988-03-12 00:00:00', 33, 'actor'),
39
+ ```
23
40
 
24
41
  countries　テーブル
42
+ ```sql
25
43
 
26
- [![Image from Gyazo](https://i.gyazo.com/4e2dc775fb85f310877750dd4052f74f.png)](https://gyazo.com/4e2dc775fb85f310877750dd4052f74f)
27
-
28
- [![Image from Gyazo](https://i.gyazo.com/9f8fb43c8021f1f2eb5c59856245a7d2.png)](https://gyazo.com/9f8fb43c8021f1f2eb5c59856245a7d2)
29
-
44
+ INSERT INTO `countries` (`code`, `name`, `continent`, `region`, `surface_area`, `indep_year`, `population`, `life_expectancy`, `gnp`, `gnp_old`, `local_name`, `government_form`, `hea_of_state`, `capital`, `code2`) VALUES
45
+ ('ABW', 'Aruba', 'North America', 'Caribbean', '193.00', NULL, 103000, '78.4', '828.00', '793.00', 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 129, 'AW'),
46
+ ('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '652090.00', 1919, 22720000, '45.9', '5976.00', NULL, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF'),
47
+ ('AGO', 'Angola', 'Africa', 'Central Africa', '1246700.00', 1975, 12878000, '38.3', '6648.00', '7984.00', 'Angola', 'Republic', 'José Eduardo dos Santos', 56, 'AO'),
48
+ ('AIA', 'Anguilla', 'North America', 'Caribbean', '96.00', NULL, 8000, '76.1', '63.20', NULL, 'Anguilla', 'Dependent Territory of the UK', 'Elisabeth II', 62, 'AI'),
49
+ ('ALB', 'Albania', 'Europe', 'Southern Europe', '28748.00', 1912, 3401200, '71.6', '3205.00', '2500.00', 'Shqipëria', 'Republic', 'Rexhep Mejdani', 34, 'AL'),
50
+ ('AND', 'Andorra', 'Europe', 'Southern Europe', '468.00', 1278, 78000, '83.5', '1630.00', NULL, 'Andorra', 'Parliamentary Coprincipality', '', 55, 'AD'),
51
+ ('ANT', 'Netherlands Antilles', 'North America', 'Caribbean', '800.00', NULL, 217000, '74.7', '1941.00', NULL, 'Nederlandse Antillen', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 33, 'AN'),
52
+ ('ARE', 'United Arab Emirates', 'Asia', 'Middle East', '83600.00', 1971, 2441000, '74.1', '37966.00', '36846.00', 'Al-Imarat al-´Arabiya al-Muttahida', 'Emirate Federation', 'Zayid bin Sultan al-Nahayan', 65, 'AE'),
53
+ ('ARG', 'Argentina', 'South America', 'South America', '2780400.00', 1816, 37032000, '75.1', '340238.00', '323310.00', 'Argentina', 'Federal Republic', 'Fernando de la Rúa', 69, 'AR'),
54
+ ```
30
55
  countrylanguages テーブル
31
56
 
57
+ ```sql
58
+ INSERT INTO `countrylanguages` (`country_code`, `language`, `is_official`, `percentage`) VALUES
59
+ ('GEO', 'Abhyasi', 'F', '1.7'),
60
+ ('UGA', 'Acholi', 'F', '4.4'),
61
+ ('BEN', 'Adja', 'F', '11.1'),
62
+ ('DJI', 'Afar', 'F', '34.8'),
63
+ ('ERI', 'Afar', 'F', '4.3'),
64
+ ('NAM', 'Afrikaans', 'F', '9.5'),
32
- [![Image from Gyazo](https://i.gyazo.com/649c8d933185675aa68a0ba6746000ef.png)](https://gyazo.com/649c8d933185675aa68a0ba6746000ef)
65
+ ('ZAF', 'Afrikaans', 'T', '14.3'),
66
+ ('BOL', 'Aimará', 'T', '3.2'),
67
+ ('CHL', 'Aimará', 'F', '0.5'),
68
+ ('PER', 'Aimará', 'T', '2.3'),
69
+ ('JPN', 'Ainu', 'F', '0.0'),
70
+ ('BEN', 'Aizo', 'F', '8.7'),
71
+ ('CIV', 'Akan', 'F', '30.0'),
72
+ ('GHA', 'Akan', 'F', '52.4'),
73
+ ```
MySQL