检查列表中的任何值是否满足条件

发布于 2025-02-07 02:22:49 字数 1431 浏览 3 评论 0原文

假设我有下表称为seasons：

...	start_month	end_month
...	2	6
...	3	4
...	... ...	...

我需要编写一个查询给定的月份列表，返回所有满足列表中至少1个月的条件的季节：start_month＆lt; = noter＆lt; = end_month。

我已经将此查询写为JDBC的本机查询，除了WHERE子句。

@Repository
public class SeasonsRepositoryImpl implements SeasonsRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<SeasonsProjection> findByMonths(List<Integer> months) {
        String query = "select * " +
                       "from seasons as s" +
                       "where ...."

        try {
            return  em.createNativeQuery(query)
                    .setParameter("months", months)
                    .unwrap(org.hibernate.query.NativeQuery.class)
                    .setResultTransformer(Transformers.aliasToBean(SeasonsProjection.class))
                    .getResultList();
        } catch (Exception e) {
            log.error("Exception with an exception message: {}", e.getMessage());
            throw e;
        }
    }

}

我不知道如何写这篇文章，我想到了使用任何操作员，直到我发现任何只能与桌子一起使用而不列表，我认为将其写为将列表转换为表的子查询，但我不喜欢t知道是否可以，我在MySQL文档中找不到任何东西。

原文

Suppose I have the following table called Seasons:

...	start_month	end_month
...	2	6
...	3	4
...	...	...

I need to write a query which, for a given list of months, returns all the Seasons that satisfy the condition where at least 1 month in the list is: start_month <= month <= end_month.

I've written this query as a native query with JDBC, except the where clause.

@Repository
public class SeasonsRepositoryImpl implements SeasonsRepositoryCustom {

    @PersistenceContext
    private EntityManager em;

    @Override
    public List<SeasonsProjection> findByMonths(List<Integer> months) {
        String query = "select * " +
                       "from seasons as s" +
                       "where ...."

        try {
            return  em.createNativeQuery(query)
                    .setParameter("months", months)
                    .unwrap(org.hibernate.query.NativeQuery.class)
                    .setResultTransformer(Transformers.aliasToBean(SeasonsProjection.class))
                    .getResultList();
        } catch (Exception e) {
            log.error("Exception with an exception message: {}", e.getMessage());
            throw e;
        }
    }

}

I have no idea how to write this, I thought of using the ANY operator until I found out that ANY only works with tables and not lists, I thought of writing this as a subquery with converting the list to a table, but I don't know if that's possible, I couldn't find anything in the MySQL documentation.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情未る 2025-02-14 02:22:49

在您的Where条款中，您需要每个月的条件或条件。
您可以在循环中生成它，然后进行concat或类似的东西。

例如，您对1,3,4,7的输入的最终查询将如下：

SELECT *
FROM seasons as s
WHERE (1 between start_month and end_month
OR 3 between start_month and end_month
OR 4 between start_month and end_month
OR 7 between start_month and end_month)

In your where clause, you will want to have an OR conditions for each month.
You can generate it in a loop and then concat or something similar.

Your final query for input of 1,3,4,7 for example will look as follows:

SELECT *
FROM seasons as s
WHERE (1 between start_month and end_month
OR 3 between start_month and end_month
OR 4 between start_month and end_month
OR 7 between start_month and end_month)

回复收藏 0 原文

离去的眼神 2025-02-14 02:22:49

实现此目的的一种方法是：

select s.* 
from (select 1 as mn union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8
   union select 9 union select 10 union select 11 union select 12) as a
inner join season s on a.mn between s.start_month and s.end_month
where a.mn in (:flexibleTypeMonths);

One way to accomplish this is:

select s.* 
from (select 1 as mn union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8
   union select 9 union select 10 union select 11 union select 12) as a
inner join season s on a.mn between s.start_month and s.end_month
where a.mn in (:flexibleTypeMonths);

回复收藏 0 原文

~没有更多了~