SELECT m1.id AS id, ( CASE WHEN (m2.id = 0 OR m2.id IS NULL) THEN 0 ELSE m2.id END ) AS pId, m1. NAME AS NAME, ( CASE WHEN (m2.id = 0 OR m2.id IS NULL) THEN 'true' ELSE 'false' END ) as isOpen FROM menu_daihou m1 LEFT JOIN menu_daihou m2 ON m1.pcode = m2. CODE ORDER BY m1.id ASC </select>
时间: 2024-04-06 13:29:14 浏览: 13
这是一条 SQL 查询语句,用于查询菜单表中的数据,并按照 id 升序排列。查询结果包括以下字段:
- id:菜单的唯一标识符。
- pId:菜单的父级菜单的唯一标识符,如果该菜单没有父级菜单,则为 0。
- NAME:菜单的名称。
- isOpen:表示该菜单是否为父级菜单,如果是则为 true,否则为 false。
该查询语句使用了左连接(LEFT JOIN),查询菜单表(menu_daihou)中的数据,并通过 pcode 字段与自身进行关联,来获取菜单的父级菜单信息。同时,使用了 CASE WHEN 语句来判断当前菜单是否为父级菜单,并将结果返回给 isOpen 字段。最后使用 ORDER BY 子句按照 id 升序排列查询结果。
相关问题
SELECT m1.id AS id, m1.pcode as pcode, m1.icon AS icon, ( CASE WHEN (m2.id = 0 OR m2.id IS NULL) THEN 0 ELSE m2.id END ) AS parentId, m1.NAME as name, m1.url as url, m1.levels as levels, m1.ismenu as ismenu, m1.num as num FROM menu_daihou m1 LEFT JOIN menu_daihou m2 ON m1.pcode = m2. CODE INNER JOIN ( SELECT ID FROM menu_daihou WHERE ID IN ( SELECT menuid FROM relation_daihou rela WHERE rela.roleid IN <foreach collection="list" index="index" item="i" open="(" separator="," close=")"> #{i} </foreach> ) ) m3 ON m1.id = m3.id and (m1.pcode in ('mxgl','xhcd_namelist','mxtj','bmxgl','kouche') or m1.code in ('mxgl','xhcd_namelist','xhcd-mid','mxtj','bmxgl','kouche') or m1.`name` = '档案管理') where m1.ismenu = 1 order by levels,num asc </select>
这是一个 SQL 查询语句,用于在 `menu_daihou` 表和 `relation_daihou` 表中获取菜单信息,并按照 `levels` 和 `num` 排序返回结果。语句中用到了左连接、内连接和嵌套查询等 SQL 的基本操作。
具体来说,这个查询语句中的 `<foreach>` 标签是 MyBatis 框架的特殊语法,用于将 Java 中的 List 参数转换为 SQL 语句中的 IN 表达式。在这个查询中,该标签用于将传入的 `list` 参数中的值作为 `relation_daihou` 表中 `roleid` 的查询条件。
该查询语句的具体功能是:获取 `menu_daihou` 表中 `ismenu` 字段为 1 的菜单信息,同时根据传入的 `list` 参数中的 `roleid` 的值,查询关联的菜单信息。同时,还需要满足 `m1.pcode` 字段在指定的值列表中,或者 `m1.code` 字段在指定的值列表中,或者 `m1.name` 字段的值为指定的字符串。
结果中的字段包括菜单 id、菜单编码、菜单图标、父级菜单 id、菜单名称、菜单链接地址、菜单层级、是否为菜单、菜单显示顺序等。其中,`parentId` 的值是根据 `m1.pcode` 字段在 `menu_daihou` 表中查询得到的 `m2.id`,如果查询结果为空,则赋值为 0。
case when a.id='1' from
It looks like your SQL statement is incomplete. The `CASE` statement is used to conditionally return a value based on one or more conditions. However, in your example, you only have the beginning of the statement (`CASE WHEN a.id='1' FROM`).
Assuming you want to return a specific value when `a.id` is equal to `'1'`, the correct syntax would be:
```
SELECT CASE WHEN a.id = '1' THEN 'Value when ID is 1' ELSE 'Value when ID is not 1' END as my_column
FROM my_table a
```
In this example, the `CASE` statement checks if `a.id` is equal to `'1'`. If it is, the value `'Value when ID is 1'` is returned. If it is not, the value `'Value when ID is not 1'` is returned. The result is returned as a column named `my_column` from the table `my_table` (which you will need to replace with your actual table name).