如何获得以下输出 [英] how to get the below output

问题描述

我的查询如下

 选择 fa.facname， f.bfid，b.class，b.examdate 来自 bthfac f，批次b，faculty fa 其中月（b.examdate）= '  11' 和 year（b.examdate）= '  2012' 和 b.bthid = f.bthid 和 fa.facname = '  MADHAVAN' 
 

当我按如下方式执行上述查询输出时;

 Facname Bfid class examdate 
 MADHAVAN 542 RM 2012-11-01 
 MADHAVAN 543 CTF 2012-11-02 
 MADHAVAN 544 PSCRB 2012-11-02 

facfeedback表如下;

 Bf id Rate 
 542 1 
 543 2 
 544 3 
 

使用以上查询和反馈表我希望得到如下输出：

<前lang =文本> Facname Bfid类考试率
MADHAVAN 542 RM 2012-11-01 1
MADHAVAN 543 CTF 2012-11-02 2
MADHAVAN 544 PSCRB 2012-11-02 3

为了得到上面的输出我怎么能写出查询。



请帮帮我



问候，

Narasiman P.

解决方案

这个想法是这样的：

 选择 
 fa.facname 
，f.bfid 
，b.class 
，b.examdate 
 来自 
 bthfac f 
  inner   join  batch b  on  b.bthid = f.bthid 
 内部  join  facfeedback fb  on  fb.bfid = f.bfid 
  内部 加入 faculty fa  on  fa。???? = ??。????  
 其中 
 month（b.examdate）= < span class =code-string>'  11' 
 和年（b.examdate）= '  2012' 
 和 fa.facname = '  MADHAVAN' 

但请注意，您从 faculty 表中选择，而不加入其他表。这对我来说似乎是一个问题。但由于我不知道桌子之间的关系，我不能建议你更多。当然，除了内连接之外你可能还需要其他东西，但这更有可能发生。当您从没有连接的表中选择或者在条件中选择您生成笛卡尔积的条件时 - 在这种情况下您不太可能需要类似的东西。

My query as follows

select fa.facname,f.bfid,b.class,b.examdate from bthfac f,batch b,faculty fa where month(b.examdate)= '11' and  year(b.examdate)= '2012'  and b.bthid=f.bthid and fa.facname = 'MADHAVAN'

when i execute the above query output as follows;

Facname         Bfid    class     examdate
MADHAVAN	542	RM	2012-11-01 
MADHAVAN	543	CTF	2012-11-02 
MADHAVAN	544	PSCRB	2012-11-02

facfeedback table as follows;

Bfid   Rate
 542    1
 543    2
 544    3

using the above query and facfeedback table i want to get thebelow output as follows;

Facname         Bfid  class     examdate    rate
MADHAVAN   542 RM  2012-11-01    1
MADHAVAN   543 CTF 2012-11-02    2
MADHAVAN   544 PSCRB   2012-11-02    3

for getting the above output how can i write the query.

please help me

Regards,
Narasiman P.

解决方案

The idea is this:

select 
 fa.facname
,f.bfid
,b.class
,b.examdate 
from 
bthfac f
inner join batch b on b.bthid = f.bthid
inner join facfeedback fb on fb.bfid=f.bfid
inner join faculty fa on fa.????=??.????
where 
 month(b.examdate)= '11' 
 and year(b.examdate)= '2012' 
 and fa.facname = 'MADHAVAN'

But be aware, that you select from faculty table without any join to the other tables. That look like a problem to me. But as I don''t know the relations between the tables, I can not suggest you more. Of course, it might happen that you need something else than inner join, but that is more likely. When you select from a table without join or equivalent where condition you produce a Cartesian product - and it is unlikely that you need something like that in this situation.

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