返回 IN 值的默认结果，不管

2021-11-20 00:00:00 sql mysql

我有一个查询，可以获取在某个日期范围内在线的所有用户.由于我想如何显示数据，这是通过 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

相关文章