mssql和日常办公日记

mssql:
字符串转日期
a.schedule_date between cast('2022-11-10' as datetime) and cast('2022-11-29' as datetime)
日期截取
SUBSTRING(convert(varchar(101), begntime, 8), 0, 6) AS begntime
转换日期
CONVERT(varchar(100), a.schedule_date, 23) AS scheduleDate
格式化日期
FORMAT(schedule_date, 'yyyy-MM-dd') AS scheduleDate -- yyyy-MM-dd HH:mm:ss

日期大于昨天
select * from mob_use_appointmen where appoint_date > dateadd(DD,-1,getdate())

计算年龄
floor((DateDiff(Day, vis_birth, getdate()))/365.25) age

SELECT FORMAT (getdate(), 'dd-MM-yy') as date
SELECT FORMAT (getdate(), 'hh:mm:ss') as time

生成uniquestirng ID
NEWID()
sysdate()

分页：

WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY id DESC) as __row_number__, id,order_number FROM mob_use_appointmen WHERE (mark = 1) ORDER BY id DESC) SELECT * FROM selectTemp WHERE __row_number__ BETWEEN 1 AND 10 ORDER BY __row_number__

// 获取数据列表
IPage<SetDoctor> page = new Page<>(setDoctorQuery.getPage(), setDoctorQuery.getLimit());
IPage<SetDoctor> pageData = setDoctorMapper.selectPage(page, queryWrapper);
pageData.convert(x -> {
SetDoctorListVo setDoctorListVo = Convert.convert(SetDoctorListVo.class, x);
return setDoctorListVo;
});

<resultMap type="com.javaweb.generator.entity.GenTable" id="GenTableResult">
<id property="id" column="id"/>
<result property="tableName" column="table_name"/>
<result property="tableComment" column="table_comment"/>
<result property="className" column="class_name"/>
<result property="tplCategory" column="tpl_category"/>
<result property="packageName" column="package_name"/>
<result property="moduleName" column="module_name"/>
<result property="businessName" column="business_name"/>
<result property="functionName" column="function_name"/>
<result property="functionAuthor" column="function_author"/>
<result property="options" column="options"/>
<result property="createUser" column="create_user"/>
<result property="createTime" column="create_time"/>
<result property="updateUser" column="update_user"/>
<result property="updateTime" column="update_time"/>
<result property="note" column="note"/>
<collection property="columns" javaType="java.util.List" resultMap="GenTableColumnResult"/>
</resultMap>

<resultMap type="com.javaweb.generator.entity.GenTableColumn" id="GenTableColumnResult">
<id property="id" column="id"/>
<result property="tableId" column="table_id"/>
<result property="columnName" column="column_name"/>
<result property="columnComment" column="column_comment"/>
<result property="columnType" column="column_type"/>
<result property="javaType" column="java_type"/>
<result property="javaField" column="java_field"/>
<result property="isPk" column="is_pk"/>
<result property="isIncrement" column="is_increment"/>
<result property="isRequired" column="is_required"/>
<result property="isInsert" column="is_insert"/>
<result property="isEdit" column="is_edit"/>
<result property="isList" column="is_list"/>
<result property="isQuery" column="is_query"/>
<result property="queryType" column="query_type"/>
<result property="htmlType" column="html_type"/>
<result property="dictType" column="dict_type"/>
<result property="sort" column="sort"/>
<result property="createUser" column="create_user"/>
<result property="createTime" column="create_time"/>
<result property="updateUser" column="update_user"/>
<result property="updateTime" column="update_time"/>
</resultMap>

/**
* 获取业务表列表
*
* @param page 分页信息
* @param param 参数
* @return
*/
IPage<GenTable> selectGenTableList(IPage<GenTable> page, @Param("param") GenTableQuery param);

<sql id="selectGenTableVo">
select id, table_name, table_comment, class_name, tpl_category, package_name, module_name, business_name, function_name, function_author, options, note, create_user, create_time, update_user, update_time, mark from gen_table
</sql>

<!-- 获取数据表列表 -->
<select id="selectGenTableList" parameterType="com.javaweb.generator.entity.GenTable" resultMap="GenTableResult">
<include refid="selectGenTableVo"/>
<where>
<if test="param.tableName != null and param.tableName != ''">
AND lower(table_name) like lower(concat('%', #{param.tableName}, '%'))
</if>
<if test="param.tableComment != null and param.tableComment != ''">
AND lower(table_comment) like lower(concat('%', #{param.tableComment}, '%'))
</if>
AND mark = 1
</where>
</select>

<!-- 获取数据库表 -->
<select id="selectDbTableList" parameterType="com.javaweb.generator.entity.GenTable" resultMap="GenTableResult">
select table_name, table_comment, create_time, update_time from information_schema.tables
where table_schema = (select database())
AND table_name NOT LIKE 'gen_%'
AND table_name NOT IN (select table_name from gen_table)
<if test="param.tableName != null and param.tableName != ''">
AND lower(table_name) like lower(concat('%', #{param.tableName}, '%'))
</if>
</select>

<!-- 根据表名获取数据表 -->
<select id="selectDbTableListByNames" resultMap="GenTableResult">
select table_name, table_comment, create_time, update_time from information_schema.tables
where table_name NOT LIKE 'gen_%' and table_schema = (select database())
and table_name in
<foreach collection="array" item="name" open="(" separator="," close=")">
#{name}
</foreach>
</select>

获取2周后日期
//当前日期
String currentDate = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));

LocalDate today = LocalDate.now();
String currentDate2 = today.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
System.out.println(currentDate2);

LocalDate nextWeek = today.plus(2, ChronoUnit.WEEKS);

currentDate2 = nextWeek.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
System.out.println(currentDate2);

select top 1 hos.hospital_number , h.id , h.interval_num, k.department_name , y.doc_name , p.reg_fee , p.schedule_date \n" +
" , case when p.time_flag = 1 then '上午' when p.time_flag = 2 then '下午' when p.time_flag = 1 then '晚上' end as time_flag\n" +
" , CONVERT(varchar(12) , h.begntime , 108 ) as begntime , CONVERT(varchar(12) , h.endtime , 108 ) as endtime \n" +
" , substring( CONVERT(varchar(12) , h.begntime , 108 ) ,1,5) + '-' + substring( CONVERT(varchar(12) , h.endtime , 108 ) ,1,5) as interval\n" +
" , hos.hospital_name \n" +
" from set_doc_roster_interval as h \n" +
" join set_doc_roster as p on h.schedule_id = p.id \n" +
" join set_doctor as y on y.id = p.dr_code \n" +
" join set_department as k on k.id = p.dep_code \n" +
" join set_hospital as hos on hos.hospital_number = h.hospital_number \n" +
" where h.hospital_number =#{hospitalNumber} \n" +
" and h.id = #{id} ")

http://192.168.1.56:8080/jeecg-boot/#/default/人员对接接口信息/getRyxxUsingGET

getOpenUser
人员信息查询
Parameters

mvn archetype:generate
mvn dependency:tree
mvn dependency:list
mvn clean install -Dmaven.test.skip=true
mvn