加入两个SQL查询 [英] joining two sql queries

问题描述

首先查询如下

First query as follows

select cmn_minor_code as Course_Name, convert(char,cbm_batch_start_dt,106) as Course_date, cbm_batch_id as Batch_ID
from co_batch_master
where cbm_active <. 'd'
and cbm_batch_start_dt between convert(datetime,'06-Jul-2015',6)
and convert(datetime,'06-Jul-2015',6)  order by cbm_batch_start_dt

我按如下方式运行第一个查询输出

When i run the first query output as follows

Course_Name   Course_date            Batchid

 AFF            12   jun 2015          B8753

第二次查询如下

Second query as follows

select count(*)  as No_of_students
from student s,course_registration cr,batch_course_registration bcr,co_batch_master cbm where cr.stud_id = s.stud_id and cr.cr_bill_no = bcr.cr_bill_no and bcr.bcr_batch_id = cbm.cbm_batch_id and cbm.cbm_active <> 'D' and cr.cr_active = 'A'  and s.stud_active <>'D' and cbm_batch_id = 'B8753'

当我跑步时第二个查询输出如下

When i run the second query output as follows

No_of_students  
24

我想结合第一个查询和我想要的第二个查询和输出如下

I want to combine the first query and second query and output i want as follows

Course_Name   Course_date  Batchid    No_of_students

 AFF          12 jun 2015   B8753        24

为什么我如何结合上面的第一个和第二个查询并在sql server中获取输出。



[edit]已添加代码块 - OriginalGriff [/ edit]

for that how can i combine the above first and second query and get the output in sql server.

[edit]Code block added - OriginalGriff[/edit]

解决方案

如果我理解你的问题，那么你就是在 GROUP BY [ ^ ]条款。



如果您接受第一个查询并将表连接到第二个表中，然后按所有非聚合列分组，我相信我们已经接近了到你想要的。所有条件都需要合并到同一个语句中。



所以可能是这样的：

If I understood your question correctly, you're after GROUP BY[^] clause.

If you take the first query and join the tables to the tables in the second and then group by all non-aggregated columns, I believe that we're getting close to what you want. Also all of the conditions need to be merged into the same statement.

So perhaps something like this:

SELECT  cbm.cmn_minor_code as Course_Name, 
        CONVERT(char,cbm.cbm_batch_start_dt,106) as Course_date, 
        cbm.cbm_batch_id as Batch_ID,
        count(*)  as No_of_students
FROM co_batch_master cbm,
     student s,
     course_registration cr,
     batch_course_registration bcr
WHERE cbm.cbm_active   < 'd'
AND   cbm.cbm_batch_start_dt 
      BETWEEN CONVERT(datetime,'06-Jul-2015',6) 
      AND     CONVERT(datetime,'06-Jul-2015',6)  
AND   cr.stud_id       = s.stud_id 
AND   cr.cr_bill_no    = bcr.cr_bill_no 
AND   bcr.bcr_batch_id = cbm.cbm_batch_id 
AND   cbm.cbm_active   <> 'D' 
AND   cr.cr_active     = 'A'  
AND   s.stud_active    <>'D' 
AND   cbm_batch_id     = 'B8753'
GROUP BY cbm.cmn_minor_code, 
        CONVERT(char,cbm.cbm_batch_start_dt,106),
        cbm.cbm_batch_id
ORDER BY cbm_batch_start_dt

请注意，由于我不知道，我真的不确定连接和其他条件回合你的表结构或逻辑。但是，这应该让你开始。

Note that I'm really not sure about the joins nor the other conditions since I have no idea about your table structure or logic. However, this should get you started.

这篇关于加入两个SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！