如何把my SQL语句改为SQL SERVER 2005语句。

Question

String sQuery = "select kc.*,IF(xk.course_id,count(*),0) as amount "
+ "from kc left join xk "
+ "on kc.course_id = xk.course_id "
+ "where kc.course_id not in "
+ "(select distinct course_id from xk where username='" + sUsername + "') "
+ "group by kc.course_id";
这是My SQL的语句改成SQL Server 2005的，谢谢了！~

Answer 1

String sQuery = "select kc.*,(case when xk.course_id then count(*) else 0 end) as amount "
+ "from kc left join xk "
+ "on kc.course_id = xk.course_id "
+ "where kc.course_id not in "
+ "(select distinct course_id from xk where username='" + sUsername + "') "
+ "group by kc.course_id";追问

My SQL中的 IF(xk.course_id,count(*),0) as amount ，是什么意思？谢谢了~~其他的都不需要改么？

追答

IF(xk.course_id,count(*),0) as amount
意思就是 如果xk.course_id 不为0 ，则 取值 count(*)，否则 取值 0
该列字段取别名amount

追问

它怎么说'then' 附近中指定了非布尔类型的表达式？急~！~

追答

String sQuery = "select kc.*,(case when xk.course_id 0 then count(*) else 0 end) as amount "
+ "from kc left join xk "
+ "on kc.course_id = xk.course_id "
+ "where kc.course_id not in "
+ "(select distinct course_id from xk where username='" + sUsername + "') "
+ "group by kc.course_id";

追问

我很急，加下我Q，550473384，谢了。拜托哦！~