2,596
社区成员
发帖
与我相关
我的任务
分享
USE AdventureWorks;
GO
SELECT ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO
1> select * from course
2> go
course_id
---------
000001
000002
000003
000004
000005
(5 rows affected)
1> select * from elective
2> go
username course_id
-------- ---------
aaaaaa 000001
bbbbbb 000002
cccccc 000001
dddddd 000001
eeeeee 000003
(5 rows affected)
1> select c.course_id,count(e.course_id)
2> from course c left join elective e on c.course_id=e.course_id
3> group by c.course_id
4> go
course_id
--------- -----------
000001 3
000002 1
000003 1
000004 0
000005 0
1>
String sQuery = "select course.*,IF(elective.course_id, count(*), 0) as amount "
+ "from course left join elective "
+ "on course.course_id = elective.course_id "
+ "where course.course_id not in "
+ "(select distinct course_id from elective where username='" + sUsername + "') "
+ "group by course.course_id";
course_id count
-------------------
000001 3
000002 1
000005 0
username course_id
-----------------------
aaaaaa 000001
bbbbbb 000002
cccccc 000001
dddddd 000001
eeeeee 000003