我有这张桌子
[1|Sub1|Mark1|Sub2|Mark2|Subj3|Mark3|...]
[2|JSP |85 |ASP |70 |PHP |95 |...]
我需要两个最高学科的平均成绩吗?
例如
我有这张桌子
[1|Sub1|Mark1|Sub2|Mark2|Subj3|Mark3|...]
[2|JSP |85 |ASP |70 |PHP |95 |...]
我需要两个最高学科的平均成绩吗?
例如
你可能会问这个吗?
SELECT ID,(SELECT AVG(v)
FROM (VALUES (Mark1),(Mark2),(Mark3),(Mark4),(Mark5)) AS value(v)) as [AverageMarks]
FROM Table1
如果您仅在3个主题中寻找2个最高主题的平均值,则尝试此操作。
SELECT ID,(SELECT (SUM(v)-MIN(V))/2
FROM (VALUES (Mark1),(Mark3)) AS value(v)) as [AverageMarks]
FROM Table1
,
请考虑以下内容:
DROP TABLE IF EXIST my_table;
CREATE TABLE my_table
(subject CHAR(3) PRIMARY KEY,mark INT NOT NULL
);
INSERT INTO my_table VALUES
('JSP',85),('ASP',70),('PHP',95);
SELECT AVG(a.mark)
FROM
( SELECT mark FROM my_table ORDER BY mark DESC LIMIT 2 ) a;
+-------------+
| AVG(a.mark) |
+-------------+
| 90.0000 |
+-------------+