当我执行查询输出时，在sql server中没有正确获取 [英] When i execute the query output is not getting correctly in sql server

问题描述

教师表记录如下： -



Facid Faculty



1 MADHAVAN

2 Sekar

3 John

4 vinoth

5 Ramesh



批表记录如下



Bthid Examdate



1 14/9/2011

2 24/10/2011

3 9/12/2011

4 12/10/2012

5 14 / 9/2011



Bthfac表记录如下



Bfid Bthid facid



1 1 1,2

2 2 2,5

3 3 3,4

4 4 1,5

5 5 4,5



Facfeedback table记录如下



Bfid Rate



1 1

2 2

3 3

4 4

5 5



使用上面的四个表我写下面的查询如下;

  SELECT   distinct  r.rate < span class =code-keyword> as  Rate，f.bthid  as  Batchid，
  CASE  
  WHEN  r.rate = '  1'  THEN  '  100 ' 
  WHEN  r.rate = '  2'  THEN  '  75 ' 
  WHEN  r.rate = '  3' 那么 '  50 ' 
  WHEN  r.rate = '  4' 那么 '  25 ' 
  ELSE  '  0 '  END   AS  Translated_Rate 
  FROM  bthfac  AS  f，批次 AS  b，faculty  AS  fa，facfeedback  AS  r 
 其中​​月（b.examdate ）= '  9' 和年（ b.examdate）= '  2011' 和 b.bthid = f .bthid 和 r.bfid = f.bfid 和 fa.Faculty = '  MADHAVAN'; 

使用以上四个表我得到如下输出： -



FacName费率bthid



MADHAVAN 1 1

MADHAVAN 5 5



当我为不同的教师执行上述sql查询时，我得到了所有教师的上述输出。



我上面的SQL查询有什么问题。



我该怎么办？请帮助我。



问候，

Narasiman P.

解决方案

试试这个...

  SELECT   distinct  r.rate  as  Rate，f.bthid  as  Batchid，
  CASE  
  WHEN  r.rate = '  1' 那么 '  100' 
  WHEN  r.rate = '  2' 那么 '  75' 
  WHEN  r.rate = '  3' 那么 '  50 ' 
  WHEN  r.rate = '  4' 那么 '  25 ' 
  ELSE  '  0 '  END   AS  Translated_Rate 
  FROM  bthfac f 
  INNER   JOIN  faculty fa  ON  fa.Facid = f.Facid 
  INNER   JOIN 批b  ON  b.Bthid = f.Bthid 
  INNER   JOIN  facfeedback r  ON  r.Bfid = b.Bthid 
 其中月（b.examdate ）= '  9' 和年（ b.examdate）= '  2011' 和 b.bthid = f.bthid 和 r.bfid = f.bfid 和 fa.Faculty = < span class =code-string>'  MADHAVAN'; 

GVPrabu

Faculty table records as follows:-

Facid Faculty

1 MADHAVAN
2 Sekar
3 John
4 vinoth
5 Ramesh

Batch table records as follows

Bthid Examdate

1 14/9/2011
2 24/10/2011
3 9/12/2011
4 12/10/2012
5 14/9/2011

Bthfac table records as follows

Bfid Bthid facid

1 1 1,2
2 2 2,5
3 3 3,4
4 4 1,5
5 5 4,5

Facfeedback table records as follows

Bfid Rate

1 1
2 2
3 3
4 4
5 5

Using the above four tables i written the below query as follows;

SELECT distinct r.rate as Rate,f.bthid as Batchid,
CASE  
 WHEN r.rate = '1' THEN '100' 
 WHEN r.rate = '2' THEN '75' 	
 WHEN r.rate = '3' THEN '50' 
 WHEN r.rate = '4' THEN '25' 
 ELSE '0' END AS Translated_Rate 
FROM bthfac AS f,batch AS b, faculty AS fa,facfeedback AS r 
where  month(b.examdate)= '9' and year(b.examdate)= '2011' and b.bthid=f.bthid and r.bfid = f.bfid and fa.Faculty = 'MADHAVAN';

Using the above four table i get the output as follows :-

FacName Rate bthid

MADHAVAN 1 1
MADHAVAN 5 5

When I execute the above sql query for different Faculty, I get the the above output for all faculty.

What is the problem in my above sql query.

How can I do? Please help me.

Regards,
Narasiman P.

解决方案

Hi,

Try this...

SELECT distinct r.rate as Rate,f.bthid as Batchid,
CASE  
 WHEN r.rate = '1' THEN '100' 
 WHEN r.rate = '2' THEN '75' 	
 WHEN r.rate = '3' THEN '50' 
 WHEN r.rate = '4' THEN '25' 
 ELSE '0' END AS Translated_Rate 
FROM bthfac f
INNER JOIN faculty fa ON fa.Facid=f.Facid 
INNER JOIN batch b ON b.Bthid=f.Bthid 
INNER JOIN facfeedback r ON r.Bfid=b.Bthid 
where  month(b.examdate)= '9' and year(b.examdate)= '2011' and b.bthid=f.bthid and r.bfid = f.bfid and fa.Faculty = 'MADHAVAN';

GVPrabu

这篇关于当我执行查询输出时，在sql server中没有正确获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！