SQL

select t.id,t.code,t.title,t.shop_id shopId,
        t.type,t.start_time startTime,t.end_time endTime,
        shop.shop_name shopName, t.member_level memberLevel,
        t.require_Include requireInclude,t.is_mobile mobile,
        t.remark,t.order_no orderNo,t.is_test test,
        t.order_type orderType, t.test_user testUser,t.test_date testDate,
        t.is_exclude exclude,t.is_overlap overlap,t.create_user createUser,
        t.create_date createDate, t.modify_user modifyUser,t.modify_date modifyDate,
        t.flag,
        GROUP_CONCAT(distinct mas.ssku_code) includeSskuCodes1, 
        GROUP_CONCAT(distinct mas.ssku_id) includeSskuIds1, 
        GROUP_CONCAT(distinct (case when num_iid is null then  mas.ssku_code else null end)) includeSskuCodes, 
        GROUP_CONCAT(distinct (case when num_iid is null then  mas.ssku_id else null end)) includeSskuIds, 
        GROUP_CONCAT(distinct mae.ssku_code) excludeSskuCodes, 
        GROUP_CONCAT(distinct mae.ssku_id) excludeSskuIds  
from oms_marketing_activities t 
LEFT JOIN t_pub_shop shop ON shop.shop_id=t.shop_id 
LEFT JOIN (select ssku_id,ssku_code,marketing_activities_id,flag,null num_iid  from oms_marketing_activities_include_ssku
              union ALL
           select ssku_id,ssku_code,marketing_activities_id,flag,num_iid  from oms_marketing_activities_num_iid
          ) as mas on t.id=mas.marketing_activities_id and mas.flag=0  
LEFT JOIN oms_marketing_activities_exclude_ssku  mae on t.id=mae.marketing_activities_id and mae.flag=0  
WHERE 1=1  and shop.shop_id=0 GROUP BY t.id  ORDER BY t.id  DESC

 

SELECT
    GROUP_CONCAT( DISTINCT s.NAME ) subjectNames,
    GROUP_CONCAT( DISTINCT s.id ) subjectIds 
FROM
    by_lesson_schedule ls
    INNER JOIN by_course c ON ls.course_id = c.id
    INNER JOIN by_subject s ON c.subject_id = s.id 
WHERE
    ls.is_signed = 0 
    AND ls.teacher_id = : teacherId 
    AND FIND_IN_SET ( s.id, (SELECT GROUP_CONCAT( st.subject_id ) FROM by_subject_teacher AS st WHERE st.teacher_id =: teacherId) )

 

转载于:https://www.cnblogs.com/java-zzl/p/9740006.html