如何执行 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 

EnrolledID,Date,COUNT(Time),

CASE 

    WHEN COUNT(Time) = 1 THEN 

        CASE 

            WHEN Time > '1899-12-30 03:00:00' 

                && Time <= '1899-12-30 12:15:00' 

                THEN Time END

    ELSE MIN(Time) 

END as time_in,

CASE 

    WHEN COUNT(Time) = 1 THEN

        CASE 

            WHEN Time > '1899-12-30 12:15:00' 

                && Time <= '1899-12-30 23:59:59' 

                THEN Time END

    ELSE NULLIF(MAX(Time), MIN(Time)) 

END as time_out

from attendance

GROUP BY EnrolledID,Date;

Answer 2

您可以使用UNION以下方法组合您的查询：

 $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

          UNION

          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);