select count(in_time) form table where to_date(in_time,'yyyymmdd')=to_date('20050303','yyyymmdd')
创建测试数据: SQL> CREATE TABLE TEST(ID NUMBER, in_time VARCHAR2(8));Table createdSQL> INSERT INTO TEST VALUES(1, '20060101');1 row insertedSQL> INSERT INTO TEST VALUES(2, '20060102');1 row insertedSQL> INSERT INTO TEST VALUES(3, '20060103');1 row insertedSQL> INSERT INTO TEST VALUES(4, '20060110');1 row insertedSQL> INSERT INTO TEST VALUES(5, '20060211');1 row insertedSQL> INSERT INTO TEST VALUES(6, '20060214');1 row inserted SQL> INSERT INTO TEST VALUES(7, '20060214');1 row insertedSQL> INSERT INTO TEST VALUES(8, '20060214');1 row inserted SQL> SELECT * FROM TEST; ID IN_TIME ---------- -------- 1 20060101 2 20060102 3 20060103 4 20060110 5 20060211 6 20060214 7 20060214 8 200602148 rows selectedSQL语句:'200602'为参数。 SQL> SELECT IN_TIME, COUNT(IN_TIME) COUNT FROM TEST WHERE SUBSTR(IN_TIME, 1, 6)='200602' GROUP BY IN_TIME;IN_TIME COUNT -------- ---------- 20060211 1 20060214 3
select count(1), to_char(in_time,'YYYYMMDD') from test where to_char(in_time,'YYYYMM') = '200602' group by to_char(in_time,'YYYYMMDD')
select count(*) from tablename where trunc(in_time) = to_date('2006-2-28','yyyy-mm-dd')这样就是查06年2月28号的记录总数
select to_date(in_time,'yyyymmdd'),count(in_time) form table group by to_date(in_time,'yyyymmdd')
SQL> CREATE TABLE TEST(ID NUMBER, in_time VARCHAR2(8));Table createdSQL> INSERT INTO TEST VALUES(1, '20060101');1 row insertedSQL> INSERT INTO TEST VALUES(2, '20060102');1 row insertedSQL> INSERT INTO TEST VALUES(3, '20060103');1 row insertedSQL> INSERT INTO TEST VALUES(4, '20060110');1 row insertedSQL> INSERT INTO TEST VALUES(5, '20060211');1 row insertedSQL> INSERT INTO TEST VALUES(6, '20060214');1 row inserted
SQL> INSERT INTO TEST VALUES(7, '20060214');1 row insertedSQL> INSERT INTO TEST VALUES(8, '20060214');1 row inserted
SQL> SELECT * FROM TEST; ID IN_TIME
---------- --------
1 20060101
2 20060102
3 20060103
4 20060110
5 20060211
6 20060214
7 20060214
8 200602148 rows selectedSQL语句:'200602'为参数。
SQL> SELECT IN_TIME, COUNT(IN_TIME) COUNT FROM TEST WHERE SUBSTR(IN_TIME, 1, 6)='200602' GROUP BY IN_TIME;IN_TIME COUNT
-------- ----------
20060211 1
20060214 3