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
|
|