select ss.*,(select count(*) from a where condition1......) count1 from ( select a1,a2,a3,a4...... from a where condition1...... ) ss where rownum < 5;
EMPNO ENAME COUNT ---------- ---------- ---------- 0 14 7369 SMITH 0 7499 ALLEN 0 7521 WARD 0 7566 JONES 0
select * from (select count(*) from emp),(select * from emp where rownum<5)
table ptest like this ptest(szval varchar2(10));select szval, (select count(szval) from ptest where szval='1')mycounter from ptest where szval='1';it can do
这是我的语句,但报错: SELECT jgbh, (SELECT COUNT(*),jgbh FROM yw_yhjb WHERE jgbh LIKE '%3%') rowtotal FROM (SELECT rownum rowcount,jgbh FROM yw_yhjb WHERE jgbh LIKE '%3%') WHERE rowcount >=1 and rowcount<=6 我想按照一定的条件从数据库中取出一定数量的记录,如前6个,同时想得到数据库中符合条件的所有记录总数。
805不能用子查询替代字段,但你的查询即使在8I中也是错误的. SELECT jgbh, (SELECT COUNT(*),jgbh --作为一个字段返回的子查询只能返回一行纪录和一个值 FROM yw_yhjb WHERE jgbh LIKE '%3%') rowtotal FROM (SELECT rownum rowcount,jgbh FROM yw_yhjb WHERE jgbh LIKE '%3%') WHERE rowcount >=1 and rowcount<=6
from (
select a1,a2,a3,a4...... from a
where condition1...... ) ss
where rownum < 5;
---------- ---------- ----------
0 14
7369 SMITH 0
7499 ALLEN 0
7521 WARD 0
7566 JONES 0
14 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
14 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
14 7566 JONES MANAGER 7839 1981-4-2 2975.00 20
SELECT jgbh,
(SELECT COUNT(*),jgbh
FROM yw_yhjb
WHERE jgbh LIKE '%3%') rowtotal
FROM (SELECT rownum rowcount,jgbh
FROM yw_yhjb
WHERE jgbh LIKE '%3%')
WHERE rowcount >=1 and rowcount<=6
我想按照一定的条件从数据库中取出一定数量的记录,如前6个,同时想得到数据库中符合条件的所有记录总数。
SELECT jgbh,
(SELECT COUNT(*),jgbh --作为一个字段返回的子查询只能返回一行纪录和一个值
FROM yw_yhjb
WHERE jgbh LIKE '%3%') rowtotal
FROM (SELECT rownum rowcount,jgbh
FROM yw_yhjb
WHERE jgbh LIKE '%3%')
WHERE rowcount >=1 and rowcount<=6