sql 学习

来源：互联网发布：鬼泣4但丁数据编辑：程序博客网时间：2024/06/02 07:31

ALTER TABLE `pm_sys_packages`ADD COLUMN `APP_NAME`  varchar(64) NULL AFTER `PACKAGE_USE_TYPE`,ADD COLUMN `APP_CODE`  varchar(32) NOT NULL AFTER `APP_NAME`;

update pm_sys_packages t,sm_system s set t.APP_CODE = CONCAT(s.SYS_NAME,'-',t.PACKAGE_ID) where t.SYS_ID = s.SYS_ID and

( t.APP_CODE = '' or t.APP_CODE is null);update pm_sys_packages pm,( SELECT DISTINCTT.SYS_ID,T.packageId,T.PACKAGE_NAME,T.packageUseType,T.packageType,T.PACKAGE_USE_TYPE_NAME,CONCAT(T.packageUseType,T.packageType) as appName,T.REPOS_NAME,T.PACKAGE_DESCFROM(SELECTsm.SYS_ID,pa.PACKAGE_NAME,(CASEWHEN pa.PACKAGE_TYPE = '163' THEN'应用发布包'WHEN pa.PACKAGE_TYPE = '164' THEN'静态发布包'END) packageType,(CASEWHEN pa.PACKAGE_USE_TYPE = '183' THEN'前台'WHEN pa.PACKAGE_USE_TYPE = '184' THEN'后台'WHEN pa.PACKAGE_USE_TYPE = '298' THEN'中台'WHEN pa.PACKAGE_USE_TYPE = '294' THEN''END) packageUseType,pa.PACKAGE_USE_TYPE_NAME,pa.PACKAGE_ID AS packageId,re.REPOS_NAME,1,pa.PACKAGE_DESCFROMsm_system sm,pm_sys_packages pa,cf_repository reWHEREsm.SYS_ID = pa.SYS_IDAND re.RELATE_ID = sm.SYS_IDAND re.TYPE = 115) T) tab  set pm.APP_NAME = tab.appName where pm.PACKAGE_ID = tab.packageId  and ( pm.APP_NAME = '' or pm.APP_NAME is null);

分组获取的数据进行去重筛选：（对查询回来属性列表中，部分属性distinct,不能整体distinct）,所以使用group by

 SELECT       t1.GROUP_ID,t1.ARTIFACT_ID,t3.VERSION, MAX(t1.CREATE_TIME) , MAX(t2.UPDATE_TIME)       FROM       CF_BRANCH_BUILD_DETAILTASK t1 INNER JOIN CF_BRANCH_BUILD_BASICINFO  t2        ON t1.BUILD_ID=t2.ID AND t2.BRANCH_STATUS='0' AND t2.BRANCH_ID <> '0'        INNER JOIN CF_BRANCH t3 ON t2.BRANCH_ID=t3.BRANCH_ID        INNER JOIN BM_PACKAGE_CONFIG t4 ON t2.SYS_ID=t4.SYS_ID AND t4.PACKAGE_TYPE='MAVEN'  WHERE    t1.CREATE_TIME>='20170701' AND t1.CREATE_TIME<='20170801' AND t1.BUILD_STATUS='2'GROUP BY t1.GROUP_ID,t1.ARTIFACT_ID,t3.VERSION

阅读全文

0 0