Results in the table, the number of each class is not sure, how to remove the score before and after-CodePudding

Is a result table, inside field about the following

Per shift number is not sure, now require to calculate each division average, but the average is calculated after 1/10 and 1/10 places before students grades, how to achieve that in SQL statements.

Thank you!!

CodePudding user response:

SELECT AVG (mark) FROM league tables WHERE the score NOT IN
(
(SELECT TOP (SELECT COUNT (*) FROM league tables)/10) scores FROM record table ORDER BY score),
(SELECT TOP (SELECT COUNT (*) FROM league tables)/10) scores FROM score record table ORDER BY DESC)
)

CodePudding user response:

reference 1/f, kun jins kao kun jins kao response:

SELECT AVG (mark) FROM league tables WHERE the score NOT IN
(
(SELECT TOP (SELECT COUNT (*) FROM league tables)/10) scores FROM record table ORDER BY score),
(SELECT TOP (SELECT COUNT (*) FROM league tables)/10) scores FROM score record table ORDER BY DESC)
)

Points to add GROUP according to subject BY subject, subqueries will course=course inside, low efficiency of this method is good but feeling,

CodePudding user response:

 
The CREATE TABLE # A 
(
ID INT, 
KEMU VARCHAR (20), 
FENSHU INT 
) 
# INSERT INTO A VALUES (1, 'language', 100) 
# INSERT INTO A VALUES (2, 'language', 4) 
# INSERT INTO A VALUES (3, 'language', 50) 
# INSERT INTO A VALUES (4, 'language', 60) 
# INSERT INTO A VALUES (5, 'mathematics', 100) 
# INSERT INTO A VALUES (20) 6, 'mathematics', 
# INSERT INTO A VALUES (70) 7, 'mathematics', 
# INSERT INTO A VALUES (8 'mathematics', 72) 
# INSERT INTO A VALUES (74) 9, 'mathematics', 

The SELECT A.K EMU subjects, AVG (A.F ENSHU) divide the FROM # aa WHERE Anderson D NOT IN (
(SELECT TOP (SELECT the CONVERT (INT, between ((COUNT (*) * 0.1))) FROM # A WHERE clause KEMU=A.K EMU) ID # FROM A WHERE clause KEMU=A.K EMU ORDER BY FENSHU), 
(SELECT TOP (SELECT the CONVERT (INT, between ((COUNT (*) * 0.1))) FROM # A WHERE clause KEMU=A.K EMU) ID # FROM A WHERE clause KEMU=A.K EMU ORDER BY FENSHU DESC) 
) GROUP BY A.K EMU 

DROP TABLE # A

CodePudding user response:

Quote: refer to the third floor kun jins kao kun jins kao response:

[code=SQL]
You want to consider the situation of nouns are repeated, but also think about before and after the tenth, just have more than one

CodePudding user response:

reference 4 floor sleet response:

Quote: refer to 3rd floor kun jins kao kun jins kao response:

You want to consider the situation of nouns are repeated, but also think about before and after the tenth, just have more than one

Thank you!
In a way, but I feel more think more complicated, I don't know whether it is feasible to
Don't compare ranking but a score: the top 10% score lowest points respectively, and 10% after high score, and then use the average value of the open interval for these two grades

 
SELECT X.K EMU subjects, AVG (X.F ENSHU) divide the FROM # A WHERE clause X 
(SELECT TOP 1 FENSHU FROM (SELECT TOP (CONVERT (INT, (ROUND ((SELECT COUNT (*) FROM # A WHERE clause KEMU=X.K EMU) * 0.1, 0)))) * FROM # AA WHERE A.K EMU=X.K EMU ORDER BY A.F ENSHU) AA) & lt; 
X.F ENSHU AND X.F ENSHU & lt; 
(SELECT TOP 1 FENSHU FROM (SELECT TOP (CONVERT (INT, (ROUND ((SELECT COUNT (*) FROM # A WHERE clause KEMU=X.K EMU) * 0.1, 0)))) * FROM # AA WHERE A.K EMU=X.K EMU ORDER BY A.F ENSHU DESC) AA) 
GROUP BY X.K EMU

CodePudding user response:

Thinking:
First group, divided into 10, get rid of both ends to calculate again

 
The CREATE TABLE # A 
(
ID INT IDENTITY, 
[subject] VARCHAR (20) NOT NULL, 
Score INT the NOT NULL 
) 
INSERT INTO # A ([subject], Score) VALUES 
(' mathematics', 88), (' mathematics', 86), (' mathematics', 53), (' mathematics', 65), (' mathematics', 35), 
(' mathematics', 42), (' mathematics', 63), (' mathematics', 54), (' mathematics', 87), (' mathematics', 96), 
(' mathematics', 44), (' mathematics', 46), (' mathematics', 61), (' mathematics', 49), (' mathematics', 40), 
(' mathematics', 88), (' mathematics', 73), (' mathematics', 87), (' mathematics', 65), (' mathematics', 58), 
(37), 'mathematics', (' mathematics', 63), (' mathematics', 97), (' mathematics', 57), (' mathematics', 77), 
(' mathematics', 96), (' mathematics', 70), (' mathematics', 79), (' language ', 90), (' language ', 75), 
(' language ', 98), (' language ', 93), (' language ', 77), (' language ', 96), (' language ', 76), 
(' language ', 66), (' language ', 49), (' language ', 39), (' language ', 56), (' language ', 44), 
(' language ', 74), (' language ', 76), (' language ', 35), (' language ', 75), (' language ', 50), 
(' language ', 69), (' language ', 69), (' language ', 90), (' language ', 64), (' language ', 100), 
(' language ', 81), (' language ', 75), (' language ', 88), (' language ', 59), (' language ', 41), 
(' language ', 61), (' English ', 62), (' English ', 57), (' English ', 34) and (' English ', 90), 
(' English ', 37), (' English ', 60), (' English ', 95), (' English ', 75), (' English ', 90), 
(' English ', 91), (' English ', 70), (' English ', 96), (' English ', 72), (' English ', 58), 
(' English ', 75), (' English ', 38), (' English ', 58), (' English ', 39), (' English ', 91), 
(' English ', 83), (' English ', 30), (43) 'English', (' English ', 53), (' English ', 53), 
(' English ', 93), (' English ', 63), (' English ', 37), (97) 'English', 


; 
WITH t AS (SELECT *, NTILE (10) OVER (PARTITION BY a. [subject] ORDER BY a.S core) seq # FROM aa), - grouping, divided into 10 groups 
MaxSeq AS (SELECT t. [subject], MAX (seq) maxSeq FROM t GROUP BY t. [subject]), the largest GROUP of number, if the number is less than 10 will be less than 10, maximize, if record the number 10 is the 
nullnullnullnullnullnull