mysql - 如何在这种类型的条件下编写 SQL 查询

Question

我正在构建一个电子商务网站，并坚持在一个地方编写 SQL 查询来实现，因为我想实现一个函数，它将循环到两个地方，然后再次切换!有点难以解释请检查我的代码

  <?php
      $ret=mysql_query("select * from products where seller_type='best' and status = 1");
      while ($row=mysql_fetch_array($ret))
      {?>
      <div class="item">
        <div class="products best-product">
          <div class="product">
            <div class="product-micro">
              <div class="row product-micro-row">
                <div class="col col-xs-5">
                  <div class="product-image">
                    <div class="image"> <a href="product-details.php?pid=<?php echo htmlentities($row['id']);?>"> <img src="assets/images/products/p26.jpg" alt=""> </a> </div>
                    <!-- /.image -->

                  </div>
                  <!-- /.product-image -->
                </div>
                <!-- /.col -->
                <div class="col2 col-xs-7">
                  <div class="product-info">
                    <h3 class="name"><a href="product-details.php?pid=<?php echo htmlentities($row['id']);?>"><?php echo htmlentities($row['productName']);?></a></h3>
                    <div class="rating rateit-small"></div>
                    <div class="product-price"> <span class="price"> $<?php echo htmlentities($row['productPrice']);?> </span> </div>
                    <!-- /.product-price -->

                  </div>
                </div>
                <!-- /.col -->
              </div>
              <!-- /.product-micro-row -->
            </div>
            <!-- /.product-micro -->

          </div>
          <div class="product">
            <div class="product-micro">
              <div class="row product-micro-row">
                <div class="col col-xs-5">
                  <div class="product-image">
                    <div class="image"> <a href="product-details.php?pid=<?php echo htmlentities($row['id']);?>"> <img src="assets/images/products/p27.jpg" alt=""> </a> </div>
                    <!-- /.image -->

                  </div>
                  <!-- /.product-image -->
                </div>
                <!-- /.col -->
                <div class="col2 col-xs-7">
                  <div class="product-info">
                    <h3 class="name"><a href="product-details.php?pid=<?php echo htmlentities($row['id']);?>"> <?php echo htmlentities($row['productName']);?></a></h3>
                    <div class="rating rateit-small"></div>
                    <div class="product-price"> <span class="price"> $<?php echo htmlentities($row['productPrice']);?>	 </span> </div>
                    <!-- /.product-price -->

                  </div>
                </div>
                <!-- /.col -->
              </div>
              <!-- /.product-micro-row -->
            </div>
            <!-- /.product-micro -->

          </div>
        </div>
      </div>
      <?php } ?>

现在，如果我使用 LIMIT 2，然后在下一个查询中我将如何跳过我已经使用过的前 2 个!!提前致谢

Answer 1

"select * from products where seller_type='best' and status = 1 LIMIT 2 OFFSET 2"

Offset 就是您所需要的。它告诉 mysql 查询应该从什么偏移量开始。

这是动态查找正确偏移量的规则:

(offset * limit ) - limit

示例您想要从第一行开始的 2 行

(1 * 2 ) - 2 = 0

所以你的Offset是0并且查询是Like

"SELECT * FROM products WHERE seller_type='best' and status = 1 LIMIT 2 OFFSET 0"

第一次你想从第三行开始获取两行:

(2 * 2 ) - 2 = 2

所以这次你的Offset是2，查询是Like

"SELECT * FROM products WHERE seller_type='best' and status = 1 LIMIT 2 OFFSET 2"

实现就像在 SQL 查询末尾添加 OFFSET 和 LIMIT 一样简单