我有一个应用程序,您可以使用过滤器的月份,年份和类别。它们不是强制性的,你也可以使用所有3个过滤器,只需月份和年份或任何可能的。
我在我的房间里尝试了SQL查询,DAO首先是这样的:
SELECT *
FROM SaveEntryEntity
WHERE " +
"CASE " +
"WHEN (:month) IS NOT NULL THEN substr(date,4,2) = (:month) " +
"WHEN (:year) IS NOT NULL AND (:month) IS NULL THEN substr(date,7,4) = (:year) " +
"WHEN (:kategory) IS NOT NULL THEN kategory = (:kategory) " +
"ELSE substr(date,4,2) = (:month) AND substr(date,7,4) = (:year) AND kategory = (:kategory) " +
"END " +
"OR " +
"CASE " +
"WHEN (:month) IS NOT NULL THEN substr(date,4,2) = (:month) " +
"ELSE NULL " +
"END AND " +
"CASE " +
"WHEN (:year) IS NOT NULL THEN substr(date,7,4) = (:year) " +
"ELSE NULL " +
"END"
List<SaveEntryEntity> getMyAttributes(String month, String year, String kategory);
这样,我就可以只过滤月份,或者只过滤年份,而不能过滤一年中的月份。
另一次尝试是这样的:
@Query("SELECT * FROM SaveEntryEntity WHERE " +
"(:month IS NULL OR substr(date,4,2) = :month) AND (:year IS NULL OR substr(date,7,4) = :year) OR " +
"(:month IS NOT NULL AND substr(date,4,2) = :month) OR " +
"(:kategory IS NULL OR kategory = :kategory)")
List<SaveEntryEntity> getMyAttributes(String month, String year, String kategory);`
但在这里,我甚至不能过滤单个参数。通过该查询,我希望过滤出年份和月份。
事实上,我希望能够用这三种过滤器来过滤任何可能的方式。
有人知道我怎样才能做到我所描述的那样吗?
非常感谢
1条答案
按热度按时间5f0d552i1#
我自己解决了这个问题。我上次尝试的逻辑基本上是正确的,我混淆了OR和AND。有了这一点,任何事情都能奏效: