a complex sql query, high hand help!!! - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

a complex sql query, high hand help!!!

a complex sql query, high hand help!!!# Database - 数据库

w*n2001-02-13 08:02

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?

l*o2001-02-13 08:02

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

s*t2001-02-13 08:02

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 的大作中提到】