业务Sql

2019独角兽企业重金招聘Python工程师标准>>>

SELECT
	p.id AS pId,
	p.project_name,
	e.emp_name,
	e.id AS eId,
  GROUP_CONCAT(e.id) as userIds,
  GROUP_CONCAT(e.emp_name) as userNames
FROM
	project P
LEFT JOIN emp_pro ep ON ep.pro_id = p.id
LEFT JOIN emp e ON e.id = ep.emp_id
GROUP BY pid

-------------------------------------------------------------------------------------

SELECT
	A.state, A.userNums,B.userNumsTotal, 
  CONCAT( CONVERT ( A.userNums / B.userNumsTotal, DECIMAL (38, 5) ) * 100, '%' ) AS rate
FROM
	(
		SELECT
			1 AS id, SUM(userNums) AS userNums, state
			FROM mobile_regionUser
			WHERE state IS NOT NULL
			AND state != ''
      GROUP BY state
	) A
LEFT JOIN (
	SELECT 1 AS id,
		SUM(userNums) AS userNumsTotal
		FROM mobile_regionUser B
		WHERE state IS NOT NULL
		AND state != ''
) B ON A.id = B.id
GROUP BY userNums DESC

转载于:https://my.oschina.net/u/2253438/blog/818499