Consult a query-CodePudding

- staff table
The create table userry
(
Userry_bm1 varchar (50), - level department
Userry_bm2 varchar (50), the secondary department
Userry_name varchar (50), - name
Userry_zy varchar (50), - post
Integral
userry_yu int, -)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'human resources',' zhang ', 'employees', 200)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'technology', 'bill', 'employees' 100)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'technology', 'Mr. Lee', 'employees' 100)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' B ', 'technology', 'Cathy', 'employees' 500)
GO
Select * from userry
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- need to check a balance of statistical results, probably format is as follows: is that each company to tot up the total number of points, each of the different department
Primary sector secondary sector position integral
A company of personnel staff of 200
Total 200
Company A technical staff of 200
Total 200
The company technical staff of 500 B
In total, 500

CodePudding user response:

 
SELECT the CASE WHEN the GROUPING (userry_zy)=1 THEN 'total' ELSE userry_bm1 END AS userry_bm1, 
CASE WHEN the GROUPING (userry_zy)=1 THEN 'ELSE userry_bm2 END AS userry_bm2, 
The SUM (userry_yu) 
The FROM # userry 
GROUP BY userry_bm1 userry_bm2, userry_ZY WITH ROLLUP 
HAVING the GROUPING (userry_bm2)=0

CodePudding user response:

The

refer to the original poster SLTZZZZZZ response:

- staff table
The create table userry
(
Userry_bm1 varchar (50), - level department
Userry_bm2 varchar (50), the secondary department
Userry_name varchar (50), - name
Userry_zy varchar (50), - post
Integral
userry_yu int, -)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'human resources',' zhang ', 'employees', 200)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'technology', 'bill', 'employees' 100)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'technology', 'Mr. Lee', 'employees' 100)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' B ', 'technology', 'Cathy', 'employees' 500)
GO
Select * from userry
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- need to check a balance of statistical results, probably format is as follows: is that each company to tot up the total number of points, each of the different department
Primary sector secondary sector position integral
A company of personnel staff of 200
Total 200
Company A technical staff of 200
Total 200
The company technical staff of 500 B
Total 500

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
I am now, add a field
- staff table
The create table userry
(
Userry_bm1 varchar (50), - level department
Userry_bm2 varchar (50), the secondary department
Userry_name varchar (50), - name
Userry_zy varchar (50), - post
Userry_sf varchar (50), identity -
Integral
userry_yu int, -)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu, userry_sf)
Values (' A company ', 'human resources',' zhang ', 'employees', 200,' noble ')
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu, userry_sf)
Values (' A company ', 'technology', 'bill', 'employees' 100,' civilians')
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu, userry_sf)
Values (' A company ', 'technology', 'Mr. Lee', 'employees' 100,' civilians')
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu, userry_sf)
Values (' B ', 'technology', 'Cathy', 'employees' 500,' civilians')
GO

Results show a line more, is there a way to solve it, thanks to the
Userry_bm1 userry_bm2 userry_sf (no column name)
Company A technical civilian 200
Company A technical NULL 200
Total 200
A company personnel noble 200
A NULL in the personnel division of the 200
Total 200
B company technical civilian 500
B company department NULL 500
Total 500

CodePudding user response:

refer to the second floor SLTZZZZZZ response:

Quote: refer to the original poster SLTZZZZZZ response:

- staff table
The create table userry
(
Userry_bm1 varchar (50), - level department
Userry_bm2 varchar (50), the secondary department
Userry_name varchar (50), - name
Userry_zy varchar (50), - post
Integral
userry_yu int, -)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'human resources',' zhang ', 'employees', 200)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
Values (' A company ', 'technology', 'bill', 'employees' 100)
GO
Insert userry (userry_bm1, userry_bm2 userry_name, userry_zy, userry_yu)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull