一个数据库查询问题
有2个表
表1
EntryId
1
2
3
4
表2
id EntryId date ip
1 2 2001-11-12 11:11 61.135.179.155
2 1 2001-11-12 11:23 61.135.179.155
3 3 2001-11-12 13:34 61.135.179.152
4 1 2001-11-12 12:43 61.135.179.152
5 2 2001-11-12 12:34 61.135.179.155
查询成
EntryId date visit ip_visit
1 2001-11-12 2 2
2 2001-11-12 2 1
3 2001-11-12 1 1
4 2001-11-12 0 0
那个date是查询条件 输出的时候可有可无
不用存储过程
sql hql都可
[解决办法]
select a.EntryId,format(b.date,'yyyy-mm-dd') as cdate, count(*) as visit, (select count(*) from (select distinct ip from 表2 where EntryId=a.EntryId and int(date)=int(b.date))) as ip_visitfrom 表1 a inner join 表2 b on a.EntryId=b.EntryIdgroup by a.EntryId,format(b.date,'yyyy-mm-dd')
[解决办法]
select a.entryid,date,count(date) as visit,count(distinct ip) as visit_ipfrom 表1 a left join 表2 b on a.EntryId=b.EntryIdgroup by a.EntryId,date;