Mysql常见问题总结

1.表的去重操作

 

2.分组统计

 

1）项目无计划[projectplan表中无对应记录或者status状态为0]

 

SELECT project.Name,
CASE MAX(
	CASE WHEN projectplan.`Status`>0 THEN 1 ELSE 0 END
)
WHEN 1 
THEN '项目计划'
ELSE '无项目计划'
END
FROM project LEFT JOIN projectplan ON project.ID=projectplan.ProjectID
GROUP BY project.ID

 

SELECT project.ID FROM project
WHERE EXISTS (SELECT 1 FROM projectplan WHERE projectplan.ProjectID=project.ID AND projectplan.Status>0)

 2大阶段活动数<2[查询一级活动数]

 

SELECT
	project.Name,COUNT(planitem.ID)
FROM
	project
LEFT JOIN planitem ON project.id = planitem.project_id
WHERE
	planitem.LEVEL = 1
GROUP BY
	project.ID
HAVING COUNT(planitem.ID)>= 2