需求1：
我的收藏列表：
1, 顶部展示条数
2, 展示政策名、状态、时间、类别，其中：
A, 政策名展示两行，展示不完用...表示
B, 状态与原版本相同（申报预告、正在申报、申报结束）
C, 展示时间也与之前规则相同
D, 类别也与之前规则相同
3, 排序方式与之前规则相同：
A, 首先按照申报预告、正在申报、申报结束的顺序排列
B, 然后申报预告中，按开始时间正序排列
C, 其次正在申报按结束时间正序排列，结束时间相同时（均为长期受理就是这种情况）按开始时间倒序排列
D, 最后，申报结束按结束时间倒序排列
select distinct CM_ID,
CM_NAME,
GCT_NAME,
CM_PUBLISH_TIME,
CM_START_TIME,
CM_END_TIME,
CM_SORT_CODE,
(case
when CM_START_TIME > now() then 1
when CM_START_TIME < now() and CM_END_TIME > now()
then 2
when CM_END_TIME < now() then 3 end) as code,
CM_NEXT_START_TIME
from tbl_content_mgt
left join tbl_group_cms_type on GCT_ID = CM_GCT_ID
left join tbl_match_industry on MI_CM_ID = CM_ID
left join tbl_policy_org_detail pod on pod.POD_CM_ID = CM_ID
left join tbl_policy_org po on po.PO_ID = pod.PO_ID
left join TBL_MY_COLLECTION mc on mc.MC_BUSINESS_ID = CM_ID
where CM_TYPE = 4
and CM_STATE = 2
order by code asc,
case when CM_START_TIME > now() then CM_START_TIME end asc,
case when CM_START_TIME <= now() and CM_END_TIME >= now() then CM_END_TIME end asc,
case when CM_START_TIME <= now() and CM_END_TIME >= now() and CM_END_TIME = '2068-01-01'
then CM_START_TIME end desc,
case when CM_END_TIME < now() then CM_END_TIME end desc
需求2
select tb.CM_NAME,
CM_INDUSTRY_CODE,
CM_REVENUE,
CM_EMPLOYEES,
CM_DIAGNOSIS_INFO,
tc.EBE_REQUEST_OTHER,
(SELECT CASE tt.BT_ID
WHEN 11 THEN
concat(GROUP_CONCAT(BT_NAME ORDER BY BT_ID DESC SEPARATOR '、'), '（', EBE_REQUEST_OTHER, '）')
ELSE GROUP_CONCAT(BT_NAME ORDER BY BT_ID DESC SEPARATOR '、') END
FROM tbl_base_tag tt
WHERE
tt.BT_TYPE = ?
AND INSTR(tc.EBE_REQUEST_IDS, tt.BT_ID)) AS req
from TBL_CONSULTING_MGE t
left join TBL_CONTENT_MGT tb on t.CM_ID = tb.CM_ID
left join TBL_ENTERPRISE_BASE_EXT tc on t.CM_EN_ID = tc.EBE_ID
where t.CM_ID = ?
and t.CM_EN_ID = ?
需求3
select cm1.CM_ID,
cm1.CM_G_ID,
cm1.CM_GCT_ID,
cm1.CM_SELF_ID,
if(cm1.CM_SELF_ID is null, cm1.CM_NAME, cm2.CM_NAME) as CM_NAME,
cm1.CM_CODE,
if(cm1.CM_SELF_ID is null, cm1.CM_INFO, cm2.CM_INFO) as CM_INFO,
if(cm1.CM_SELF_ID is null, cm1.CM_FILES, cm2.CM_FILES) as CM_FILES,
GCT_NAME,
G_NAME,
MC_SEND_TIME,
cm1.CM_IS_ALL,
cm1.CM_STATE,
cm1.CM_PUBLISH_ORG,
cm1.CM_PUBLISH_TIME,
BT_NAME,
cm1.CM_SELF_ID,
cm1.CM_IS_REPLY,
if(cm1.CM_SELF_ID is null, cm1.CM_START_TIME, cm2.CM_START_TIME) as CM_START_TIME,
if(cm1.CM_SELF_ID is null, cm1.CM_END_TIME, cm2.CM_END_TIME) as CM_END_TIME,
group_concat(PO_NAME) as poNames,
group_concat(po.PO_ID) as poIds
from tbl_content_mgt cm1
left join tbl_message_center mc on mc.MC_BUSINESS_ID = cm1.CM_ID
left join tbl_group on G_ID = cm1.CM_G_ID
left join tbl_content_mgt cm2 on cm2.CM_ID = cm1.CM_SELF_ID
left join tbl_group_cms_type on GCT_ID = if(cm1.CM_SELF_ID is null, cm1.CM_GCT_ID, cm2.CM_GCT_ID)
left join tbl_base_tag on BT_ID = if(cm1.CM_SELF_ID is null, cm1.CM_PUBLISH_ORG, cm2.CM_PUBLISH_ORG)
left join tbl_policy_org_detail pod on pod.POD_CM_ID = if(cm1.CM_SELF_ID is null, cm1.CM_ID, cm1.CM_SELF_ID)
left join tbl_policy_org po on po.PO_ID = pod.PO_ID
group by POD_CM_ID;
需求4
SELECT sum(t.OI_SUBSIDY_QUOTA) as sum,
teq.EQ_ID,
EQ_SC_NAME,
EQ_QUOTA,
TM_NAME,
TM_TOTAL_QUOTA,
TM_USED_QUOTA,
TM_START_DAY,
TM_END_DAY,
TM_STATE
FROM tbl_order_items t
right join tbl_en_quota teq on t.OI_EQ_ID = teq.EQ_ID
right join tbl_ticket_mgt ttm on teq.EQ_TM_ID = ttm.TM_ID
group by EQ_ID;
