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

編集履歴

回答編集履歴

3

修正

2019/09/10 14:14

投稿

退会済みユーザー
answer CHANGED
@@ -2,19 +2,16 @@
2
2
  SQLなら似たようなものが作成できると思います。
3
3
  ```SQL
4
4
  WITH T1 AS (
5
- SELECT 0
5
+ SELECT 0 UNION ALL SELECT 0
6
6
  ), T2 AS (
7
- SELECT * FROM T1 UNION ALL SELECT * FROM T1
8
- ), T3 AS (
9
- SELECT * FROM T2 S1 CROSS JOIN T2 S2
7
+ SELECT * FROM T1 S1 CROSS JOIN T1 S2
10
8
  ), R AS (
11
- SELECT * FROM T3 S1 CROSS JOIN T3 S2
9
+ SELECT * FROM T2 S1 CROSS JOIN T2 S2
12
10
  )
13
11
 
14
12
  SELECT ABS(RANDOM()) % 10
15
- FROM R AS R1 CROSS JOIN
13
+ FROM R AS R1
16
- R AS R2 CROSS JOIN
14
+ CROSS JOIN R AS R2
17
- R AS R3
15
+ CROSS JOIN R AS R3
18
16
  LIMIT 3000;
19
-
20
17
  ```

2

変更

2019/09/10 14:14

投稿

退会済みユーザー
answer CHANGED
@@ -2,23 +2,19 @@
2
2
  SQLなら似たようなものが作成できると思います。
3
3
  ```SQL
4
4
  WITH T1 AS (
5
- SELECT 0 , 1, 2, 3, 4, 5, 6, 7, 8, 9
5
+ SELECT 0
6
6
  ), T2 AS (
7
- SELECT * FROM T1
8
- UNION ALL SELECT * FROM T1
7
+ SELECT * FROM T1 UNION ALL SELECT * FROM T1
9
- UNION ALL SELECT * FROM T1
10
- UNION ALL SELECT * FROM T1
11
8
  ), T3 AS (
12
9
  SELECT * FROM T2 S1 CROSS JOIN T2 S2
13
10
  ), R AS (
14
11
  SELECT * FROM T3 S1 CROSS JOIN T3 S2
15
12
  )
16
13
 
17
- SELECT
18
- COUNT(1) --16777216
14
+ SELECT ABS(RANDOM()) % 10
19
- FROM
20
- R AS R1 CROSS JOIN R AS R2 CROSS JOIN R AS R3
15
+ FROM R AS R1 CROSS JOIN
16
+ R AS R2 CROSS JOIN
17
+ R AS R3
18
+ LIMIT 3000;
21
19
 
22
-
23
-
24
20
  ```

1

変更

2019/09/10 14:02

投稿

退会済みユーザー
answer CHANGED
@@ -4,20 +4,21 @@
4
4
  WITH T1 AS (
5
5
  SELECT 0 , 1, 2, 3, 4, 5, 6, 7, 8, 9
6
6
  ), T2 AS (
7
+ SELECT * FROM T1
7
- SELECT * FROM T1 UNION ALL SELECT * FROM T1
8
+ UNION ALL SELECT * FROM T1
9
+ UNION ALL SELECT * FROM T1
10
+ UNION ALL SELECT * FROM T1
8
11
  ), T3 AS (
9
- SELECT * FROM T2 UNION ALL SELECT * FROM T2
12
+ SELECT * FROM T2 S1 CROSS JOIN T2 S2
10
13
  ), R AS (
11
- SELECT * FROM T3
14
+ SELECT * FROM T3 S1 CROSS JOIN T3 S2
12
- UNION ALL SELECT * FROM T3
13
- UNION ALL SELECT * FROM T3
14
- UNION ALL SELECT * FROM T3
15
- UNION ALL SELECT * FROM T3
16
15
  )
17
16
 
18
17
  SELECT
19
- COUNT(1) --160000
18
+ COUNT(1) --16777216
20
19
  FROM
21
- R AS R1 CROSS JOIN R AS R2 CROSS JOIN R R3 CROSS JOIN R R4
20
+ R AS R1 CROSS JOIN R AS R2 CROSS JOIN R AS R3
22
21
 
22
+
23
+
23
24
  ```