使用JOIN查询检索记录 [英] Retrieve records using JOIN query

问题描述

我想每天从emp_info表中检索过去1周的数据.

I want to retrieve data of last 1 week from emp_info table on per day basis.

所以我用过:

SELECT DAYNAME(timestamp), COUNT(*) 
FROM `emp_info` 
WHERE DATE(timestamp ) > DATE_SUB(CURDATE( ) , INTERVAL 1 WEEK ) 
GROUP BY DAYNAME(timestamp);

根据查询，我得到的结果如下:

According to the query I am getting result like:

Monday    5
Thursday  7

但是我也希望工作日的结果为 0 ，但未输入任何记录.

But I also want the result of weekday as 0 on which no record has been entered.

根据我的建议，我了解了有关 JOIN 查询的信息.所以我试图修复它，但没有任何解决方案.

From suggestions I come to know about JOIN query. So I have tried to fix it but not getting any solution.

推荐答案

您得到的结果是正确的，因为在特定的Dayname上没有记录.由于要获取所有日名，因此需要投影完整的日期集(在子查询中使用UNION )，并将其与现有查询结合起来.

The result you are getting is right because there are no records on a specific dayname. Since you want to get all daynames, you need to project complete set of day (using UNION inside a SUBQUERY) and join it with your existing query.

SELECT  a.day_name,
        COALESCE(b.totalCount, 0) totalCount
FROM
        (
            SELECT 'Sunday' day_name, 1 ordby UNION ALL
            SELECT 'Monday' day_name, 2 ordby UNION ALL
            SELECT 'Tuesday' day_name, 3 ordby UNION ALL
            SELECT 'Wednesday' day_name, 4 ordby UNION ALL
            SELECT 'Thursday' day_name, 5 ordby UNION ALL
            SELECT 'Friday' day_name, 6 ordby UNION ALL
            SELECT 'Saturday' day_name, 7 ordby 
        ) a
        LEFT JOIN
        (
            SELECT  DAYNAME(timestamp) day_name, 
                    COUNT(*) totalCount
            FROM    `emp_info` 
            WHERE   DATE(timestamp ) > DATE_SUB(CURDATE( ) , INTERVAL 1 WEEK ) 
            GROUP   BY DAYNAME(timestamp)
        ) b ON a.day_name = b.day_name
ORDER   BY a.ordby

• SQLFiddle演示(简单示例)

这篇关于使用JOIN查询检索记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！