PHP MYSQL 5表连接问题

Question

尝试根据匹配的列信息从另一个表中减去剩余数量

t1 (id,complete)

t2 (id,t1_id,qty,size,desc)

t3 (id,t1_id,qty,size,desc)

t4 (id,size)

t5 (id,desc)

t1包含主“作业”，以及它是否完成

t2包含“中的项目”

t3包含“传出的项目”

t4包含“项目的大小”

t5包含“项目描述”

示例数据：

t1 (id=300,complete=0)

t1 (id=350,complete=1)

t2 (id=1,t1_id=300,qty=20,size=1,desc=3)

t2 (id=2,t1_id=300,qty=10,size=2,desc=1)

t2 (id=3,t1_id=350,qty=10,size=2,desc=1)

t3 (id=1,t1_id=300,qty=7,size=1,desc=3)

t3 (id=2,t1_id=300,qty=9,size=2,desc=1)

t4 (id=1,size="3.5 inch")

t4 (id=2,size="4.5 inch")

t5 (id=1,desc="Drill")

t5 (id=3,desc="Flow")

预期输出：

QTY: 13 3.5 inch Flow Remaining

QTY: 1 4.5 inch Drill Remaining

注意，t1 id 350是完整的，这就是它没有显示的原因。

$sql = "
SELECT
t1.id AS main_id,
t2.t1_id,
t2.qty,
t2.size,
t2.desc,
t3.t1_id,
t3.qty,
t3.size,
t3.desc,
t4.size,
t5.desc
SUM(t2.qty-t3.qty) AS remaining,
JOIN t2 ON t1.id=t2.t1_id
JOIN t3 ON t1.id=t3.t1_id
JOIN t4 ON t2.size=t4.id
JOIN t5 ON t2.desc=t5.id
JOIN t4 ON t3.size=t4.id
JOIN t5 ON t3.desc=t5.id            
WHERE           
(t1.complete != 1) AND (t2.size = t3.size) AND (t2.desc=t3.desc)        
ORDER BY t4.size ASC, t5.desc ASC ";
$result = $conn->query($sql);
if ($result->num_rows > 0){
while($row = $result->fetch_assoc()){
echo "$row['remaining']";
}
}

Answer 1

因此，我更改了您的sql语句，因为您发布的内容无法工作。

CREATE TABLE t1 (
  id INT,
  complete int
 );
 INSERT INTO t1 (id,complete) VALUES 
   (300,0),(350,1);

CREATE TABLE t2 (
  id INT,
  t1_id int
  ,qty int
  ,size int
  ,desc1 int
);
INSERT INTO t2 (id,t1_id,qty,size,desc1) VALUES 
  (1,300,20,1,3),
  (2,300,10,2,1),
  (3,350,10,2,1);

CREATE TABLE t3 (
  id INT,
  t1_id int
  ,qty int
  ,size int
  ,desc1 int
);
INSERT INTO t3 (id,t1_id,qty,size,desc1) VALUES 
  (1,300,7,1,3),
  (2,300,9,2,1);

CREATE TABLE t4 (
  id INT,
  size varchar(100)
);
INSERT INTO t4 (id,size) VALUES 
  (1,"3.5 inch"),
  (2,"4.5 inch");

CREATE TABLE t5 (
  id INT,
  desc1 varchar(100)
);
INSERT INTO t5 (id,desc1) VALUES 
 (1,"Drill"),
 (3,"Flow")

新的Sql语句是

SELECT
  t1.id AS main_id,
  t2.t1_id,
  t2.qty,
  t2.size,
  t2.desc1
  ,t3.t1_id,
  t3.qty,
  t3.size,
  t3.desc1
 ,t4.size
 ,t5.desc1
 ,t2.qty-t3.qty AS remaining
FROM t1
  right JOIN t2 ON t1.id=t2.t1_id
  JOIN t3 ON t1.id=t3.t1_id and t2.size = t3.size
  JOIN t4 ON t2.size=t4.id
  JOIN t5 ON t2.desc1=t5.id     
WHERE           
  (t1.complete != 1)       
  ORDER BY t4.size ASC, t5.desc1 ASC; 

结果就是

main_id     t1_id   qty     size    desc1   t1_id   qty     size    desc1   size    desc1 remaining
 300        300     20       1       3       300    7       1        3      3.5 inch Flow   13
 300        300     10       2       1       300    9       2        1      4.5 inch Drill  1