如何在sql server中获取所需的输出 [英] How to get the required output in sql server

问题描述

select  distinct fa.facname,r.rate,f.bfid,b.examdate from bthfac f,batch b,faculty fa,facfeedback r where month(b.examdate)= '9' and  year(b.examdate)= '2011' and b.bthid=f.bthid and  r.bfid = f.bfid and  fa.facname = 'MADHAVAN'

当我执行上述查询时，我得到以下输出如下



Facname Rate Bfid Examdate

MADHAVAN 4 17 2011-09-29

MADHAVAN 1 3 2011-09-24

MADHAVAN 3 12 2011-09-2 8

MADHAVAN 2 4 2011-09-24



facfeedback表如下



Bfid Rate



17 4

3 1

12 3

4 2





查询如下

when i exeucte the above query i get the below output as follows

Facname Rate Bfid Examdate
MADHAVAN 4 17 2011-09-29
MADHAVAN 1 3 2011-09-24
MADHAVAN 3 12 2011-09-28
MADHAVAN 2 4 2011-09-24

facfeedback table as follows

Bfid Rate

17 4
3 1
12 3
4 2


query as follows

select Rating =  ((case rate when '1' then 100 when '2' then 75 when '3' then 50 when '4' then 25 when '0' then 0 end))  from facfeedback

上面使用的查询如果费率1表示100，则表示速率目的，2表示75,3表示50表示意味着25表示25.





使用我的上述查询我希望输出如下





Facname Rate Bfid Examdate

MADHAVAN 25 17 2011-09-29

MADHAVAN 100 3 2011-09-24

MADHAVAN 50 12 2011-09-28

MADHAVAN 75 4 2011-09-24





如何才能获得上述输出。



请帮助我



Reg ards，

Narasiman P.

the above query used for rate purpose if Rate 1 means 100, 2 means 75,3 means 50 and 4 means 25.


using my above above query i want the output as follows


Facname Rate Bfid Examdate
MADHAVAN 25 17 2011-09-29
MADHAVAN 100 3 2011-09-24
MADHAVAN 50 12 2011-09-28
MADHAVAN 75 4 2011-09-24


how can i get the above output.

please help me

Regards,
Narasiman P.

推荐答案

你正在使用被认为是遗产加入的东西，这对我个人来说我不喜欢和我相信或多或少被认为已被弃用。所以我给你两个查询选项。



1）使用你的orignial查询（旧版连接）

You are using what is considered a legacy join, which for me personally i do not like and i believe are more or less considered deprecated. So i''ve given you two query options.

1) Using your orignial query (legacy joins)

SELECT 
	fa.facname,
	r.rate,
	f.bfid,
	b.examdate,
	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 TranslatedRate
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.facname = 'MADHAVAN'

2）使用我重新编写的查询（未经测试，但它应该有效......我没有你的DB /表/架构，我感觉不到喜欢复制它）。

2) Using the query i re-wrote (not tested but it "should" work...i don''t have your DB/Table/Schema and i didn''t feel like replicating it).

SELECT
    C.facname,
    D.rate,
    A.bfid,
    B.examdate,
    CASE
        WHEN D.rate = '1' THEN '100'
        WHEN D.rate = '2' THEN '75'
        WHEN D.rate = '3' THEN '50'
        WHEN D.rate = '4' THEN '25'
        ELSE '0' END AS TranslatedRate
FROM bthfac AS A
JOIN batch AS B ON B.bthid = A.bthid
JOIN faculty AS C ON C.facname = 'MADHAVAN'
JOIN facfeedback AS D ON D.bfid = A.bfid
WHERE MONTH(B.examdate) = 9 AND YEAR(B.examdate) = 2011
GROUP BY C.facname, D.rate, A.bfid, B.examdate

所以第一个查询，使用您的原始，应该为您提供您想要的输出...提供我正确理解您的问题。

So the first query, using your orginal, should provide you the output that you are desiring...providing i understood your question correctly.

这篇关于如何在sql server中获取所需的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！