一个数据库查询有关问题

一个数据库查询问题

有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都可

SQL code

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_visit
from 表1 a inner join 表2 b on a.EntryId=b.EntryId
group by a.EntryId,format(b.date,'yyyy-mm-dd')




  
  
   
   SQL code
   
   
select a.entryid,date,count(date) as visit,count(distinct ip) as visit_ip
from 表1 a left join 表2 b on a.EntryId=b.EntryId
group by a.EntryId,date;

相关文章：

你感兴趣的文章：

标签云：