一个比较复杂的查询语句

 Access中不支持视图，认为视图是个复杂查询。

Create View TV as
select B.IPDCode as 类别,B.Grade,count(B.Grade) as 数目 From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="建议推荐"  group by B.IPDCode,B.Grade
union
select B.IPDCode as 类别,B.Grade,count(B.Grade) as 数目 From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="暂不推荐" group by B.IPDCode,B.Grade;

Access中不认识关键字“View”。

第一版查询语句：

select B.IPDCode as 类别,B.Grade,count(B.Grade) as 数目 From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="建议推荐"  group by B.IPDCode,B.Grade
union
select B.IPDCode as 类别,B.Grade,count(B.Grade) as 数目 From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="暂不推荐" group by B.IPDCode,B.Grade;

第二版改进型查询语句：

 select B.IPDCode as 类别,count(B.Grade) as 建议推荐,

(select count(B.Grade) From WW_IPD C,WW_SM D Where C.IPDCode = D.IPDCode And D.Grade="暂不推荐" AND C.IPDCode=B.IPDCode )as 暂不推荐

From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="建议推荐"  group by B.IPDCode,B.Grade

 

第二版语句有漏洞，下面为第三版改进型查询语句：

 

select B.IPDCode as 类别,
(select count(B.Grade) From WW_IPD C,WW_SM D Where C.IPDCode = D.IPDCode And D.Grade="暂不推荐" AND C.IPDCode=B.IPDCode )as 暂不推荐, count(B.Grade) as 建议推荐

From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="建议推荐"  group by B.IPDCode,B.Grade

union
select B.IPDCode as 类别,count(B.Grade) as 暂不推荐,

(select count(B.Grade) From WW_IPD C,WW_SM D Where C.IPDCode = D.IPDCode And D.Grade="建议推荐" AND C.IPDCode=B.IPDCode )as 建议推荐

From WW_IPD A,WW_SM B Where A.IPDCode = B.IPDCode And B.Grade="暂不推荐"  group by B.IPDCode,B.Grade

 

*感悟：用户需求促使我们技术探索！