亚洲国产日韩欧美一区二区三区,精品亚洲国产成人av在线,国产99视频精品免视看7,99国产精品久久久久久久成人热,欧美日韩亚洲国产综合乱

<p>I need a simple SQL query to display a virtual/temporary table without creating it in the database. </p> <p>I'm using PHP to create a query string with data. </p> <p>My current PHP code is: </p> <pre class="brush:php;toolbar:false;">$array = [ ['id' => 1, 'name' => 'one'], ['id' => 2, 'name' => 'two'], ['id' => 3, 'name' => 'three'] ]; $subQuery = "SELECT {$array[0]['id']} AS col1, '{$array[0]['name']}' AS col2"; for ($i=1; $i < count($array); $i ) { $subQuery .= " UNION ALL SELECT {$array[$i]['id']}, '{$array[$i]['name']}'"; } $sql = "WITH cte AS ( {$subQuery} ) SELECT col1, col2 FROM cte;"; echo $sql;</pre> <p>The output is: </p> <pre class="brush:php;toolbar:false;">WITH cte AS ( SELECT 1 AS col1, 'one' AS col2 UNION ALL SELECT 2, 'two' UNION ALL SELECT 3, 'three' ) SELECT col1, col2 FROM cte; // Output table from the SQL col1 col2 1 one 2 two 3 three</pre> <p>I got the idea for this query from here. </p> <p>But the problem with this query is that if there are 100 data in $array, the <strong>UNION ALL</strong> part will be included 100 times in the SQL. I don't think this is a better SQL because it's like joining 100 tables at the same time. </p> <p>I could also create a temporary table (<strong>CREATE TEMPORARY TABLE table_name</strong>) in place of this <strong>WITH</strong> clause, but that would not be a single query because I Another query is needed to insert records into the temporary table. </p> <p>Can anyone help me simplify this query in a better way? </p>

<?php
$array = [
    ['id' => 1, 'name' => 'one'],
    ['id' => 2, 'name' => 'two'],
    ['id' => 3, 'name' => 'three']
];

$data = json_encode($array);

$sql = "SELECT tbl.* 
FROM JSON_TABLE(
        '{\"data\":$data}',
        '$.data[*]' COLUMNS (
                id VARCHAR(40)  PATH '$.id',
                name VARCHAR(100) PATH '$.name')
     ) tbl";

echo $sql;

+====+=======+
| id | name  |
+====+=======+
| 1  | one   |
+----+-------+
| 2  | two   |
+----+-------+
| 3  | three |
+----+-------+