错了点,改下Select A.name,Count(B.job_id) As 工作量 from yuangong_datum A Left Join job_report B On A.user_name=B.user_name Group By A.name
Select min(A.name) as name ,Count(isnull(B.job_id,0)) As 工作量 from yuangong_datum A Left Join job_report B On A.user_name=B.user_name Group By A.user_name
--建立测试环境 Create Table yuangong_datum ([user_name] Varchar(10), name Nvarchar(10))Create Table job_report (job_id Int, [user_name] Varchar(10)) --插入数据 Insert yuangong_datum Values('xie', N'小谢') Insert yuangong_datum Values('wu', N'小吴') Insert yuangong_datum Values('li', N'小李')Insert job_report Values(1, 'xie') Insert job_report Values(1, 'wu') Insert job_report Values(2, 'wu') Insert job_report Values(3, 'xie') --测试 Select A.name,Count(B.job_id) As 工作量 from yuangong_datum A Left Join job_report B On A.user_name=B.user_name Group By A.name --删除测试环境 Drop Table yuangong_datum,job_report --结果 /* name 工作量 小吴 2 小李 0 小谢 2 */
Select A.name,Count(B.job_id) As 工作量 from yuangong_datum A Left Join job_report B On A.user_name=B.user_name Group By A.name Order By 工作量即可
from yuangong_datum A
Left Join job_report B
On A.user_name=B.user_name
Group By A.name
from yuangong_datum A
Left Join job_report B
On A.user_name=B.user_name
Group By A.user_name
Create Table yuangong_datum
([user_name] Varchar(10),
name Nvarchar(10))Create Table job_report
(job_id Int,
[user_name] Varchar(10))
--插入数据
Insert yuangong_datum Values('xie', N'小谢')
Insert yuangong_datum Values('wu', N'小吴')
Insert yuangong_datum Values('li', N'小李')Insert job_report Values(1, 'xie')
Insert job_report Values(1, 'wu')
Insert job_report Values(2, 'wu')
Insert job_report Values(3, 'xie')
--测试
Select A.name,Count(B.job_id) As 工作量
from yuangong_datum A
Left Join job_report B
On A.user_name=B.user_name
Group By A.name
--删除测试环境
Drop Table yuangong_datum,job_report
--结果
/*
name 工作量
小吴 2
小李 0
小谢 2
*/
from yuangong_datum A
Left Join job_report B
On A.user_name=B.user_name
Group By A.name
Order By 工作量即可