Department table table1 (department, name, position, salary), query the various development programmers, and the maintenance department, the maintenance engineer salary in 1000 yuan of above of the total number of more than 10 people department,

CodePudding user response:

 SELECT COUNT number of programmers (name) AS development department table., position the FROM table 
LEFT the JOIN (SELECT department, COUNT (name) AS the number of the FROM table GROUP BY departments) AS the number of number of each department ON each department.=departments table. Department 
WHERE salary & gt; 1000 AND department number. The number & gt; 10 AND sector table.='development' AND post='programmers' 
GROUP BY table. Departments, post 

 SELECT COUNT (name) AS number of maintenance engineers, maintenance department departments table., position the FROM table 
LEFT the JOIN (SELECT department, COUNT (name) AS the number of the FROM table GROUP BY departments) AS the number of number of each department ON each department.=departments table. Department 
WHERE salary & gt; 1000 AND department number. The number & gt; 10 AND sector table.='maintenance department AND post=' maintenance engineers' 
GROUP BY table. Departments, post 

CodePudding user response:

To calculate the number, it is suggested that has an ID field, the

 COUNT (name) the 

 COUNT (ID)