如何在多个SQL查询之间保留变量（不嵌套查询）？ [英] How can I retain variables between multiple SQL queries (without nesting the queries)?

问题描述

我想知道在多个查询之间保留变量的最佳方法是什么，并在最后一个查询中回显它们。我目前的逻辑是嵌套查询，如下面的例子。



我听说嵌套会减慢程序的速度，而且这种做法不必要地冗长，而且一般情况下做的不好。我听说过加入和左连接，但我不知道如何在一个例子中应用它或如何将类似下面的代码转换为join语句。

任何人都可以告诉我如何使用最佳实践重新构建我的代码？



我尝试过：

  / *  第一个SQL查询：* /  
  $ sql1  =   SELECT * FROM （select * from my_table WHERE id< $ id ORDER BY id DESC LIMIT 1）AS x ORDER BY id LIMIT 1; 
  $ result1  = mysqli_query（$ conn，$ sql1）; 
  $ resultCheck1  = mysqli_num_rows（$ result1）; 
  if （$ resultCheck1> 0）{
  while （$ row1 = mysqli_fetch_assoc（$ result1））{
  $ sum1  = $ row1 [' 总和']; 
  / *  第二个SQL查询：* /  
  $ sql2  =   SELECT * FROM（select * from my_table WHERE id = $ id ORDER BY id DESC LIMIT 1）AS x ORDER BY id LIMIT 1; 
  $ result2  = mysqli_query（$ conn，$ sql2）; 
  $ resultCheck2  = mysqli_num_rows（$ result2）; 
  if （$ resultCheck2> 0）{
  while （$ row2 = mysqli_fetch_assoc（$ result2））{
  $ sum2  = $ row2 [' 总和']; 
  / *  第三个SQL查询：* /  
  $ sql3  =   SELECT * FROM new_table;  / *  不同的表* /  
  $ result3  = mysqli_query（$ conn，$ sql3）; 
  $ resultCheck3  = mysqli_num_rows（$ result3）; 
  if （$ resultCheck3> 0）{
  while （$ row3 = mysqli_fetch_assoc（$ result3））{
  $ sum3  = $ row3 [' 总和']; 
  echo   $ sum1  +  $ sum2  +  $ sum3 ; 
} 
} 
} 
} 
} 
} 

解决方案

sql1 = SELECT * FROM（select * from my_table WHERE id<

id ORDER BY id DESC LIMIT 1）AS x ORDER BY id LIMIT 1;

result1 = mysqli_query（

I'd like to know what is the best approach to retain variables between multiple queries and echo them out in the last query. My current logic is to nest the queries, like the example below.

I heard that nesting slows down a program, and that it's unnecessarily verbose, and bad practise in general. I heard about "join" and "left join" but I've no idea how to apply it in an example or how to convert something like the following code into a "join" statement.
Can anybody tell me how to re-construct my code using best practises?

What I have tried:

/*First SQL query:*/
	$sql1 = "SELECT * FROM (select * from my_table WHERE id < $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
	$result1 = mysqli_query($conn, $sql1);
	$resultCheck1 = mysqli_num_rows($result1);
	if ($resultCheck1 > 0) {
		while ($row1 = mysqli_fetch_assoc($result1)) {
			$sum1 = $row1['sum'];
			/*Second SQL query:*/
			$sql2 = "SELECT * FROM (select * from my_table WHERE id = $id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";
			$result2 = mysqli_query($conn, $sql2);
			$resultCheck2 = mysqli_num_rows($result2);
			if ($resultCheck2 > 0) {
				while ($row2 = mysqli_fetch_assoc($result2)) {
					$sum2 = $row2['sum'];
					/*Third SQL query:*/
					$sql3 = "SELECT * FROM new_table"; /*Different table*/
					$result3 = mysqli_query($conn, $sql3);
					$resultCheck3 = mysqli_num_rows($result3);
					if ($resultCheck3 > 0) {
						while ($row3 = mysqli_fetch_assoc($result3)) {
							$sum3 = $row3['sum'];
							echo $sum1 + $sum2 + $sum3;
						}
					}
				}
			}
		}
	}

解决方案

sql1 = "SELECT * FROM (select * from my_table WHERE id <

id ORDER BY id DESC LIMIT 1) AS x ORDER BY id LIMIT 1";

result1 = mysqli_query(

这篇关于如何在多个SQL查询之间保留变量（不嵌套查询）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！