php - “NOT NULL”、“IS NOT NULL”不显示在表中

问题描述

问题
Null 列和 Not null 列不显示在表中

说明
*我想显示来自不同数据库表的所有结果，从表 1 到下面的表 4，但它只显示那些已经有 4 个结果数据的人。我怎样才能做到这一点？对不起，我是数据库领域的新手。希望大家能给我一些参考或相关*

我的查询

<?php
                   $sql = "SELECT virtualexam.rank.id, virtualexam.rank.username,

                   virtualexam.rank.score AS score, virtualexam1.rank.score AS 'score1', virtualexam2.rank.score AS 'score2', virtualexam3.rank.score AS 'score3',

                   SUM(virtualexam.rank.score + virtualexam1.rank.score + virtualexam2.rank.score + virtualexam3.rank.score ) AS 'total' 
                   
                   FROM virtualexam.rank
               
                   JOIN virtualexam1.rank ON (virtualexam.rank.username =  virtualexam1.rank.username) 
               
                   JOIN virtualexam2.rank ON (virtualexam2.rank.username =  virtualexam1.rank.username)
               
                  JOIN virtualexam3.rank ON (virtualexam3.rank.username =  virtualexam2.rank.username) 

                  WHERE virtualexam.rank.score IS NULL OR virtualexam.rank.score = ''
                 
                  GROUP BY virtualexam.rank.username ; ";


                  $result = $conn->query($sql);

标签： phpmysqldatabase

当您说 justJOIN时，它需要一条记录存在于连接表中。如果您希望它是可选的，请LEFT JOIN改用。然后，如果连接的表不存在，对其中列的引用将给出 NULL。为了防止virtualexam.rank.score + virtualexam1.rank.score + virtualexam2.rank.score + virtualexam3.rank.score在添加的任何内容为 NULL 时返回 NULL，您需要使用 coalesce。所以：

SELECT
    virtualexam.rank.id,
    virtualexam.rank.username,
    virtualexam.rank.score AS score,
    virtualexam1.rank.score AS 'score1',
    virtualexam2.rank.score AS 'score2',
    virtualexam3.rank.score AS 'score3',
    SUM(COALESCE(virtualexam.rank.score,0) + COALESCE(virtualexam1.rank.score,0) + COALESCE(virtualexam2.rank.score,0) + COALESCE(virtualexam3.rank.score) ) AS 'total' 
FROM virtualexam.rank
LEFT JOIN virtualexam1.rank ON (virtualexam.rank.username = virtualexam1.rank.username) 
LEFT JOIN virtualexam2.rank ON (virtualexam2.rank.username = virtualexam1.rank.username)
LEFT JOIN virtualexam3.rank ON (virtualexam3.rank.username = virtualexam2.rank.username) 
WHERE virtualexam.rank.score IS NULL OR virtualexam.rank.score = ''                 
GROUP BY virtualexam.rank.username

这仍然只报告具有 virtualexam.rank 条目的用户。如果你想包括那些没有但在任何其他数据库中有条目的人，你必须这样做：

FROM (
    SELECT DISTINCT username FROM virtualexam.rank
    UNION SELECT DISTINCT username FROM virtualexam1.rank
    UNION SELECT DISTINCT username FROM virtualexam2.rank
    UNION SELECT DISTINCT username FROM virtualexam3.rank
) AS users
LEFT JOIN virtualexam.rank ON virtualexam.rank.username=users.username
LEFT JOIN virtualexam1.rank ON virtualexam1.rank.username=users.username
LEFT JOIN virtualexam2.rank ON virtualexam2.rank.username=users.username
LEFT JOIN virtualexam3.rank ON virtualexam3.rank.username=users.username

但这是一个非常糟糕的数据库设计。将所有分数放在一个表格中，并使用其他字段来确定分数的用途。

php - “NOT NULL”、“IS NOT NULL”不显示在表中

问题描述

解决方案

推荐阅读