MySQL合并为某些null，而不是所有null

I have a MySQL query that checks an employee schedule DB and (thanks to a previous * question) returns any days that the user is not working as "No Work", so that if the user isn't working on Thursday, the query fills in that date even though the date does not appear in the original result set (and so I don't have to fill in those blanks via the PHP script).

我有一个MySQL查询,检查员工日程安排数据库和(由于以前的*问题)返回用户没有工作的任何天“不工作”,所以如果用户不在周四工作,查询填写在那个日期,即使日期没有出现在原始结果集中(因此我不必通过PHP脚本填写这些空白)。

Now the problem is that if the user is checking the schedule two weeks from now, it shows "No Work" for the entire week. If there are NO results, the script should output "Sorry, No Schedule posted for this week", so the user doesn't get mislead into thinking he has the week off and knows to check back later.

现在的问题是,如果用户在两周后检查计划,则整周显示“无工作”。如果没有结果,脚本应该输出“抱歉,本周没有发布时间表”,这样用户就不会误以为他有休息一周并知道稍后再回来查看。

So the question is: What is the best query syntax for something like:

所以问题是:什么是最好的查询语法,如:

SELECT 
If Count(*) > 0 {
COALESCE(gooddata, nulldata) AS thedata
etc.
etc.
FROM sched_table
WHERE dates BETWEEN xxx AND yyy

So that it doesn't return any rows if all rows are null (or count is 0).

因此,如果所有行都为null(或count为0),它不会返回任何行。

I tried adding a column for a count and having the script output "not posted" and quitting after one iteration, but then on good weeks it still quits after one iteration, so I'm getting frustrated.

我尝试为计数添加一个列并让脚本输出“未发布”并在一次迭代后退出,但是在好几周后它仍然会在一次迭代后退出,所以我感到很沮丧。

I'm sure I can get the script to do what I want if I just take a step back, but I'm very curious if there is a way to do it in the query so that there has to be at least one row of non-Null values for it to coalesce the other null rows.

我确信如果我退后一步,我可以让脚本做我想做的事,但我很好奇是否有办法在查询中执行它以便必须至少有一行非空值,以便合并其他空行。

3 个解决方案

#1

Oh nooo, a join with two inner selects, one of them EXISTS conditional? Are you serious? How about using a simple UNION to fill in the blanks?? :) Something like

哦,不,有两个内部选择的连接,其中一个EXISTS有条件?你是认真的吗?如何使用简单的UNION来填空? :) 就像是

SELECT real_week_data UNION SELECT fake_empty_week;

This UNION would even unique the results for you, givin you exactly what you want..

这个UNION甚至会为你带来独特的结果,为你提供你想要的东西。

#2

I'm actually of the opinion that you can go too far with using the database to do row-level formatting/conditional output operations. PHP is actually a lot faster at doing string comparisons and arithmetic than MySQL. The unwritten policy among developers I've worked with has always been that we don't use the database to format our output and I think you're getting into that territory with what you're doing here.

我实际上认为使用数据库进行行级格式化/条件输出操作可能会走得太远。 PHP实际上比MySQL更快地进行字符串比较和算术运算。我与之合作的开发人员之间的不成文政策一直是我们不使用数据库来格式化我们的输出,我认为你正在进入这个领域,你正在这里做什么。

I'd be interested to know if others agree with this suggestion. I may actually post it as a new question... where does one draw the line in their use of the database to format output?

我很想知道其他人是否同意这个建议。我实际上可能会将其作为一个新问题发布...在使用数据库格式化输出时,哪一行绘制线?

#3

Most probably your query joins with a some kind of rowset to generate all days of week.

很可能你的查询加入某种行集来生成一周中的所有日子。

Add a condition into that rowset:

在该行集中添加一个条件:

SELECT  COALESCE(gooddata, nulldata) AS thedata
FROM    (
        SELECT  *
        FROM    days_of_week
        WHERE   EXISTS
                (
                SELECT  NULL
                FROM    sched_data
                WHERE   dates BETWEEN xxx AND yyy
                )
        )
LEFT OUTER JOIN
        sched_data
WHERE   dates BETWEEN xxx AND yyy

#1