select * FROM LatencyUseBase INNER JOIN salesvisit ON LatencyUseBase.guest_id = salesvisit.guest_id WHERE LatencyUseBase.LatencyUseState <> '已销售用户' and salesvisit.visit_time=max(salesvisit.visit_time) 试试
上面那个有错应该是这样的 select * FROM LatencyUseBase INNER JOIN salesvisit ON LatencyUseBase.guest_id = salesvisit.guest_id WHERE LatencyUseBase.LatencyUseState <> '已销售用户' and salesvisit.visit_time=(select max(salesvisit.visit_time) from salesvisit)
JohnnyShu(欧阳仇) 大哥,这样的话只有拜访次数次数最大的一个客户,而不是所有客户
select latencyusebase.*,salesvisit.* from (select guestID,max(visit_time) from salevisit) as a inner join LatencyUseBase on a.guestID=latencyUseBase.guestID INNER JOIN salesvisit ON a.guest_id = salesvisit.guest_id and a.visit_time=salesvisit.guest_id where LatencyUseBase.LatencyUseState <> '已销售用户'
前面有一点错select latencyusebase.*,salesvisit.* from (select guestID,max(visit_time) as maxvisit_time from salevisit) as a inner join LatencyUseBase on a.guestID=latencyUseBase.guestID INNER JOIN salesvisit ON a.guest_id = salesvisit.guest_id and maxvisit_time=visit_time where LatencyUseBase.LatencyUseState <> '已销售用户'
select LatencyUseBase.*,tmp.* FROM LatencyUseBase INNER JOIN (select guest_id,max(visit_time) as 拜访最大次数 from salesvisit group by guest_id) tmp ON LatencyUseBase.guest_id = tmp.guest_id WHERE LatencyUseBase.LatencyUseState <> '已销售用户'这样就可以了。
最後一行修改如下: WHERE LatencyUseBase.LatencyUseState <> '已销售用户' group by guestid --> WHERE LatencyUseBase.LatencyUseState <> '已销售用户' group by LatencyUseBase.guest_id
group by LatencyUseBase.guest_id
group by LatencyUseBase.guest_id后报错,服务器: 消息 8120,级别 16,状态 1,行 37
列 'LatencyUseBase.guest_name' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
Group by LatencyUseBase.guest_id, LatencyUseBase.guest_name,
LatencyUseBase.guest_add,
LatencyUseBase.guest_post,
LatencyUseBase.guest_tel ,
LatencyUseBase.guest_fax,
LatencyUseBase.guest_mobile,
LatencyUseBase.guest_birthday,
LatencyUseBase.information ,
LatencyUseBase.sales,
LatencyUseBase.buy_model ,
LatencyUseBase.buy_price,
LatencyUseBase.motor_use,
LatencyUseBase.motor_fund,
LatencyUseBase.motor_brand ,
LatencyUseBase.buy_date,
LatencyUseBase.license_use,
LatencyUseBase.old_model,
LatencyUseBase.old_price,
LatencyUseBase.old_year,
LatencyUseBase.old_average ,
LatencyUseBase.safe_date, max(salesvisit.visit_time),
salesvisit.visit_date , salesvisit.visit_type,
salesvisit.sales_promotion, salesvisit.guest_level,
salesvisit.visit_second,
salesvisit.guest_question, salesvisit.sales_idea,
salesvisit.operation_idea,
salesvisit.operation_level,
salesvisit.operation_date,
salesvisit.manager_idea,
salesvisit.manager_date
这样的话和不加“max(salesvisit.visit_time) AS 拜访次数”的显示结果没任何区别
我只是想得到最大一次的拜访次数的一条数据,并与LatencyUseBase表相连接,能否给个提示,谢谢
FROM LatencyUseBase INNER JOIN
salesvisit ON LatencyUseBase.guest_id = salesvisit.guest_id
WHERE LatencyUseBase.LatencyUseState <> '已销售用户'
and salesvisit.visit_time=max(salesvisit.visit_time)
试试
select *
FROM LatencyUseBase INNER JOIN
salesvisit ON LatencyUseBase.guest_id = salesvisit.guest_id
WHERE LatencyUseBase.LatencyUseState <> '已销售用户'
and salesvisit.visit_time=(select max(salesvisit.visit_time) from salesvisit)
from (select guestID,max(visit_time) from salevisit) as a
inner join LatencyUseBase on a.guestID=latencyUseBase.guestID
INNER JOIN salesvisit ON a.guest_id = salesvisit.guest_id and a.visit_time=salesvisit.guest_id
where LatencyUseBase.LatencyUseState <> '已销售用户'
from (select guestID,max(visit_time) as maxvisit_time from salevisit) as a
inner join LatencyUseBase on a.guestID=latencyUseBase.guestID
INNER JOIN salesvisit ON a.guest_id = salesvisit.guest_id and maxvisit_time=visit_time
where LatencyUseBase.LatencyUseState <> '已销售用户'
FROM LatencyUseBase INNER JOIN
(select guest_id,max(visit_time) as 拜访最大次数 from salesvisit group by guest_id) tmp ON LatencyUseBase.guest_id = tmp.guest_id
WHERE LatencyUseBase.LatencyUseState <> '已销售用户'这样就可以了。
WHERE LatencyUseBase.LatencyUseState <> '已销售用户' group by guestid --> WHERE LatencyUseBase.LatencyUseState <> '已销售用户' group by LatencyUseBase.guest_id