大家好,有人可以建议我.. 我正在创建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 12SELECT 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
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 35see 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 PlaceHow 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.PlaceResult:
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查询以获取特定字段并分配值
发布评论