[jeeplus] 使用笔记 (gridSelect、datetimepicker etc.)
本贴最后更新于 1798 天前,其中的信息可能已经时过境迁
相关帖子
请输入回帖内容
...
-
- 其他回帖
-
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 •作者
根据某列中的值是否包含某个字符串筛选记录:
UPDATE m_levelformajor SET type = ( CASE WHEN 1 < LENGTH(number) AND LENGTH(number) < 3 THEN "22" WHEN 3 < LENGTH(number) AND LENGTH(number) < 5 THEN "23" WHEN 6 <= LENGTH(number) THEN "24" WHEN number IS NULL THEN "21" ELSE "1" END ) WHERE find_in_set('19db900b7129468385d96f96cd6c672a', parent_ids); --设置本科专业记录的 type UPDATE m_levelformajor SET type = ( CASE WHEN 1 < LENGTH(number) AND LENGTH(number) < 3 THEN "12" WHEN 3 < LENGTH(number) AND LENGTH(number) < 5 THEN "13" WHEN 6 <= LENGTH(number) THEN "14" WHEN number IS NULL THEN "11" ELSE "1" END ) WHERE find_in_set('e131fdeaf3f041b086bec4e68dd096c7', parent_ids);```
-
PeterChu •作者
SSM 中 MyBatis 的某个 xxxMapper.xml 中,是否可以这样写?
<if test="planMajorName != null and planMajorName != '' and r1 = '0'"> AND a.planmajorname = #{planMajorName} </if> <if test="planMajorName != null and planMajorName != '' and (r1 = null or r1 = '')"> AND a.planmajorname LIKE <if test="dbName == 'oracle'">'%'||#{planMajorName}||'%'</if> <if test="dbName == 'mssql'">'%'+#{planMajorName}+'%'</if> <if test="dbName == 'mysql'">concat('%',#{planMajorName},'%')</if> </if>
- 查看全部回帖