select UsersList.User_UID, UsersList.User_Name,'
+' IIf(IsEmpty(count(StudysList.Study_key)), 0, count(StudysList.Study_key)) as StudyNum from UsersList,StudysList '
+' where StudysList.User_UID = UsersList.User_UID '
+' and StudysList.Study_ID> '+str1+' and StudysList.Study_ID< '+str2+''
+' group by UsersList.User_UID,UsersList.User_Name
我想实现的功能是:在检索 str1,str2范围内的StudysList.Study_ID时,如果 count(StudysList.Study_key) 为 0,则
UsersList.User_UID,UsersList.User_Name 还是能显示出来,StudyNum 结果显示为0。
可是上面的语句实现的是:count(StudysList.Study_key)为0时,就不显示出来了。
请教各位前辈,该怎么实现我期望的实现方式?
解决方案 »
- 跪求:用AdoDataset的UpdateBatch方法插入数据,如何得到每条新增记录的自增ID号?
- 哪个版本的Delphi(Delphi5- Delphi2009)编译出来的目标代码最小巧?
- 转载:一首诗。大家进来看看。
- 在线等待
- 用serversocket,clientsocket传tstringlist,用流技术的程序实例
- C/S结构中,客户端A更新数据后客户端B如何自动刷新数据?
- 请教各位:在stringgird 中能否用颜色区分每个小格
- socket函数为什么总是返回-1?
- 那个快一点?
- 各位帮帮忙,我的DELPHI出什么问题?
- delphi循环中等待问题~~
- 求Delphi的学习策略及书籍
+' StudyNum = case when count(StudysList.Study_key) = 0 then 0 else count(StudysList.Study_key) end '
+' from UsersList,StudysList '
+' where StudysList.User_UID = UsersList.User_UID '
+' and StudysList.Study_ID> '+str1+' and StudysList.Study_ID< '+str2+''
+' group by UsersList.User_UID,UsersList.User_Name
select UsersList.User_UID, UsersList.User_Name,'
+' StudyNum = case when count(StudysList.Study_key) = 0 then 0 else count(StudysList.Study_key) end '
+' from UsersList left jion StudysList '
+' on StudysList.User_UID = UsersList.User_UID '
+' where StudysList.Study_ID> '+str1+' and StudysList.Study_ID< '+str2+''
+' group by UsersList.User_UID,UsersList.User_Name
我是想把 UsersList 全部显示出来, 并且在StudyNum 中显示 count 检索的条数,如果是0就显示0。
ps: access 是不是不能用case when else end 语法啊?
+' IIf(IsEmpty(count(StudysList.Study_key)), 0, count(StudysList.Study_key)) as StudyNum from UsersList left join StudysList '
+' on StudysList.User_UID = UsersList.User_UID '
+' and StudysList.Study_ID> '+str1+' and StudysList.Study_ID< '+str2+''
+' group by UsersList.User_UID,UsersList.User_Name
+' count(StudysList.Study_key) as StudyNum '
+' from UsersList left join StudysList '
+' on StudysList.User_UID = UsersList.User_UID '
+' where StudysList.Study_ID> '+str1+' and StudysList.Study_ID< '+str2+''
+' group by UsersList.User_UID,UsersList.User_Name ');
这样运行出来 还是无法全部显示。。
应该是的 , 它在 left join 左联之后,执行 where 条件过滤
可是where 后面的条件是必需的。
像这样
+' on StudysList.User_UID = UsersList.User_UID '
+' and StudysList.Study_ID> '+str1+' and StudysList.Study_ID< '+str2+''
改成 on...and... 后 又提示“不支持连接表形式”
left join on 和 where 的冲突关系,期待高人指点迷津