I'm getting the first 50 records using this query. However, there is a flag named read in the same table, and I only want to return the first 50 records that are not read (i.e. isread=false). How can I achieve this? Can anybody give me ideas for the required query? I have tried using sub queries.
我将使用这个查询获得前50条记录。但是，在同一个表中有一个名为read的标志，我只想返回未读取的前50条记录(即isread=false)。我如何做到这一点?谁能给我一些关于查询的建议吗?我尝试过使用子查询。
SELECT * FROM notification WHERE toUserId = 'email' ORDER BY id DESC LIMIT 50;
3 个解决方案
#1
4
Try adding an AND condition to your WHERE clause: userID = 'email' AND flag = true. This will only return users with true value for flag of which you can get the top 50 by your limit condition.
尝试向WHERE子句:userID = 'email'和flag = true添加一个AND条件。这将只返回真正值为标志的用户，您可以根据您的限制条件获得前50名。
#2
1
Since you want to get the first 50 values regardless of the IsRead condition and then filter it, you can try this but I populated the query in Sql Server.
由于您希望获得前50个值，而不考虑IsRead条件，然后对其进行筛选，因此可以尝试这样做，但是我在Sql Server中填充了查询。
SELECT * FROM (SELECT TOP 50 * FROM notification) S WHERE toUserId = 'email' and isread=false
This should help. You can try the same technique in MySql.
这应该帮助。你可以在MySql中尝试同样的技术。
#3
0
SELECT * FROM notification WHERE toUserId = 'email' AND isread = 0 ORDER BY id DESC LIMIT 50;
should work, though it does depend on you using 0 for false and 1 (or anything else) for true. If you have chosen the tinyint or BOOL type then this will usually work (note tinyint can be -9 to +9 so make sure you set 0 or 1 as your flags.
应该是可以的，尽管它取决于你用0表示false，用1表示true(或其他任何东西)。如果您选择了tinyint或BOOL类型，那么这通常是可行的(注意tinyint可以是-9到+9，所以请确保将0或1设置为标志。
In my opinion, it is probably better to use the terms 1 and 0 rather than TRUE or FALSE (though this is a matter of aesthetics really) because the code is then more readily transferrable. It also prevents lazy coding.
在我看来，最好使用术语1和0，而不是真或假(尽管这确实是一个美学问题)，因为这样代码就更容易转移。它还可以防止延迟编码。
#1
4
Try adding an AND condition to your WHERE clause: userID = 'email' AND flag = true. This will only return users with true value for flag of which you can get the top 50 by your limit condition.
尝试向WHERE子句:userID = 'email'和flag = true添加一个AND条件。这将只返回真正值为标志的用户，您可以根据您的限制条件获得前50名。
#2
1
Since you want to get the first 50 values regardless of the IsRead condition and then filter it, you can try this but I populated the query in Sql Server.
由于您希望获得前50个值，而不考虑IsRead条件，然后对其进行筛选，因此可以尝试这样做，但是我在Sql Server中填充了查询。
SELECT * FROM (SELECT TOP 50 * FROM notification) S WHERE toUserId = 'email' and isread=false
This should help. You can try the same technique in MySql.
这应该帮助。你可以在MySql中尝试同样的技术。
#3
0
SELECT * FROM notification WHERE toUserId = 'email' AND isread = 0 ORDER BY id DESC LIMIT 50;
should work, though it does depend on you using 0 for false and 1 (or anything else) for true. If you have chosen the tinyint or BOOL type then this will usually work (note tinyint can be -9 to +9 so make sure you set 0 or 1 as your flags.
应该是可以的，尽管它取决于你用0表示false，用1表示true(或其他任何东西)。如果您选择了tinyint或BOOL类型，那么这通常是可行的(注意tinyint可以是-9到+9，所以请确保将0或1设置为标志。
In my opinion, it is probably better to use the terms 1 and 0 rather than TRUE or FALSE (though this is a matter of aesthetics really) because the code is then more readily transferrable. It also prevents lazy coding.
在我看来，最好使用术语1和0，而不是真或假(尽管这确实是一个美学问题)，因为这样代码就更容易转移。它还可以防止延迟编码。