如何编写多字段的mysql查询(where子句中有组合)

ztyzrc3y 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(296)

在上面的图片我有6个领域，从他们我的搜索可以是6个领域的任何组合，它只能是 class 或者 class-section 或者 class-section-caste 如何编写独立于参数数量的单个查询。
在where子句中可以用and或吗

class=Frist or (class=first and section=A) or (class=first and section=A and gender=Male)

有没有可能做到这一点，或者有没有更好的办法来实现这一点。
编辑请查看查询

query = currentSession.createQuery("select s.studentAdmissionId as studentAdmissionId, s.adsmissionNum as studentAdmnNum ,s.firstname as studentFirstName , s.lastname as studentLastName , s.adharNum as adharNumber ,"
                        + "  peDe.contactNum as mobileNumber  ,m.className as className ,sec.sectionName as sectionName from StudentDetails s, StudentPresentClassDetails pd, MasterCampusClass m ,"
                        + "MasterCampusSection sec , StudentParentDetails peDe  where s.studentAdmissionId = peDe.studentAdmissionId and (pd.studentAdmissionId = s.studentAdmissionId and pd.classId=m.classId) and (pd.studentAdmissionId = s.studentAdmissionId and pd.presentSectionId = sec.sectionId) and pd.classId = :classId or sec.sectionId .........");

mysql

来源：https://stackoverflow.com/questions/50927859/how-to-write-mysql-query-with-multiple-fieldshaving-combinations-in-where-claus

3条答案

按热度按时间

mgdq6dx11#

SELECT  *
FROM    testTable
WHERE   IF(
            :filter1 IS NOT NULL,
            col1 = :filter1,
            1 = 1
        )
        AND IF(
            :filter2 IS NOT NULL,
            `col2` = :filter2,
            1 = 1
        )
        AND IF(
            :filter3 IS NOT NULL,
            col3 = :filter3,
            1 = 1
        );

等等。简洁的sql

赞(0）回复(0）举报 2021-06-20

vulvrdjw2#

您可以使用多个if语句来创建查询：

StringBuilder sql = new StringBuilder("SELECT * FROM tableA WHERE 1 = 1 ");
Map<String, Object> params = new HashMap<>();
if (classValue!= null && !classValue.isEmpty()) {
    sql.append("AND class = :class ");
    params.add("class", classValue); // Or values you want
}
if (sectionValue!= null && !sectionValue.isEmpty()) {
    sql.append("AND section = :section ");
    params.add("section", sectionValue); // Or values you want
}
...
Query query = session.createQuery(sql.toString());
for (Entry<String, Object> parameters: params) {
    query.setParameter(parameters.getKey(), parameters.getValue());
}

赞(0）回复(0）举报 2021-06-20

gorkyyrv3#

@米ạ新罕布什尔州ế恩圭ễn谢谢你的示例代码。
代码中有一些错误。我写的代码引用@mạ新罕布什尔州ế恩圭ễn样本代码
给你

Map<String, Object> params = new HashMap<>();
            if (classId != null && !classId.equals("Select")) { 
                sql.append(" AND pd.classId = :classId ");
                params.put("classId", classId); // Or values you want
            } 

            if(sectionId != null && !sectionId.equals("Select"))
            {
                 sql.append(" AND pd.presentSectionId = :presentSectionId ");
                 params.put("presentSectionId", sectionId); // Or values you want
            }

            if(gender != null && !gender.equals("Select"))
            {
                 sql.append(" AND s.gender = :gender "); 
                 params.put("gender", gender); // Or values you want
            }

            if(religion != null && !religion.equals("Select"))
            {
                 sql.append(" AND s.religionId = :religionId "); 
                 params.put("religionId", religion); // Or values you want
            }
            if(casteId != null && !casteId.equals("Select"))
            {
                 sql.append(" AND mcaste.casteId = (select mm.parentId from  MasterCaste mm where mm.casteId= :casteId ) "); 
                 params.put("casteId", Integer.parseInt(casteId)); // Or values you want
            }

            query = currentSession.createQuery(sql.toString());

            System.out.println(sql.toString());  

            for (Entry<String, Object> parameters: params.entrySet()) 
            {

                query.setParameter(parameters.getKey(), parameters.getValue());
            }

感谢每一个人！

赞(0）回复(0）举报 2021-06-20

我来回答

如何编写多字段的mysql查询(where子句中有组合)

3条答案

相关问题

热门标签

最新问答