teratail
回答率
85.48%
質問するログイン新規登録
トップMySQLに関する質問DBを使わず、sqlで複数行の結果を得たい

編集履歴

回答編集履歴

3

修正

2019/09/10 14:14

投稿

退会済みユーザー
test CHANGED
@@ -6,19 +6,15 @@
6
6
 
7
7
  WITH T1 AS (
8
8
 
9
- SELECT 0
9
+ SELECT 0 UNION ALL SELECT 0
10
10
 
11
11
  ), T2 AS (
12
12
 
13
- SELECT * FROM T1 UNION ALL SELECT * FROM T1
14
-
15
- ), T3 AS (
16
-
17
- SELECT * FROM T2 S1 CROSS JOIN T2 S2
13
+ SELECT * FROM T1 S1 CROSS JOIN T1 S2
18
14
 
19
15
  ), R AS (
20
16
 
21
- SELECT * FROM T3 S1 CROSS JOIN T3 S2
17
+ SELECT * FROM T2 S1 CROSS JOIN T2 S2
22
18
 
23
19
  )
24
20
 
@@ -26,14 +22,12 @@
26
22
 
27
23
  SELECT ABS(RANDOM()) % 10
28
24
 
29
- FROM R AS R1 CROSS JOIN
25
+ FROM R AS R1
30
26
 
31
- R AS R2 CROSS JOIN
27
+ CROSS JOIN R AS R2
32
28
 
33
- R AS R3
29
+ CROSS JOIN R AS R3
34
30
 
35
31
  LIMIT 3000;
36
32
 
37
-
38
-
39
33
  ```

2

変更

2019/09/10 14:14

投稿

退会済みユーザー
test CHANGED
@@ -6,17 +6,11 @@
6
6
 
7
7
  WITH T1 AS (
8
8
 
9
- SELECT 0 , 1, 2, 3, 4, 5, 6, 7, 8, 9
9
+ SELECT 0
10
10
 
11
11
  ), T2 AS (
12
12
 
13
- SELECT * FROM T1
14
-
15
- UNION ALL SELECT * FROM T1
13
+ SELECT * FROM T1 UNION ALL SELECT * FROM T1
16
-
17
- UNION ALL SELECT * FROM T1
18
-
19
- UNION ALL SELECT * FROM T1
20
14
 
21
15
  ), T3 AS (
22
16
 
@@ -30,17 +24,15 @@
30
24
 
31
25
 
32
26
 
33
- SELECT
27
+ SELECT ABS(RANDOM()) % 10
34
28
 
35
- COUNT(1) --16777216
29
+ FROM R AS R1 CROSS JOIN
36
30
 
37
- FROM
31
+ R AS R2 CROSS JOIN
38
32
 
39
- R AS R1 CROSS JOIN R AS R2 CROSS JOIN R AS R3
33
+ R AS R3
40
34
 
41
-
35
+ LIMIT 3000;
42
-
43
-
44
36
 
45
37
 
46
38
 

1

変更

2019/09/10 14:02

投稿

退会済みユーザー
test CHANGED
@@ -10,23 +10,21 @@
10
10
 
11
11
  ), T2 AS (
12
12
 
13
+ SELECT * FROM T1
14
+
13
- SELECT * FROM T1 UNION ALL SELECT * FROM T1
15
+ UNION ALL SELECT * FROM T1
16
+
17
+ UNION ALL SELECT * FROM T1
18
+
19
+ UNION ALL SELECT * FROM T1
14
20
 
15
21
  ), T3 AS (
16
22
 
17
- SELECT * FROM T2 UNION ALL SELECT * FROM T2
23
+ SELECT * FROM T2 S1 CROSS JOIN T2 S2
18
24
 
19
25
  ), R AS (
20
26
 
21
- SELECT * FROM T3
27
+ SELECT * FROM T3 S1 CROSS JOIN T3 S2
22
-
23
- UNION ALL SELECT * FROM T3
24
-
25
- UNION ALL SELECT * FROM T3
26
-
27
- UNION ALL SELECT * FROM T3
28
-
29
- UNION ALL SELECT * FROM T3
30
28
 
31
29
  )
32
30
 
@@ -34,11 +32,15 @@
34
32
 
35
33
  SELECT
36
34
 
37
- COUNT(1) --160000
35
+ COUNT(1) --16777216
38
36
 
39
37
  FROM
40
38
 
41
- R AS R1 CROSS JOIN R AS R2 CROSS JOIN R R3 CROSS JOIN R R4
39
+ R AS R1 CROSS JOIN R AS R2 CROSS JOIN R AS R3
40
+
41
+
42
+
43
+
42
44
 
43
45
 
44
46