SELECT s.NAME,s.class,s.age,s.sex,s2.[SUBJECT],s2.score, --CASE s.credit WHEN s.credit<10 THEN s.credit+8 --WHEN s.credit>=10 THEN s.credit+5 CASE WHEN s.age<10 THEN s.credit+8 WHEN s.age>=10 THEN s.credit+5 END AS 学分 FROM dbo.Student AS s left JOIN dbo.Score AS s2 ON s.NAME=s2.NAME
--CASE s.credit WHEN s.credit<10 THEN s.credit+8
--WHEN s.credit>=10 THEN s.credit+5
CASE
WHEN s.age<10
THEN s.credit+8
WHEN s.age>=10
THEN s.credit+5
END AS 学分
FROM dbo.Student AS s
left JOIN dbo.Score AS s2
ON s.NAME=s2.NAME
如果只是显示数据,直接关联读取就可以了,用case when做盘。
如果是想把学分作为一列,每次自动修改填充,可以用触发器修改。
ALTER TABLE dbo.Student ADD [学分] AS
CASE WHEN age<10 THEN credit+8
WHEN age>=10 THEN credit+5
END
CASE WHEN age<10 THEN credit+8
WHEN age>=10 THEN credit+5
END