我有一个用户表和三个日志表,我想统计用户的日志信息,现在是每个表单独统计的,如下所示:
统计不同行政区的打印纪录信息:
SELECT XZQ,COUNT(*) dy FROM (SELECT ajjml_zb.XZQ FROM ajjml_zb,printable WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB)) GROUP BY XZQ
结果:
XZQ DY
------ ----------------------
济南市 7
青岛市 1
泰安市 2
烟台市 4
淄博市 9 统计不同行政区的借阅纪录信息:
SELECT XZQ,COUNT(*) jy FROM (SELECT ajjml_zb.XZQ FROM ajjml_zb,readtable WHERE trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB)) GROUP BY XZQ
结果:
XZQ jy
------ ----------------------
济南市 4
济宁市 11
莱芜市 3
临沂市 1
青岛市 5
泰安市 1
威海市 1
潍坊市 2
烟台市 19
枣庄市 3
淄博市 7 统计不同行政区的拷贝纪录信息:
SELECT XZQ,COUNT(*) cy FROM (SELECT ajjml_zb.XZQ FROM ajjml_zb,copytable WHERE trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB)) GROUP BY XZQ
结果:
XZQ CY
------ ----------------------
烟台市 3
淄博市 1 我想一次得到这三个结果,sql语句怎么写?
统计不同行政区的打印纪录信息:
SELECT XZQ,COUNT(*) dy FROM (SELECT ajjml_zb.XZQ FROM ajjml_zb,printable WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB)) GROUP BY XZQ
结果:
XZQ DY
------ ----------------------
济南市 7
青岛市 1
泰安市 2
烟台市 4
淄博市 9 统计不同行政区的借阅纪录信息:
SELECT XZQ,COUNT(*) jy FROM (SELECT ajjml_zb.XZQ FROM ajjml_zb,readtable WHERE trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB)) GROUP BY XZQ
结果:
XZQ jy
------ ----------------------
济南市 4
济宁市 11
莱芜市 3
临沂市 1
青岛市 5
泰安市 1
威海市 1
潍坊市 2
烟台市 19
枣庄市 3
淄博市 7 统计不同行政区的拷贝纪录信息:
SELECT XZQ,COUNT(*) cy FROM (SELECT ajjml_zb.XZQ FROM ajjml_zb,copytable WHERE trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB)) GROUP BY XZQ
结果:
XZQ CY
------ ----------------------
烟台市 3
淄博市 1 我想一次得到这三个结果,sql语句怎么写?
解决方案 »
- 安装oracle 9i问题
- oracle树形查询
- 紧急求助:EXECUTE IMMEDIATE问题
- sqlloader导数据时,出现原数据文件中的记录顺序颠倒了!
- 有关在tomcat中设置oracle连接池的问题
- 想用execute immediate执行一条update的SQL语句,但不成功,请各位帮看看
- 问一个sql语句怎么写???
- 在连接数据库是出现以下提示:没有发现 Oracle 客户端和网络组件。这些组件由 Oracle 公司提供,是 Oracle 7.3.3版(或更新)客户端软件安装
- 刚刚学习oracle下的存储过程,一个错误请各位帮忙看看
- 请问大家:数据库设计完成后提交给客户的脚本都用哪些好的工具生成的?不会是一行一行写的吧?
- 一个关于时间转换的问题
- oracle触发器
资料所在地 借阅次数 打印次数 拷贝次数
------ ---------------------- ---------------------- ----------------------
烟台市 18 4 3
淄博市 7 9 1 这样写肯定不对,在线请教高手
SELECT ajjml_zb.XZQ,
sum(decode(ajjml_zb.PKIIB, copytable.COPYPKIIB, 1, 0)) cy,
sum(decode(ajjml_zb.PKIIB, copytable.LENDPKIIB, 1, 0)) jy,
sum(decode(ajjml_zb.PKIIB, copytable.PRINTPKIIB, 1, 0)) dy
FROM ajjml_zb, copytable
group by XZQ
SELECT ajjml_zb.XZQ,
sum(decode(ajjml_zb.PKIIB, copytable.COPYPKIIB, 1, 0)) cy,
sum(decode(ajjml_zb.PKIIB, copytable.LENDPKIIB, 1, 0)) jy,
sum(decode(ajjml_zb.PKIIB, copytable.PRINTPKIIB, 1, 0)) dy
FROM ajjml_zb,printable,readtable,copytable
WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB))
and trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB)
and trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB)
GROUP BY ajjml_zb.XZQ
这个好像不行呀,我是三个不同的表,你这只写了copytable,我改成其它两个表名,查询时出现错误
sum(decode(ajjml_zb.PKIIB, printable.PRINTPKIIB, 1, 0)) dy,
sum(decode(ajjml_zb.PKIIB, readtable.LENDPKIIB, 1, 0)) jy,
sum(decode(ajjml_zb.PKIIB, copytable.COPYPKIIB, 1, 0)) cy
FROM ajjml_zb, printable,readtable,copytable
WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB)
AND trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB)
AND trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB)
GROUP BY ajjml_zb.XZQ
select SUM(A.dy),SUM(A.jy),SUM(A.cy)
from (
SELECT XZQ, COUNT(*) dy,0 as jy,0 as cy
FROM (SELECT ajjml_zb.XZQ
FROM ajjml_zb, printable
WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB))
GROUP BY XZQ
union all
SELECT XZQ, 0 as dy,COUNT(*) jy,0 as cy
FROM (SELECT ajjml_zb.XZQ
FROM ajjml_zb, readtable
WHERE trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB))
GROUP BY XZQ
union all
SELECT XZQ,0 as dy, 0 as jy,COUNT(*) cy
FROM (SELECT ajjml_zb.XZQ
FROM ajjml_zb, copytable
WHERE trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB))
GROUP BY XZQ
) A
group by A.XZQ
XZQ JY DY CY
------ ---------------------- ---------------------- ----------------------
烟台市 0 0 216 这样应该是不对的
SELECT ajjml_zb.XZQ,
sum(decode(ajjml_zb.PKIIB, copytable.COPYPKIIB, 1, 0)) cy,
sum(decode(ajjml_zb.PKIIB, copytable.LENDPKIIB, 1, 0)) jy,
sum(decode(ajjml_zb.PKIIB, copytable.PRINTPKIIB, 1, 0)) dy
FROM ajjml_zb,printable,readtable,copytable
GROUP BY ajjml_zb.XZQ
sum(decode(ajjml_zb.PKIIB, copytable.COPYPKIIB, 1, 0)) cy,
sum(decode(ajjml_zb.PKIIB, readtable.LENDPKIIB, 1, 0)) jy,
sum(decode(ajjml_zb.PKIIB, printable.PRINTPKIIB, 1, 0)) dy
FROM ajjml_zb,printable,readtable,copytable
GROUP BY ajjml_zb.XZQ查询了好久没有结果,不知道怎么回事
的 呵呵SELECT ajjml_zb.XZQ,
sum(decode(ajjml_zb.PKIIB, printable.PRINTPKIIB, 1, 0)) dy,
sum(decode(ajjml_zb.PKIIB, readtable.LENDPKIIB, 1, 0)) jy,
sum(decode(ajjml_zb.PKIIB, copytable.COPYPKIIB, 1, 0)) cy
FROM ajjml_zb, printable,readtable,copytable
WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB)
or trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB)
or trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB)
GROUP BY ajjml_zb.XZQ
COUNT(distinct printable.rowid) dy,
COUNT(distinct readtable.rowid) jy,
COUNT(distinct copytable.rowid) cy
FROM (SELECT ajjml_zb.XZQ
FROM ajjml_zb, printable, readtable, copytable
WHERE trim(ajjml_zb.PKIIB) = trim(printable.PRINTPKIIB)(+)
and trim(ajjml_zb.PKIIB) = trim(readtable.LENDPKIIB)(+)
and trim(ajjml_zb.PKIIB) = trim(copytable.COPYPKIIB) (+))
GROUP BY XZQ
from ajjml_zb a join printable b on trim(a.PKIIB) = trim(b.PRINTPKIIB)
join readtable c on trim(a.PKIIB) = trim(c.LENDPKIIB)
join copytable d on trim(a.PKIIB) = trim(d.COPYPKIIB)
select a.XZQ,count(b.PRINTPKIIB) dy,count(c.LENDPKIIB) jy,count(d.COPYPKIIB) cy
from ajjml_zb a join printable b on trim(a.PKIIB) = trim(b.PRINTPKIIB)
join readtable c on trim(a.PKIIB) = trim(c.LENDPKIIB)
join copytable d on trim(a.PKIIB) = trim(d.COPYPKIIB)
group by a.XZQ