sql查询以获取特定字段并分配值 [英] sql query to fetch particular field and assign value
问题描述
大家好,有人可以建议我..
我正在创建excel报告,我必须写一些查询。
我正在计算运动日获胜者。看到那5个参与者。在那里,他们得到了分数
我将这个ParticipantsName,ScoredMarks字段存储在数据库的tblScoredcards表中。
ParticipantsName ScoredMarks
sam 50
vin 25
pra 75
moham 15
vins 35
看我必须分配前四个分配标记是48,36,24 ,12
ie
ScoredMarks AssignedMarks
75 48 ----->第一名
50 36 ----->第二名
35 24 ----->第三名
25 12 ----->第四名
如何使用C#在特定字段中分配标记?
我尝试了arraylist。
[edit]已添加代码块 - OriginalGriff [/ edit]
测试它:
DECLARE @ scoredcards TABLE (ParticipantsName VARCHAR ( 30 ),ScoredMarks INT )
INSERT INTO @ scoredcards (ParticipantsName,ScoredMarks)
VALUES (' sam', 50 )
INSERT INTO @scoredcards (ParticipantsName,ScoredMarks)
VALUES (' vin', 25 )
INSERT INTO @ scoredcards (ParticipantsName,ScoredMarks)
VALUES (' pra', 75 )
INSERT INTO @scoredcards (ParticipantsName,ScoredMarks)
VALUES (' moham', 15 )
INSERT INTO @ scoredcards (ParticipantsName,ScoredMarks)
VALUES (' vins' , 35 )
DECLARE @assignedmarks 表(放置 INT ,AssignedMarks INT )
INSERT INTO @assignedmarks (Place,AssignedMarks)
VALUES ( 1 , 48 )
INSERT INTO @ assignedmarks (Place,AssignedMarks)
VALUES ( 2 , 36 )
INSERT INTO @ assignedmarks (地方, AssignedMarks)
VALUES ( 3 , 24 )
INSERT INTO @ assignedmarks (Place,AssignedMarks)
VALUES ( 4 , 12 )
SELECT A.Place,A.ParticipantsName,A.ScoredMarks,B.AssignedMarks
FROM (
SELECT ROW_NUMBER() OVER ( ORDER BY ScoredMarks DESC ) AS 地方,*
FROM @ scoredcards
) AS A RIGHT JOIN (
SELECT Place,AssignedMarks
FROM @ assignedmarks ) AS B ON A.Place = B.Place
结果:
Place Part ... Scor ...分配...
1 pra 75 48
2 sam 50 36
3 vins 35 24
4 vin 25 12
SELECT A.Place,A.ParticipantsName,A.ScoredMarks,B.AssignedMarks
FROM 跨度> (
SELECT RANK() OVER ( ORDER BY ScoredMarks DESC ) AS Place,*
FROM @ scoredcards
) AS A RIGHT JOIN (
SELECT Place,AssignedMarks
FROM @ assignedmarks )< span class =code-keyword> AS B ON A.Place = B.Place
WHERE NOT A.Place IS NULL 跨度>
Hi guys anyone can suggest me..
I''m creating excel report for that i have to write some query.
I''m calculating sports day winner. see in that 5 participants. in that, they got marks
I stored this ParticipantsName, ScoredMarks field in tblScoredcards table in database.
ParticipantsName ScoredMarks
sam 50
vin 25
pra 75
moham 15
vins 35
see i have to assign first four place assigned mark is 48,36,24,12
i.e
ScoredMarks AssignedMarks
75 48-----> 1st Place
50 36----->2st Place
35 24----->3rd Place
25 12----->4rd Place
How to assign marks in particular field using C#?
I tryed arraylist.
[edit]Code block added - OriginalGriff[/edit]
Test it:
DECLARE @scoredcards TABLE (ParticipantsName VARCHAR(30), ScoredMarks INT) INSERT INTO @scoredcards (ParticipantsName, ScoredMarks) VALUES('sam', 50) INSERT INTO @scoredcards (ParticipantsName, ScoredMarks) VALUES('vin', 25) INSERT INTO @scoredcards (ParticipantsName, ScoredMarks) VALUES('pra', 75) INSERT INTO @scoredcards (ParticipantsName, ScoredMarks) VALUES('moham', 15) INSERT INTO @scoredcards (ParticipantsName, ScoredMarks) VALUES('vins', 35) DECLARE @assignedmarks TABLE (Place INT, AssignedMarks INT) INSERT INTO @assignedmarks (Place, AssignedMarks) VALUES(1, 48) INSERT INTO @assignedmarks (Place, AssignedMarks) VALUES(2, 36) INSERT INTO @assignedmarks (Place, AssignedMarks) VALUES(3, 24) INSERT INTO @assignedmarks (Place, AssignedMarks) VALUES(4, 12) SELECT A.Place, A.ParticipantsName, A.ScoredMarks, B.AssignedMarks FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ScoredMarks DESC) AS Place, * FROM @scoredcards ) AS A RIGHT JOIN ( SELECT Place, AssignedMarks FROM @assignedmarks) AS B ON A.Place=B.Place
Result:
Place Part... Scor... Assign... 1 pra 75 48 2 sam 50 36 3 vins 35 24 4 vin 25 12
[EDIT #1]
SELECT A.Place, A.ParticipantsName, A.ScoredMarks, B.AssignedMarks FROM ( SELECT RANK() OVER (ORDER BY ScoredMarks DESC) AS Place, * FROM @scoredcards ) AS A RIGHT JOIN ( SELECT Place, AssignedMarks FROM @assignedmarks) AS B ON A.Place=B.Place WHERE NOT A.Place IS NULL
这篇关于sql查询以获取特定字段并分配值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!