/******************************删除****************************************************************************/
delete from pl_dynamic_field_data where df_id in( 
select df_id from pl_dynamic_field 
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='22726b54-5748-4926-82ed-abe076caecfa')
);

delete from pl_dynamic_field 
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='22726b54-5748-4926-82ed-abe076caecfa');

delete from pl_dynamic_table where ds_id ='22726b54-5748-4926-82ed-abe076caecfa';

delete from pl_dynamic_select where ds_id ='22726b54-5748-4926-82ed-abe076caecfa';

/******************************添加****************************************************************************/
INSERT INTO pl_dynamic_select (ds_id, ds_name, ds_describe, ds_create_time) VALUES ('22726b54-5748-4926-82ed-abe076caecfa', '品牌查询', null, null);

INSERT INTO pl_dynamic_table (dt_id, ds_id, dt_name, dt_tablename, dt_type, dt_ref_select, dt_on, dt_relation, dt_sort, dt_create_time, dt_update_time) VALUES ('72324598-9665-4985-9417-b65ae9d8ad9e', '22726b54-5748-4926-82ed-abe076caecfa', '品牌表', 'mtBrand', 1, null, null, 1, 1, null, null);

INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('9e31fa17-c545-4dca-881f-ef64aac37af6', '72324598-9665-4985-9417-b65ae9d8ad9e', '过滤品牌', null, 'filter', false, 4, 1, 1, 1, true, 1, null, 1, null, 'CASE WHEN ${filter}=1 THEN 1=1 WHEN ${filter}<>1 THEN t1.brId IN ( SELECT br.brId FROM smUsers us LEFT JOIN smUsPermission usp ON usp.usId = us.usId LEFT JOIN smUsPermBrand usb ON usp.uspId = usb.uspId LEFT JOIN mtBrand br ON usb.brId = br.brId WHERE us.usId=${filter} )END', null, null, null, '${userId}', null, null, 100, null, null, null, null, null, null, null, 1, 1, null, null, null, 1, false, true);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('fe76e39b-d6d4-497d-9bbb-6be3b9c9b4ea', '72324598-9665-4985-9417-b65ae9d8ad9e', '代号', null, 'brId', true, 3, 1, 2, 21, false, 1, null, null, null, null, null, '不限', null, null, null, null, 200, null, null, null, null, null, null, null, 1, 1, null, null, null, 1, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('9c7017f5-62cb-4584-a637-dcbaa901543d', '72324598-9665-4985-9417-b65ae9d8ad9e', '名称', null, 'brName', false, 1, 1, 1, 1, false, 1, null, null, null, 'locate(${brName},${masterTable}.brName)>0', null, '不限', null, null, null, null, 200, null, null, null, null, null, null, null, 1, 1, null, null, null, 2, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('c881ae5c-a650-4635-9a89-9e73eefaac9b', '72324598-9665-4985-9417-b65ae9d8ad9e', '描述 ', null, 'brDesc', false, 3, 1, 1, 1, false, 1, null, null, null, null, null, '不限', null, null, null, null, 200, null, null, null, null, null, null, null, 1, 1, null, null, null, 3, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('33779476-472b-41e4-b24c-29a432fca8ed', '72324598-9665-4985-9417-b65ae9d8ad9e', '编码值', null, 'brCode', false, 1, 1, 1, 1, false, 1, null, null, null, 'locate(${brCode},${masterTable}.brCode)>0', null, '不限', null, null, null, null, 200, null, null, null, null, null, null, null, 1, 1, null, null, null, 4, false, false);
INSERT INTO pl_dynamic_field (df_id, dt_id, df_name, df_field_asname, df_field_name, df_primary, df_type, df_group, df_data_type, df_filter_type, df_filter_hidden, df_select_type, df_ref_select, df_ref_type, df_select_format, df_where_format, df_having_format, df_empty_tips, df_notnull, df_defval, df_regex, df_regex_error, df_width, df_decimals, df_maxvalue, df_minvalue, df_maxlength, df_minlength, df_temp_text, df_temp_sql, df_data_use_type, df_data_show_type, df_data_sql, df_orderby, df_group_concat, df_sort, df_disable, df_select_hidden) VALUES ('63781d57-a2af-484d-bcbb-b62fd5d6585d', '72324598-9665-4985-9417-b65ae9d8ad9e', '可用', null, 'ptValid', false, 1, 1, 2, 21, false, 1, null, null, null, null, null, '不限', null, null, null, null, 200, null, null, null, null, null, null, null, 1, 1, null, null, null, 4, false, false);

INSERT INTO pl_dynamic_field_data (dfd_id, df_id, dfd_code, dfd_name, dfd_ext, dfd_group, dfd_parent) VALUES ('34009937-ef4c-4c6c-b4de-1bc5f74e735a', '63781d57-a2af-484d-bcbb-b62fd5d6585d', '1', '是', null, null, null);
INSERT INTO pl_dynamic_field_data (dfd_id, df_id, dfd_code, dfd_name, dfd_ext, dfd_group, dfd_parent) VALUES ('99e2ef1b-6fe9-4e60-a0f2-810fde3c1ae1', '63781d57-a2af-484d-bcbb-b62fd5d6585d', '0', '否', null, null, null);


/******************************查询****************************************************************************/
select * from pl_dynamic_select where ds_id ='22726b54-5748-4926-82ed-abe076caecfa';
select * from pl_dynamic_table where ds_id ='22726b54-5748-4926-82ed-abe076caecfa' order by dt_sort asc;

select * from pl_dynamic_field 
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='22726b54-5748-4926-82ed-abe076caecfa')
order by df_sort asc;

select * from pl_dynamic_field_data where df_id in( 
select df_id from pl_dynamic_field 
where dt_id in (select dt_id from pl_dynamic_table where ds_id ='22726b54-5748-4926-82ed-abe076caecfa')
);