如何在mysql中使用sql join [英] how to use sql join in mysql

问题描述

表名称:tbl_schedule

tr_id(P.K.)  mr_id(F.K.)   sch_date   doctor_id   
-----------  -----------   --------   ----------
1              23         01/01/2012    32
2              23         05/01/2012    13
3              22         08/01/2012    14

表名:tbl_user

mr_id(P.K.)  mr_fname      mr_lname     
-----------  -----------   --------  
23            Manish        malviya    
24            chandan       gerry    
22            jacky         chen 

请回复查询谢谢 我想使用mr_fname, mr_lname group by mr_id获取两个日期之间的mr数 它应该看起来像.此计数是从tbl_schedule表中获得的，而mr_fnmae和mr_lname是从tbl_user引用mr_id获取的.

我不想再计数0的另一件事

mr_fname      mr_lname   count  
-----------   --------  -------
Manish        malviya    2
jacky         chen       1

解决方案

    select tu.mr_fname, tu.mr_lname, count(ts.mr_id) as `count` 
from tbl_user tu 
inner join tbl_schedule ts on ts.mr_id = tu.mr_id and 
      ts.sch_date between '2012-01-01' and '2012-08-01' 
group by tu.mr_id

以上内容将使您在计划表中的所有用户都排着一行.因此，计数为0的用户将不会出现.

select tu.mr_fname, tu.mr_lname, count(ts.mr_id) as `count` 
from tbl_user tu 
left join tbl_schedule ts on ts.mr_id = tu.mr_id and 
      ts.sch_date between '2012-01-01' and '2012-08-01' 
group by tu.mr_id

此查询将选择用户的名字和姓氏，并计算用户ID在计划表中出现的次数.

它通过按用户ID分组来实现.

此外，它还根据用户ID和日期使用左联接.重要的是在此处放置日期条件，以便选择所有用户.这是因为左联接将包括所有不匹配的用户.但是，如果将其放在where子句中，则不会选择所有用户.换句话说，如果将日期条件放在where子句中，则"gerry chandan"将不会获得零计数.取而代之的是，他将被排除在结果之外.

table name: tbl_schedule

tr_id(P.K.)  mr_id(F.K.)   sch_date   doctor_id   
-----------  -----------   --------   ----------
1              23         01/01/2012    32
2              23         05/01/2012    13
3              22         08/01/2012    14

Table name: tbl_user

mr_id(P.K.)  mr_fname      mr_lname     
-----------  -----------   --------  
23            Manish        malviya    
24            chandan       gerry    
22            jacky         chen 

Please reply with query thanks i want to get number of mr between two dates with mr_fname, mr_lname group by mr_id It should look like. in this counting is from tbl_schedule table and mr_fnmae and mr_lname are fetched from tbl_user with reference of mr_id.

one more thing i dont want 0 count

mr_fname      mr_lname   count  
-----------   --------  -------
Manish        malviya    2
jacky         chen       1

解决方案

    select tu.mr_fname, tu.mr_lname, count(ts.mr_id) as `count` 
from tbl_user tu 
inner join tbl_schedule ts on ts.mr_id = tu.mr_id and 
      ts.sch_date between '2012-01-01' and '2012-08-01' 
group by tu.mr_id

The above will get you all the users with a row in the schedule it. So the users with a count of 0 will not show up.

select tu.mr_fname, tu.mr_lname, count(ts.mr_id) as `count` 
from tbl_user tu 
left join tbl_schedule ts on ts.mr_id = tu.mr_id and 
      ts.sch_date between '2012-01-01' and '2012-08-01' 
group by tu.mr_id

This query will select the first name and last name of the user and count the number of times that user id appears in the schedule table.

It does this by grouping by the user id.

And additionally it uses a left join based on user id and date. It is important to put the date condition here so that all users are selected. This is because the left join will include all users that did not match too. But if you put this on the where clause, all users would not be selected. In other words, you will not get a count of zero for 'gerry chandan' if you put the date condition in the where clause. Instead he would be left off the results.

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