Write SQL-CodePudding

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:

 CREATE TABLE department (the deptname nvarchar (16), empname varchar (8), occu varchar (8), salary numeric (12, 2)) 

SELECT the deptname, CNT=COUNT (*) 
The FROM department 
WHERE salary & gt; 1000 
AND (the deptname LIKE '% % development department OR the deptname LIKE' % % 'maintenance department) 
GROUP BY the deptname HAVING the COUNT (*) & gt; 10

CodePudding user response:

Ask this question is very chaotic, is basically guessing,

CodePudding user response:

reference 1/f, eldest brother I reply:

 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

is this query twice?

CodePudding user response:

I'm a little dizzy, now what do you want in Canada post is a programmer and maintenance engineer?

CodePudding user response:

reference 4 floor h_0724 response:

Quote: reference 1/f, eldest brother I reply:

 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

is this query twice?

can switch to a query,

 SELECT DISTINCT number of programmers in the development department. The number, the number of maintenance engineer in the maintenance department. The number of departments table. Department, the department table. Position the FROM department 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 
LEFT the JOIN (SELECT department, position, the COUNT (name) AS the number of the FROM table WHERE departments='development' AND post='programmers' GROUP BY department, job) AS development programmers number 
ON the number of programmers in the development department. Table=departments. Departments AND the number of programmers in the development department.=departments table. His duties 
LEFT the JOIN (SELECT department, position, the COUNT (name) AS the number of the FROM table WHERE departments='maintenance department AND post=' maintenance engineers' GROUP BY department, job) AS maintenance engineer in maintenance department number 
ON the number of maintenance engineer in the maintenance department.=departments table. The number of maintenance engineer in the department AND maintenance department.=departments table. His duties 
The WHERE (salary & gt; 1000 AND department number. The number & gt; 10) 
AND ((='development' tables. Departments AND departments table. Post='programmers') OR (table. Table=' maintenance department AND departments. Post='maintenance engineers'))

CodePudding user response:

Do you this is to build tables, or queries, questions of have a little fuzzy, also does not suit the answer,