表:tb_sss
字段:pos_time(时间),user_id(人员ID),Part_id(部门ID),Area_ID(地区ID)要写4个SQL语句:怎么写呢?
1.2008年表里共有有多少人的记录?2.每个月共有多少人的记录?3.2008年每个部门有多少条记录4.2008年每个地区有多少条记录
字段:pos_time(时间),user_id(人员ID),Part_id(部门ID),Area_ID(地区ID)要写4个SQL语句:怎么写呢?
1.2008年表里共有有多少人的记录?2.每个月共有多少人的记录?3.2008年每个部门有多少条记录4.2008年每个地区有多少条记录
1、select count(1) from tb_sss where to_char(pos_time,'yyyy')='2008';
2、select count(1),to_char(pos_time,'yyyy-mm') group by to_char(pos_time,'yyyy-mm');
3、select count(1) from tb_sss group by part_id;
4、select count(1) from tb_sss group by area_id;
1. select count(1) from tb_sss where to_char(pos_time,'yyyy')='2008';
2. select count(1),substr(to_char(pos_time,'yyyymmdd'),5,2) from tb_sss group by substr(to_char(pos_time,'yyyymmdd'),5,2);
3. select Part_id,count(1) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Part_id;
3. select Area_ID,count(1) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Area_ID;
group by part_id;
4、select count(1) from tb_sss where to_char(pos_time,'yyyy')='2008';
group by area_id;
2. select count(1),substr(to_char(pos_time,'yyyymmdd'),5,2) from tb_sss group by substr(to_char(pos_time,'yyyymmdd'),5,2);
3. select Part_id,count(1) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Part_id;
4. select Area_ID,count(1) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Area_ID
1. select count(distinct user_id) from tb_sss where to_char(pos_time,'yyyy')='2008';2. select count(distinct user_id),substr(to_char(pos_time,'yyyymmdd'),5,2) from tb_sss group by substr(to_char(pos_time,'yyyymmdd'),5,2);3. select Part_id,count(distinct user_id) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Part_id;4. select Area_ID,count(distinct user_id) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Area_ID;
select count(user_id) from tb_sss where pos_time>=to_date('2008-01-01','yyyy-mm-dd') and pos_time<(to_date('2008-01-01','yyyy-mm-dd')+ interval '1' year)
2.
select count(user_id) from tb_sss where pos_time>=to_date('2008-01-01','yyyy-mm-dd') and pos_time<(to_date('2008-01-01','yyyy-mm-dd')+ interval '1' year) group by to_char(pos_time,'yyyy-mm')
3.
select Part_id,count(user_id) from tb_sss where pos_time>=to_date('2008-01-01','yyyy-mm-dd') and pos_time<(to_date('2008-01-01','yyyy-mm-dd')+ interval '1' year) group by Part_id
4.select Area_ID ,count(*) from tb_sss where pos_time>=to_date('2008-01-01','yyyy-mm-dd') and pos_time<(to_date('2008-01-01','yyyy-mm-dd')+ interval '1' year) group by Area_ID
--写慢了
--1:
select count(user_id) from tb_sss where to_char(pos_time,'yyyy')='2008';
--2:
select to_char(pos_time,'yyyy-mm'),count(user_id) from tb_sss group by to_char(pos_time,'yyyy-mm');
--3:
select Part_id,count(*) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Part_id;
--4:
select Area_ID,count(*) from tb_sss where to_char(pos_time,'yyyy')='2008' group by Area_ID;
3.2008年每个部门有多少条记录 4.2008年每个地区有多少条记录Part_id(部门ID),Area_ID(地区ID) 在另一个表KK里,通过user_id(人员ID)关联的,怎么写呢???
--new.3:
select a.Part_id,count(a.*) from tb_sss a,kk b where a.user_id=b.user_id and to_char(a.pos_time,'yyyy')='2008' group by a.Part_id;
--new.4:
select a.Area_id,count(a.*) from tb_sss a,kk b where a.user_id=b.user_id and to_char(a.pos_time,'yyyy')='2008' group by a.Area_id;