[jeeplus] 使用笔记 (gridSelect、datetimepicker etc.)
本贴最后更新于 1919 天前,其中的信息可能已经时过境迁
相关帖子
请输入回帖内容
...
-
PeterChu • • 1 评论作者
Mysql 中对一个表中的字段进行汇总计算后 赋值给 其中一个字段
如:招生计划表中,按照年份、省份、批次、文理科、学校 分组汇总后,将各专业招生计划数汇总后,赋值给该校该年度该批次该省份该文理科中的 招生计划总数。
查询语句:
SELECT COUNT(zhaoshengjihua) AS jihuazongshu, zhaoshengjihuazongshu, shengfen, nianfen, wenlike, pici, daxuedaima, daxuemingcheng FROM d_zhaoshengjihuabiao GROUP BY shengfen, nianfen, wenlike, pici, daxuedaima;
*zhaoshengjihuazongshu 字段中已经被赋值了,原来该字段中的值为空最开始写出的 sql 如下,但是完全不正确。
UPDATE d_zhaoshengjihuabiao a SET a.zhaoshengjihuazongshu = ( SELECT COUNT(b.zhaoshengjihua) AS b.cou, b.shengfen, b.nianfen, b.wenlike, b.pici, b.daxuedaima FROM d_zhaoshengjihuabiao b GROUP BY b.shengfen, b.nianfen, b.wenlike, b.pici, b.daxuedaima ) WHERE b.shengfen = a.shengfen AND b.nianfen = a.nianfen AND b.wenlike = a.wenlike AND b.pici = a.pici AND b.daxuedaima = a.daxuedaima
在网上搜索后,看到如下 2 条 sql 做为参考:
-- 1 UPDATE au_window_re_project aw SET aw.project_name = ( SELECT ap.SERVICENAME FROM apas_service ap WHERE ap.unid = aw.PROJECT_ID ) -- 2 UPDATE test_user_points AS a LEFT JOIN ( SELECT user_id, sum(point) AS point_count FROM test_point_log GROUP BY user_id ) AS b ON a.user_id = b.user_id SET a.point_count = b.point_count
最终参考第 2 条 SQL 写出如下语句,可以实现要求:
UPDATE d_zhaoshengjihuabiao AS a LEFT JOIN ( SELECT COUNT(zhaoshengjihua) AS cou, shengfen, nianfen, wenlike, pici, daxuedaima FROM d_zhaoshengjihuabiao GROUP BY shengfen, nianfen, wenlike, pici, daxuedaima ) AS b ON ( a.shengfen = b.shengfen AND a.wenlike = b.wenlike AND a.nianfen = b.nianfen AND a.pici = b.pici AND a.daxuedaima = b.daxuedaima ) SET a.zhaoshengjihuazongshu = b.cou ;
最开始的 SQL 还不知道错在了哪里,应该是用子查询实现的呀PeterChu • - 其他回帖
-
PeterChu •作者
主附表的页面中的如果某个字段用了字典,那么页面上该怎么显示该字段呢。
自动生成的页面的 Js 代码这块直接是{{dict.name}}
,dict 没见到哪里定义、或者调用了字典,数据库中当然存的是字典的 value,而不是 name ;
我之前的解决办法都是,存储的时候,直接存字典的 name 到数据库表中,这里显示的时候,就用{row.name}
,怎么用其他方法呢
2019-6-13 15:50:51
找到解决办法:原因:js 中未对使用了字典的字段做处理。在 2.41 新版本中发现此处 js 中有处理方法。
function detailFormatter(index, row) { var htmltpl = $("#volunteerChildrenTpl").html().replace(/(\/\/\<!\-\-)|(\/\/\-\->)/g,""); var html = Mustache.render(htmltpl, { idx:row.id }); $.get("${ctx}/volunteer/volunteerconfig/volunteer/detail?id="+row.id, function(volunteer){ var volunteerChild1RowIdx = 0, volunteerChild1Tpl = $("#volunteerChild1Tpl").html().replace(/(\/\/\<!\-\-)|(\/\/\-\->)/g,""); var data1 = volunteer.volunteerConfigList; for (var i=0; i<data1.length; i++){ data1[i].dict = {}; data1[i].dict.name = jp.getDictLabel(${fns:toJson(fns:getDictList('school_batches'))}, data1[i].name, "-"); addRow('#volunteerChild-'+row.id+'-1-List', volunteerChild1RowIdx, volunteerChild1Tpl, data1[i]); volunteerChild1RowIdx = volunteerChild1RowIdx + 1; } }) return html; }
这样之后,下面
<script type="text/template" id="volunteerChild1Tpl"> <tr> <td> {{row.dict.name}} </td> <td> {{row.vindex}} </td> <td> {{row.isArt}} </td> <td> {{row.isAdvance}} </td> <td> {{row.reportedStart}} </td> <td> {{row.reportedEnd}} </td> <td> {{row.raiseStart}} </td> <td> {{row.raiseEnd}} </td> <td> {{row.enrollStart}} </td> <td> {{row.enrollEnd}} </td> </tr> </script>
中就可以直接使用
{{row.dict.name}}
,这样,页面中主附表的附表内该字段就会正常显示了。1 操作PeterChu 在 2019-06-13 15:55:15 更新了该回帖 -
PeterChu •作者
MySql 中根据 判断某个字段(字符串)的长度后 按不同情况更新 同一个字段的值 的方法:
UPDATE m_levelformajor SET type = ( CASE WHEN 1 < LENGTH(number) AND LENGTH(number) < 3 THEN "2" WHEN 3 < LENGTH(number) AND LENGTH(number) < 5 THEN "3" WHEN 6 <= LENGTH(number) THEN "4" WHEN number IS NULL THEN "1" ELSE "1" END ) UPDATE m_levelformajor SET type = ( CASE WHEN number IS NULL THEN "1" ELSE "1" END ) SELECT id,name, number, type, parent_id,parent_ids , LENGTH(number) FROM m_levelformajor SELECT id,name, number, type, parent_id,parent_ids , LENGTH(number) FROM m_levelformajor WHERE LENGTH(number) <5 AND LENGTH(number) > 3 SELECT id,name, number, type, parent_id,parent_ids , LENGTH(number) FROM m_levelformajor WHERE 1 < LENGTH(number) AND LENGTH(number) <3 SELECT id,name, number, type, parent_id,parent_ids , LENGTH(number) FROM m_levelformajor WHERE LENGTH(number) <1```
-
PeterChu •作者
慎用
select *
当自己手写一些 sql 时,如果查找的对象/表中有封装了其他对象,那么,当 sql 中有
select *
时会导致封装对象为 Null 的现象。
因此,如果有会使用到封装的对象,那么 xml 中的 sql 还是加上映射关系吧。
eg:<!-- 根据年份、省份、高校、文理科、批次查找记录 findListByYPNWB --> <select id="findListByYPNWB" resultType="Eucode" > SELECT <include refid="EucodeColumns"/> FROM e_uecode a <include refid="EucodeJoins"/> WHERE a.year = #{year} AND a.province = #{province.id} AND a.universityid = #{university.id} AND a.wltype = #{wltype} AND a.schoolbatches = #{schoolBatches} AND (a.r3 != "2" OR ISNULL(a.r3)) AND (a.r3 != "3" OR ISNULL(a.r3)) </select>
- 查看全部回帖