h*********n
发帖数: 915 | 1 table Orders:
primary key order_id, foreign key customer_id
table Customers:
primary key customer_id, name, address
find out if there's any customer not having any order.
single query, and no nested query allowed. |
b******u
发帖数: 81 | 2 select
count*)
from
customers as c
left join orders as o on (c.customerid = o.customerid)
where
o.customerid is null |
s**********e
发帖数: 326 | 3 select customer_id
from Customers
minus
select distinct customer_id
from Orders
order by customer_id;
【在 h*********n 的大作中提到】
: table Orders:
: primary key order_id, foreign key customer_id
: table Customers:
: primary key customer_id, name, address
: find out if there's any customer not having any order.
: single query, and no nested query allowed.
|
h*********n
发帖数: 915 | 4 您这个还是两次查询啊。
【在 s**********e 的大作中提到】
: select customer_id
: from Customers
: minus
: select distinct customer_id
: from Orders
: order by customer_id;
|
h*********n
发帖数: 915 | 5 这个可以。
再来一个：学生编号，课程，成绩。判断是否有一个学生所有成绩没有F也没有A。每个
学生选的课可能不同。
Records: Id, Course, Grade
【在 b******u 的大作中提到】
: select
: count*)
: from
: customers as c
: left join orders as o on (c.customerid = o.customerid)
: where
: o.customerid is null
|
z****h
发帖数: 164 | 6 select distinct id from records where grade <> 'f' and grade <>'a' |
h*********n
发帖数: 915 | 7 这个是至少一门在B和D之间的所有学生。
找的是至少一个学生，所有成绩在B到D之间。
【在 z****h 的大作中提到】
: select distinct id from records where grade <> 'f' and grade <>'a'
|
z****h
发帖数: 164 | 8 select distinct id from records where grade between 'b' and 'd' |
b******u
发帖数: 81 | 9 select
studentid
from
Grade
group by
studentid
having
(
max(case when grade = 'A' then 1 else 0 end ) = 0 and
max(case when grade = 'F' then 1 else 0 end ) = 0
) |
z****h
发帖数: 164 | 10 select distinct id from records where id not in (select distinct id from
records where grade = 'a' or grade = 'f') |
g*****e
发帖数: 282 | 11 分别用not exist和between。效率更高，主要是能否利用index的考虑
【在 z****h 的大作中提到】
: select distinct id from records where id not in (select distinct id from
: records where grade = 'a' or grade = 'f')
|
