php - 选择SQL查询从多个表中获取数据并在一张表中查看

Question

出现的问题是，在雇主输入结帐时间数据之前，结帐时间自动循环。

输入入住时间后，结帐栏将为空，但它会自动生成结帐时间，并在接下来的几天里，它会从前几天提取数据并复制时间。我只是怀疑，会有一些错误在一个查询中调用 3 个表中的数据时: * tbl_学生 * 时间 * checkout

Table 1 ( time )

id       id_number1      date1        check_in             
-----------------------------------------------------
30        10001       2018-02-04       22:07:59

31        10002       2018-02-04       22:08:09

32        10001       2018-02-05      08:21:10 




Table 2 ( check_out)

id       id_number2      date2        check_out             
-----------------------------------------------------
10         10001      2018-02-04      22:09:09

11         10002      2018-02-04      22:09:21

12         10001      2018-02-05      09:21:22


Table 3 ( tbl_student )

id       id_number      Password       full_name             
-----------------------------------------------------
2          10001         10001           Alma

3          10002         10002          Daniel

out put

<?php
    include ("connection.php");
	$id = $_GET['employee-ID'];
	$query = "SELECT tbl_student.full_name,tbl_student.Id_Number, time.Id_Number1, time.date1 ,time.check_in, check_out.check_out  
	          FROM tbl_student, time, check_out 
	          WHERE check_out.Id_Number2 ='$id' AND time.Id_Number1 = '$id' AND  tbl_student.Id_Number =  '$id'";
	$result = mysqli_query ($link, $query) or die("select Error ".mysqli_error($link));
	$i = 1;
	while ($row = mysqli_fetch_array($result)) {
		
?>
<tbody>   
    
    <tr>
    <td><?php echo $i; ?></td>
    <td><?php echo $row['full_name']; ?></td>
    <td><?php echo $row['Id_Number']; ?></td>
    <td><?php echo $row['check_in'];  ?></td>
    <td><?php echo $row['check_out']; ?></td>
    </tr>
    <?php
	$i++;
	}
	mysqli_close  ($link);
?>
<p>
</p>

Error

<?php
    include ("connection.php");
	$id = $_GET['employee-ID'];
	$query = "SELECT tbl_student.full_name, tbl_student.Id_Number, time.Id_Number1, time.date1, time.check_in,check_out.check_out, time.time_rn, check_out.check_out_rn
	
            FROM tbl_student ,(SELECT time.*, row_number() OVER ( partition by time.id_number1 order by time.date1) time_rn FROM time WHERE 1=1 ) 
			
	        time, (SELECT check_out.*, row_number() OVER (partition by check_out.id_number2 order by check_out.date2) check_out_rn from check_out where 1=1) check_out 
   
            WHERE check_out.Id_Number2='$id' AND time.Id_Number1 ='$id' AND tbl_student.Id_Number = '$id' AND time_rn = check_out_rn (+)";
	 
	$result = mysqli_query ($link, $query) or die("select Error ".mysqli_error($link));
	while ($row = mysqli_fetch_array($result)) {
		
?>

Answer 1

好的，事情就是这样。如果您运行查询，您会发现它只是在 time 和 check_out< 之间执行交叉联接或笛卡尔联接/ 表。你几乎永远不会想要这个。它显示了 check_in 和 check_out 时间的所有可能组合，因为您还没有告诉它 check_in 和 check_out 时间应该如何关联。

select full_name, id_number, date1, check_in, check_out
from tbl_student, time, check_out
where id_number = 10001 and id_number1 = 10001 and id_number2 = 10001;

FULL_NAME  ID_NUMBER DATE1     CHECK_IN CHECK_OUT
--------- ---------- --------- -------- ---------
Alma           10001 04-FEB-18 22:07:59 22:09:09 
Alma           10001 04-FEB-18 22:07:59 09:21:22 
Alma           10001 05-FEB-18 08:21:10 22:09:09 
Alma           10001 05-FEB-18 08:21:10 09:21:22 

在本例中，我假设您希望它显示与该用户的 checkin 时间序列匹配的 checkout 时间。因此，用户的第三次 checkin 应该与他们的第三次 checkout 相匹配。为此，我将向每个表添加一个“按 id_number 分区的 row_number”，以便我们可以将它们匹配。

SELECT tbl_student.full_name, tbl_student.Id_Number, 
    time.Id_Number1, time.date1, time.check_in,
    check_out.check_out,
        time.time_rn, -- for testing
        check_out.check_out_rn -- for testing
  FROM tbl_student, 
    (select time.*,
        row_number() over (partition by time.id_number1 order by time.date1) time_rn
        from time where 1=1) time, 
    (select check_out.*,
        row_number() over (partition by check_out.id_number2 order by check_out.date2) check_out_rn
        from check_out where 1=1) check_out 
  WHERE check_out.Id_Number2=10001 AND time.Id_Number1 =10001 AND tbl_student.Id_Number = 10001
    and time_rn = check_out_rn (+)
  ;

输出:

FULL_NAME  ID_NUMBER ID_NUMBER1 DATE1     CHECK_IN CHECK_OUT    TIME_RN CHECK_OUT_RN
--------- ---------- ---------- --------- -------- --------- ---------- ------------
Alma           10001      10001 04-FEB-18 22:07:59 22:09:09           1            1
Alma           10001      10001 05-FEB-18 08:21:10 09:21:22           2            2

<小时/>

顺便说一句，这是一种尴尬的表结构。如果您可以更改架构，我建议删除 check_out 表并执行以下操作:

create table time (id number, 
                   id_number1 number, 
                   date1 date, 
                   check_in varchar2(8), 
                   check_out varchar2(8));

这样您就可以知道某人何时尚未 checkout ，因为 time.check_out 将为 null。

<小时/>

编辑:看来您使用的 MySQL 版本早于 8.0，它不支持窗口函数。试试这个吧。在某些方面，这是一个更简单的查询。

SELECT tbl_student.full_name, tbl_student.Id_Number, 
time.Id_Number1, time.date1, time.check_in,
(select min(check_out) 
  from check_out
  where Id_Number2=time.Id_Number1
    and check_out.date2 >= time.date1
    and check_out.check_out >= time.check_in) check_out
FROM tbl_student, time
WHERE time.Id_Number1 =10001 AND tbl_student.Id_Number = 10001;