Redian新闻
>
a complex sql query, high hand help!!!
avatar
a complex sql query, high hand help!!!# Database - 数据库
w*n
1
let say i have a table
called log:
which has colum agentID, logID, type, customerID
and another table called agent have agentID
and another table called customer have customerID
type can be car, or hotel.
and one customer can rent car from same agent at many
different
times. how do i found out customer who have rent car more
than twice from
the same agent?
avatar
l*o
2
select log.cID
from log L
Group By L.cID
Having 1from log L2
where L.cID=L2.cID and L.aId= L2.aID)
right? I don't know, make a try.

【在 w******n 的大作中提到】
: let say i have a table
: called log:
: which has colum agentID, logID, type, customerID
: and another table called agent have agentID
: and another table called customer have customerID
: type can be car, or hotel.
: and one customer can rent car from same agent at many
: different
: times. how do i found out customer who have rent car more
: than twice from

avatar
s*t
3
If you only want CustomerID and agentID:
SELECT customerID, agentID, count(logID)
FROM log
GROUP BY cutomerID, agentID
HAVING count(logID) >= 2;
if you want names, joint with customer and
agent table:
SELECT customer_name, agent_name, count(logID)
FROM log l, customer c, agent a
WHERE l.customerID = c.customerID AND
l.agentID = a.agentID
GROUP BY cutomer_name, agent_name
HAVING count(logID) >= 2;

【在 w******n 的大作中提到】
: let say i have a table
: called log:
: which has colum agentID, logID, type, customerID
: and another table called agent have agentID
: and another table called customer have customerID
: type can be car, or hotel.
: and one customer can rent car from same agent at many
: different
: times. how do i found out customer who have rent car more
: than twice from

相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。