問題描述

我有一個查詢，可以獲取在某個日期范圍內在線的所有用戶.由于我想如何顯示數據，這是通過 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 值的默認結果，不管的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！