如何执行 2 个条件查询并将数据检索到单个表中

Question

实际上，我尝试在不同的条件下运行 2 个查询，并尝试使用以下代码在单个表中获取两个查询结果

$query01 = "SELECT COUNT(Time) as count

          FROM attendance

          GROUP BY EnrolledID, Date";

$result_set01 = mysqli_query($conn, $query01);

while($row = mysqli_fetch_assoc($result_set01)){

    $count = $row["count"];

   if ($count == 1) 

   {

     $query = "SELECT EnrolledID,

          Date,

          COUNT(Time),

          CASE WHEN Time > '1899-12-30 03:00:00' && Time <= '1899-12-30 12:15:00' THEN Time

          END AS time_in,

          CASE WHEN Time > '1899-12-30 12:15:00' && Time <= '1899-12-30 23:59:59' THEN Time 

          END AS time_out

          from attendance

          GROUP BY EnrolledID,Date

          HAVING COUNT(Time) = 1";

          $result_set = mysqli_query($conn, $query);

    }

    else

    {

      $query = "SELECT EnrolledID, Date,

          COUNT(Time),

          MIN(Time) as time_in,

          NULLIF(MAX(Time), MIN(Time)) as time_out

          FROM attendance

          GROUP BY EnrolledID, Date

          HAVING COUNT(Time) != 1";

          $result_set = mysqli_query($conn, $query);

    }

}

if (mysqli_num_rows($result_set) > 0) 

{ 

      $table ='<table class="table table-dark" id="table">';

      $table .='<tr><th>EnrolledID</th><th>Date</th><th>Time IN</th><th>Time Out</th><th>Total hours</th></tr>';

  // output data of each row

  while($row = mysqli_fetch_assoc($result_set)) 

  {

    $EnrolledID = sprintf('%04d',$row["EnrolledID"]);

    $Date = date_format( date_create($row['Date']), 'Y-m-d D' );

    if(isset($row['time_in']) == 'NULL')

    {

          $time_in = date_format( date_create($row['time_in']), 'H:i:s' );

    }

    else{

          $time_in = '';

    }

    if(isset($row['time_out']) == 'NULL')

    {

          $time_out = date_format( date_create($row['time_out']), 'H:i:s' );

    }

    else{

         $time_out = ''; 

    }

使用此代码，我尝试在单个表中检索数据。两个查询都单独运行，但我不能同时运行。

Answer 1

你能检查一下这个单一的查询是否适合你吗？如果它给出了你想要的输出，你不需要使用单独的查询来检查 row_count，即使是单个查询也会像你现在对多个查询所做的一样。SELECT&nbsp;EnrolledID,Date,COUNT(Time),CASE&nbsp;&nbsp; &nbsp; WHEN COUNT(Time) = 1 THEN&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN Time > '1899-12-30 03:00:00'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; && Time <= '1899-12-30 12:15:00'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN Time END&nbsp; &nbsp; ELSE MIN(Time)&nbsp;END as time_in,CASE&nbsp;&nbsp; &nbsp; WHEN COUNT(Time) = 1 THEN&nbsp; &nbsp; &nbsp; &nbsp; CASE&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN Time > '1899-12-30 12:15:00'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; && Time <= '1899-12-30 23:59:59'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN Time END&nbsp; &nbsp; ELSE NULLIF(MAX(Time), MIN(Time))&nbsp;END as time_outfrom attendanceGROUP BY EnrolledID,Date;

Answer 2

您可以使用UNION以下方法组合您的查询：&nbsp;$query = "SELECT EnrolledID,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT(Time),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN Time > '1899-12-30 03:00:00' && Time <= '1899-12-30 12:15:00' THEN Time&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END AS time_in,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN Time > '1899-12-30 12:15:00' && Time <= '1899-12-30 23:59:59' THEN Time&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; END AS time_out&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from attendance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GROUP BY EnrolledID,Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HAVING COUNT(Time) = 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT EnrolledID, Date,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNT(Time),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; MIN(Time) as time_in,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NULLIF(MAX(Time), MIN(Time)) as time_out&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM attendance&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GROUP BY EnrolledID, Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; HAVING COUNT(Time) != 1";&nbsp; &nbsp; &nbsp; $result_set = mysqli_query($conn, $query);