求一个SQL语句，谢谢~

[mjfei]

谢谢大家~

[mjfei]

引用 2 楼 hdhai9451 的回复:

select b.u_name as 姓名 , case when ISNULL(a.f_path,'')='' then '未上传' else '已上付' end as 查看上传文件, 'k_id='+cast(a.k_id as varchar) as 备注 from b_file a left join b_user b on a.u_id=b.u_id where a.k_id=1 and b.u_dept=1

不对呢，出来的结果没有“张三”

[mjfei]

其实我是想显示，同一部门人员在同一个绩效项目（比如：一季度绩效考核）下的维护情况列表（已维护、未维护），前提是要将同一部门人员列表出来。

[Andy__Huang]

你帖出你的测试数据出来

[shuyunn]

select b.* from b_user b join b_file f on b.u_id=f.f_id join kpi_list k on k.k_id=f.k_id where b.u_dept=1 and k.k_id=1

[Andy__Huang]

select b.u_name as 姓名 ,
case when ISNULL(a.f_path,'')='' then '未上传' else '已上付' end as 查看上传文件,
'k_id='+cast(a.k_id as varchar) as 备注
from b_file a
left join b_user b on a.u_id=b.u_id
where a.k_id=1 and b.u_dept=1

[mjfei]

引用 5 楼 hdhai9451 的回复:

你帖出你的测试数据出来

create table b_user(u_id nvarchar(50),u_name nvarchar(50), u_dept nvarchar(50)) insert into b_user values('1','张三','1') insert into b_user values('2','李四','1') insert into b_user values('3','王二','1') insert into b_user values('4','麻子','2') insert into b_user values('5','小明','1') create table b_file(f_id nvarchar(50),u_id nvarchar(50), f_path nvarchar(50),k_id nvarchar(50)) insert into b_file values('1','2','文件1','1') insert into b_file values('2','3','文件2','1') insert into b_file values('3','5','文件3','1') insert into b_file values('4','4','文件4','1') insert into b_file values('5','2','文件5','2') insert into b_file values('6','3','文件6','2') create table kpi_list(k_id nvarchar(50),k_title nvarchar(50)) insert into kpi_list values('1','一季度绩效考核') insert into kpi_list values('1','半年绩效考核')

[mjfei]

自己顶一下下

[Andy__Huang]

create table b_user(u_id nvarchar(50),u_name nvarchar(50), u_dept nvarchar(50))
insert into b_user values('1','张三','1')
insert into b_user values('2','李四','1')
insert into b_user values('3','王二','1')
insert into b_user values('4','麻子','2')
insert into b_user values('5','小明','1')
create table b_file(f_id nvarchar(50),u_id nvarchar(50), f_path nvarchar(50),k_id nvarchar(50))
insert into b_file values('1','2','文件1','1')
insert into b_file values('2','3','文件2','1')
insert into b_file values('3','5','文件3','1')
insert into b_file values('4','4','文件4','1')
insert into b_file values('5','2','文件5','2')
insert into b_file values('6','3','文件6','2')
create table kpi_list(k_id nvarchar(50),k_title nvarchar(50))
insert into kpi_list values('1','一季度绩效考核')
insert into kpi_list values('1','半年绩效考核')

select a.u_name as 姓名 ,
case when ISNULL(b.f_path,'')='' then '未上传' else '已上付' end as 查看上传文件,
'k_id='+cast(b.k_id as varchar) as 备注
from b_user a
left join b_file b on b.u_id=a.u_id and b.k_id=1 
where  a.u_dept=1

/*
姓名	查看上传文件	备注
张三	未上传	NULL
李四	已上付	k_id=1
王二	已上付	k_id=1
小明	已上付	k_id=1
*/

[北极海hein]

select a.u_id
,a.u_name as 姓名
,a.u_Dept as 部门
,case when b.f_path is null then '未上传' else '已上付' end as 查看上传文件
,'k_id='+cast(b.k_id as varchar) as 备注
from b_user a
left join b_file b
(on b.u_id=a.u_id)

[唐诗三百首]

create table b_user
(u_id nvarchar(50),u_name nvarchar(10), u_dept nvarchar(50))

insert into b_user values('1','张三','1')
insert into b_user values('2','李四','1')
insert into b_user values('3','王二','1')
insert into b_user values('4','麻子','2')
insert into b_user values('5','小明','1')

create table b_file
(f_id nvarchar(50),u_id nvarchar(50), f_path nvarchar(50),k_id nvarchar(10))

insert into b_file values('1','2','文件1','1')
insert into b_file values('2','3','文件2','1')
insert into b_file values('3','5','文件3','1')
insert into b_file values('4','4','文件4','1')
insert into b_file values('5','2','文件5','2')
insert into b_file values('6','3','文件6','2')

create table kpi_list
(k_id nvarchar(10),k_title nvarchar(50))

insert into kpi_list values('1','一季度绩效考核')
insert into kpi_list values('2','半年绩效考核')


select a.u_name '姓名',
       case when c.f_path is null then '未上传' else '已上传' end  '查看上传文件',
       'k_id='+b.k_id '备注'
from
(select u_id,u_name from b_user
 where u_dept='1') a
cross join
(select k_id from kpi_list
 where k_id='1') b
left join b_file c on a.u_id=c.u_id and c.k_id='1'

/*
姓名         查看上传文件        备注
----------  ---------------  ---------------
张三          未上传             k_id=1
李四          已上传             k_id=1
王二          已上传             k_id=1
小明          已上传             k_id=1

(4 row(s) affected)
*/