自动为每个主题添加升级标记 [英] automation of adding upgrade marks to each subject
问题描述
我的示例表包含示例数据
CREATE TABLE TEMP_GRACE
(
SYM NVARCHAR( 2),
ENG NVARCHAR(2),
MATHS NVARCHAR(2),
SCIENCE NVARCHAR(2),
GEO NVARCHAR(2)
);
插入temp_grace值('1','28','5','10','5');
插入temp_grace值('2','50','25','30','20');
插入temp_grace值('3','15','10','25','20');
插入temp_grace值('4','90','95','98','90');
插入temp_grace值('5','90','88','25','87');
在每个主题中,通过标记为30.我必须计算每个主题标记应添加多少UPGRADE NUMBER以使其成为30得到这样的UPGRADE NUMBER的总和 [TTL_UPGRADE]
每个符号编号。
如果每行总UPGRADE NUMBER小于60,我必须向主题添加所需的UPGRADE NUMBER。
我已经做了 SELECT
查询
这个条件,但我不知道我怎么能 添加/更新
这些
需要每个科目的UPGRADE NUMBER。
WITH CTE AS
(
SELECT
SYM,
ENG,
CASE WHEN A.ENG<>'AB'和TRY_CONVERT( NUMERIC(38,2),A.ENG)< 30.00
THEN 30.00 - TRY_CONVERT(NUMERIC(38,2),A.ENG)
ELSE 100 END AS [UP_ENG]
, MATHS
,例如A.MATHS<>'AB'和TRY_CONVERT(NUMERIC(38,2),A.MATHS)< 30.00
那么30.00 - TRY_CONVERT(NUMERIC(38,2)) ,A.MATHS)
ELSE 100结束[UP_MATHS]
,科学
,情况A.SCIENCE<>'AB'和TRY_CONVERT(NUMERIC(38,2),A .SCIENCE)< 30.00
那么30.00 - TRY_CONVERT(NUMERIC(38,2),A.SCIENCE)
ELSE 100结束为[UP_SCIENCE]
,GEO
,CASE WHEN A.GEO<>'AB'和TRY_CONVERT(NUMERIC (38,2),A.GEO)< 30.00
THEN 30.00 - TRY_CONVERT(NUMERIC(38,2),A.GEO)
ELSE 100 END AS [UP_GEO]
,(
(CASE WHEN A.ENG< >'AB'和TRY_CONVERT(NUMERIC(38,2),A.ENG)< 30.00
那么30.00 - TRY_CONVERT(NUMERIC(38,2),A.ENG)
ELSE 0 END) +
(例如A.MATHS<>'AB'和TRY_CONVERT(NUMERIC(38,2),A.MATHS)< 30.00
那么30.00 - TRY_CONVERT(NUMERIC(38,2)) ,A.MATHS)
ELSE 0 END)+
(A.SCIENCE<>'AB'和TRY_CONVERT(NUMERIC(38,2),A.SCIENCE)的情况下< 30.00
THEN 30.00 - TRY_CONVERT(NUMERIC(38,2),A.SCIENCE)
ELSE 0 END)+
(例如A.GEO<>'AB'和TRY_CONVERT(NUMERIC(38) ,2),A.GEO)< 30.00
那么30.00 - TRY_CONVERT(NUMERIC(38,2),A.GEO)
ELSE 0 END)
)AS [TTL_UPGRADE]
FROM [DBO]。[TEMP_GRACE] A
)
SELECT SYM,ENG,UP_ENG,MATHS,UP_MATHS,SCIENCE,UP_SCIENCE,GEO,UP_GEO,TTL_UPGRADE FROM CTE
WHERE TTL_UPGRADE< 60和TTL_UPGRADE> 0订购TTL_UPGRADE DESC;
基于上面的过滤查询,我必须将UPGRADE NUMBER添加到 sym
行。
现在如何更新这些行?
SYM ENG UP_ENG MATHS UP_MATHS SCIENCE UP_SCIENCE GEO UP_GEO TTL_UPGRADE
3 15 15.00 10 20.00 25 5.00 20 10.00 50.00
2 50 100.00 25 5.00 30 100.00 20 10.00 15.00
5 90 100.00 88 100.00 25 5.00 87 100.00 5.00
在第一行中,我必须在ENG中添加15,在MATHS中添加20在科学中有5个,在GEO中有10个,同样在第二行我必须在MATHS中添加5个,在GEO中添加10个,在第三个
行中我必须在科学中添加5个。如何自动执行添加UPGRADE MARKS的过程?请帮助!!!
如果你只是想用一个简单的更新声明更新表TEMP_GRACE,请在下面查询将起作用:
更新TEMP_GRACE
设置eng = case当eng< = 30然后30 else eng end,
maths =数学时的情况< = 30然后30其他数学结束,
科学=科学时的情况< = 30然后30其他科学结束,
geo = geo< = 30时的情况然后30其他geo结束
其中
(例如当eng< = 30然后30-eng else 0结束+
情况下数学< = 30然后30-数学其他0结束+
当科学< = 30然后30-科学其他0结束+
情况当geo< = 30然后30-geo else 0结束时)1到60之间;
;更新后的输出:
SYM ENG MATHS SCIENCE GEO
---- ---- ----- ------- ----
1 28 5 10 5
2 50 30 30 30
3 30 30 30 30
4 90 95 98 90
5 90 88 30 87
My sample table with sample data
CREATE TABLE TEMP_GRACE ( SYM NVARCHAR(2), ENG NVARCHAR(2), MATHS NVARCHAR(2), SCIENCE NVARCHAR(2), GEO NVARCHAR(2) ); insert into temp_grace values ('1','28','5','10','5'); insert into temp_grace values ('2','50','25','30','20'); insert into temp_grace values ('3','15','10','25','20'); insert into temp_grace values ('4','90','95','98','90'); insert into temp_grace values ('5','90','88','25','87');
In every subject, the pass mark is 30. I have to calculate how much UPGRADE NUMBER should be added to each subject marks to make it 30 then get the sum of such UPGRADE NUMBER [TTL_UPGRADE]
for
every symbol numbers.
I have to add the required UPGRADE NUMBER to the subjects if total UPGRADE NUMBER is less than 60 for every row.
I have made the SELECT
query
for this condition but I have no idea how can I add/update
those
required UPGRADE NUMBER to each subjects.
WITH CTE AS ( SELECT SYM, ENG, CASE WHEN A.ENG <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.ENG) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.ENG) ELSE 100 END AS [UP_ENG] ,MATHS ,CASE WHEN A.MATHS <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.MATHS) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.MATHS) ELSE 100 END AS [UP_MATHS] ,SCIENCE ,CASE WHEN A.SCIENCE <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.SCIENCE) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.SCIENCE) ELSE 100 END AS [UP_SCIENCE] ,GEO ,CASE WHEN A.GEO <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.GEO) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.GEO) ELSE 100 END AS [UP_GEO] ,( (CASE WHEN A.ENG <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.ENG) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.ENG) ELSE 0 END) + (CASE WHEN A.MATHS <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.MATHS) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.MATHS) ELSE 0 END) + (CASE WHEN A.SCIENCE <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.SCIENCE) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.SCIENCE) ELSE 0 END) + (CASE WHEN A.GEO <> 'AB' AND TRY_CONVERT(NUMERIC(38, 2), A.GEO) < 30.00 THEN 30.00 - TRY_CONVERT(NUMERIC(38, 2), A.GEO) ELSE 0 END) ) AS [TTL_UPGRADE] FROM [DBO].[TEMP_GRACE] A ) SELECT SYM, ENG, UP_ENG, MATHS, UP_MATHS, SCIENCE, UP_SCIENCE, GEO, UP_GEO, TTL_UPGRADE FROM CTE WHERE TTL_UPGRADE < 60 AND TTL_UPGRADE > 0 ORDER BY TTL_UPGRADE DESC;
Based on the above filter query, I have to add UPGRADE NUMBER to 3 of the sym
rows.
Now how to update such rows?
SYM ENG UP_ENG MATHS UP_MATHS SCIENCE UP_SCIENCE GEO UP_GEO TTL_UPGRADE 3 15 15.00 10 20.00 25 5.00 20 10.00 50.00 2 50 100.00 25 5.00 30 100.00 20 10.00 15.00 5 90 100.00 88 100.00 25 5.00 87 100.00 5.00
In the first row I have to add 15 in ENG, 20 in MATHS, 5 in SCIENCE and 10 in GEO, similarly for second row I have to add 5 in MATHS , 10 in GEO and for third
row I have to add 5 in SCIENCE. How can I automate this process of adding UPGRADE MARKS? Please help!!!
In case you just want to update table TEMP_GRACE, with a simple update statement, below query will work:
update TEMP_GRACE set eng = case when eng <= 30 then 30 else eng end, maths = case when maths <= 30 then 30 else maths end, science = case when science <= 30 then 30 else science end, geo = case when geo <= 30 then 30 else geo end where (case when eng <= 30 then 30-eng else 0 end + case when maths <= 30 then 30-maths else 0 end + case when science <= 30 then 30-science else 0 end + case when geo <= 30 then 30-geo else 0 end) between 1 and 60; ;Output after update:
SYM ENG MATHS SCIENCE GEO ---- ---- ----- ------- ---- 1 28 5 10 5 2 50 30 30 30 3 30 30 30 30 4 90 95 98 90 5 90 88 30 87
这篇关于自动为每个主题添加升级标记的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!