最基本的交叉表查詢啊! select ZBMdata.baomingNum,ZBMdata.姓名,ZBMdata.性别,KM.科目名称,KM.费用,ZBMrmb.考核日期 from ZBMdata inner join ZBMrmb on ZBMdata.baomingNum=ZBMrmb.baomingNum inner join KM on ZBMrmb.Kmnum=KM.Kmnum
得杂查询我都是这样做的:在ACCESS里用向导按要求做好,用其生成的SQL语句。试试吧!
这样的查询用MSSQLServer就很容易实现了,Access好像不可以,应为不能执行动态语句
select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b先写一半
写完了,楼主记得给分啊select ZBMdata.BaoMingNum,ZBMdata.姓名,ZBMdata.性别, case when d.办公自动化 ='KM01' then 80 else 0 end as 办公自动化, case when d.多媒体技术 ='KM02' then 100 else 0 end as 多媒体技术,d.考核日期 from ZBMdata, (select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') as 办公自动化 ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') as 多媒体技术 from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b) as d where d.BaoMingNum=ZBMdata.BaoMingNum
以下是access版的 access里sql数据查询最后带分号的,vb里就可以去掉了 但where那里出了点问题,我再看看SELECT ZBMdata.BaoMingNum, ZBMdata.姓名, ZBMdata.性别, IIf(d.办公自动化 ='KM01', "80", "0") AS 办公自动化, IIf(d.多媒体技术 ='KM02', "100", "0") AS 多媒体技术, d.考核日期 FROM ZBMdata, [select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') as 办公自动化 ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') as 多媒体技术 from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b]. AS d WHERE d.BaoMingNum=ZBMdata.BaoMingNum;
去掉where,前面加上d.BaoMingNum时,可以看出数据正确 d.BaoMingNum,ZBMdata.BaoMingNum这两列冗余,可以看出 d.BaoMingNum=ZBMdata.BaoMingNum时是正确的结果,但是加上where后结果就不对了。 原因不明。SELECT d.BaoMingNum,ZBMdata.BaoMingNum, ZBMdata.姓名, ZBMdata.性别, IIf(d.办公自动化 ='KM01', "80", "0") AS 办公自动化, IIf(d.多媒体技术 ='KM02', "100", "0") AS 多媒体技术, d.考核日期 FROM ZBMdata, [select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') as 办公自动化 ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') as 多媒体技术 from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b]. AS d;
select ZBMdata.baomingNum,ZBMdata.姓名,ZBMdata.性别,KM.科目名称,KM.费用,ZBMrmb.考核日期
from ZBMdata inner join ZBMrmb on ZBMdata.baomingNum=ZBMrmb.baomingNum inner join KM on ZBMrmb.Kmnum=KM.Kmnum
,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02')
from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b先写一半
case when d.多媒体技术 ='KM02' then 100 else 0 end as 多媒体技术,d.考核日期
from ZBMdata,
(select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') as 办公自动化
,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') as 多媒体技术
from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b) as d
where d.BaoMingNum=ZBMdata.BaoMingNum
或将你通过的代码发到
Access要将Case When改成IIF
access里sql数据查询最后带分号的,vb里就可以去掉了
但where那里出了点问题,我再看看SELECT ZBMdata.BaoMingNum, ZBMdata.姓名, ZBMdata.性别, IIf(d.办公自动化 ='KM01', "80", "0") AS 办公自动化, IIf(d.多媒体技术 ='KM02', "100", "0") AS 多媒体技术, d.考核日期
FROM ZBMdata, [select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') as 办公自动化
,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') as 多媒体技术
from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b]. AS d
WHERE d.BaoMingNum=ZBMdata.BaoMingNum;
d.BaoMingNum,ZBMdata.BaoMingNum这两列冗余,可以看出
d.BaoMingNum=ZBMdata.BaoMingNum时是正确的结果,但是加上where后结果就不对了。
原因不明。SELECT d.BaoMingNum,ZBMdata.BaoMingNum, ZBMdata.姓名, ZBMdata.性别, IIf(d.办公自动化 ='KM01', "80", "0") AS 办公自动化, IIf(d.多媒体技术 ='KM02', "100", "0") AS 多媒体技术, d.考核日期
FROM ZBMdata, [select b.* ,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM01') as 办公自动化
,(select top 1 KMnum from (select * from ZBMrmb) as c where b.baomingnum=c.baomingnum and b.考核日期=c.考核日期 and c.KMnum='KM02') as 多媒体技术
from (select BaoMingNum,考核日期 from ZBMrmb group by 考核日期,BaoMingNum) as b]. AS d;