j****d
发帖数: 1675 | 1 【 以下文字转载自 Programming 讨论区 】
发信人: jjjddd (james), 信区: Programming
标 题: 请问这个查询如何用sql join实现？
发信站: BBS 未名空间站 (Fri Aug 13 14:22:57 2010, 美东)
假设商店数据库有两个table A和B
table A是用户表，存放用户id,姓名,电话，地址 etc
table B是订单表，存放订单id, 产品id,下单用户id,etc
请问能不能只用sql join来实现这个查询：找到所有至多下过2次订单的用户名、电话
、地址？比方说有个用户下了3次单就不算。2次或1次才算。不许用stored procedure
或java等编程方式实现。
我面试时被问到这个问题，当时没答出来。汗 | x***y
发帖数: 633 | 2 Did I missing something?
select name, phoneNumber, address
from A inner join B
on A.usr_id=B.usr_id
group by B.usr_id
having count(*)<3 | l*****a
发帖数: 559 | 3 SELECT * FROM `用户表` WHERE `用户id` IN (SELECT `下单用户id` FROM `订单表`
GROUP BY `下单用户id` HAVING COUNT(*)<=2)
这个是store procedure不？ | x*****p
发帖数: 1707 | 4 Customer(cid, name, address, phone)
Order(oid, product, cid)
The query is
SELECT c.cid, c.name, c.address, c.phone
FROM Customer c, Order o
WHERE c.cid = o.cid
GROUP BY c.cid
HAVING count(*)<=2 | j****d
发帖数: 1675 | 5 多谢，很久没用having count,忘了。。。 | j****d
发帖数: 1675 | 6 you failed, kaka
【在 x***y 的大作中提到】
: Did I missing something?
: select name, phoneNumber, address
: from A inner join B
: on A.usr_id=B.usr_id
: group by B.usr_id
: having count(*)<3
| j****d
发帖数: 1675 | 7 you failed, kaka
【在 x*****p 的大作中提到】
: Customer(cid, name, address, phone)
: Order(oid, product, cid)
: The query is
: SELECT c.cid, c.name, c.address, c.phone
: FROM Customer c, Order o
: WHERE c.cid = o.cid
: GROUP BY c.cid
: HAVING count(*)<=2
| j****d
发帖数: 1675 | 8 this one works.
`
【在 l*****a 的大作中提到】
: SELECT * FROM `用户表` WHERE `用户id` IN (SELECT `下单用户id` FROM `订单表`
: GROUP BY `下单用户id` HAVING COUNT(*)<=2)
: 这个是store procedure不？
|
| j****d
发帖数: 1675 | 9 these 2 work too:
select user_name, phone, count(u.user_id)
from users u join order
on u.user_id = order.user_id
group by user_name, day_phone
having count(*) <3
select user_name, phone, count(u.user_id)
from users u , order
where u.user_id = order.user_id
group by user_name, day_phone
having count(*) <3 | s*****t
发帖数: 737 | 10 really?
you failed, kaka
【在 j****d 的大作中提到】
: you failed, kaka
|
|
