返回IN值的默认结果，无论 [英] Return default result for IN value regardless

问题描述

我有一个查询，可以查询在某个日期范围内所有在线用户.由于我要如何显示数据，这是通过IN查询完成的.但是，如果没有找到解析为IN条件的ID的记录，我想返回一个默认值.

I have a query that gets all user that have been online between a certain date range. This is done via an IN query due to how I want to display the data. However, I would like to return a default value if no records were found for an ID that was parsed into the IN condition.

简化查询:

SELECT users.Name, users.ID, SUM(users.Minutes) AS MinutesOnline
     FROM UserTable
     LEFT JOIN OnlineUseage ON OnlineUseage.ID = UserTable.ID
     WHERE OnlineUseage.Date >= '2016-01-01 00:00:00' AND OnlineUseage.Date <= '2016-12-31 23:59:59'
     AND UserTable.ID IN(332,554,5764,11,556,.........)
     GROUP BY users.ID
     ORDER BY FIELD(UserTable.ID, 332,554,5764,11,556,.........)

现在，上面的查询将仅按预期拉入那些符合条件的行.我还希望查询为不符合条件的IN条件中的ID提取默认值.

Now the above query will only pull in those row that meet the condition, as expected. I would also like the query to pull in a default value for the ID's within the IN condition that don't meet the condition.

在这种情况下使用IFNULL将不起作用，因为永远不会返回记录

Using IFNULL in this instance will not work as the record is never returned

SELECT users.Name, users.ID, IFNULL(SUM(users.Minutes), 0) AS MinutesOnline
     FROM UserTable
     LEFT JOIN OnlineUseage ON OnlineUseage.ID = UserTable.ID
     WHERE OnlineUseage.Date >= '2016-01-01 00:00:00' AND OnlineUseage.Date <= '2016-12-31 23:59:59'
     AND UserTable.ID IN(332,554,5764,11,556,.........)
     GROUP BY users.ID
     ORDER BY FIELD(UserTable.ID, 332,554,5764,11,556,.........)

仅供参考-我正在将此查询解析为自定义PDO函数.我没有使用过时的mysql函数

FYI - i'm parsing this query into a custom PDO function. I'm not using deprecated mysql functions

推荐答案

您对OnlineUseage有一个条件，左联接变得像内部联接一样.

You have a condition on OnlineUseage the left join become like a inner join.

将您的条件移到from子句会更好:

move your condition to the from clause will be better :

SELECT
    users.Name,
    users.ID,
    IFNULL(SUM(users.Minutes), 0) AS MinutesOnline
FROM
    users
    LEFT JOIN OnlineUseage ON
        OnlineUseage.ID = users.ID and
        OnlineUseage.Date >= '2016-01-01 00:00:00' AND
        OnlineUseage.Date <= '2016-12-31 23:59:59'
WHERE
    users.ID IN (332,554,5764,11,556,.........)
GROUP BY
    users.ID,users.Name
ORDER BY
    users.ID

这篇关于返回IN值的默认结果，无论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！