查看MySQL的每日数据

Question

我正在做一个关于办公室员工日常评估测试的简单应用程序。

该应用程序将几个日常问题的得分结果存储在数据库中，每天输入日常问题。

我的MySQL数据库中有2个表，其中有：

1. account_table
2. 评估表

这是account_table:

accountID | accountName | accountEmail  
1 | John        | [e-mail] 
2 | Derick      | [e-mail]
3 | Ratna       | [e-mail] 

这是assessment_table：(例如，今天是2020-10-08(

assessmentID  |  accountID    |  assessmentScore | assessmentDate
1            |   1           |  50              | 2020-10-07
2            |   2           |  75              | 2020-10-07
3            |   3           |  93              | 2020-10-07
4            |   2           |  47              | 2020-10-08
5            |   3           |  80              | 2020-10-08

如何查看今天未进行评估的帐户
谢谢你的帮助，问候。

Answer 1

SELECT act.*
FROM account_table act
LEFT JOIN assessment_table ast ON act.accountID = ast.accountID 
AND ast.assessmentDate = '2020-10-08'
WHERE ast.accountID IS NULL

或

SELECT *
FROM account_table act
WHERE NOT EXISTS ( SELECT NULL 
FROM assessment_table ast 
WHERE act.accountID = ast.accountID 
AND ast.assessmentDate = '2020-10-08' )

您可以使用CURRENT_DATE而不是'2020-10-08'文字来接收当天的输出。

PS。我没有显示WHERE NOT IN变体——它在几乎所有情况下都较慢。

Answer 2

SELECT * FROM account_table WHERE account_table.accountID NOT IN (
SELECT accounts.accountID
FROM assessment_table assessments
INNER JOIN account_table accounts ON assessments.accountID = accounts.accountID
WHERE assessments.assessmentDate = "2020-10-08"
)

Answer 3

这个sql语句应该能在中工作

SELECT account_table.accountID 
FROM account_table 
WHERE account_table.accountID NOT IN
(SELECT assessment_table.accountID 
FROM assessment_table 
WHERE CAST(assessmentDate AS DATE) = CAST("2020-10-08" AS DATE))

"2020-10-08"；你可以使用像NOW((这样的函数来获取今天的所有条目

Answer 4

仅

SELECT ac_t.* FROM `account_table` ac_t
INNER JOIN `assessment_table` as_t
ON ac_t.accountID = as_t.accountID
WHERE as_t.accountID NOT IN (
SELECT accountID FROM assessment_table WHERE `assessmentDate` = '2020-10-08'
)

如果你今天不想硬编码

SELECT ac_t.* FROM `account_table` ac_t
INNER JOIN `assessment_table` as_t
ON ac_t.accountID = as_t.accountID
WHERE as_t.accountID NOT IN (
SELECT accountID FROM assessment_table WHERE `assessmentDate` = CURDATE()
)

Answer 5

我认为最简单的方法是这样的：

SELECT * FROM account_table 
WHERE accountID NOT IN (SELECT accountID FROM assessment_table WHERE assessmentDate = DATE(NOW()));

Answer 6

通过使用以下命令，如果评估表中存在的某些在科目表中没有条目的额外条目将被拒绝。

SELECT * FROM account_table 
WHERE accountID NOT IN 
(SELECT assessment_table.accountID FROM assessment_table 
RIGHT JOIN account_table on account_table.accountID = assessment_table.accountID 
WHERE assessment_table.assessmentDate = DATE(NOW()))

Answer 7

hi请使用此sql语句

$today = date("d");
$month = date('m');
$year  = date('Y');
$query = "SELECT * FROM account_table as account inner join assessment_table as on account.accountID=assesment.accountId  where DAY(assesment.assessmentDate) = $today AND MONTH(assesment.assessmentDate) = $month AND YEAR(assesment.assessmentDate) = $year";