我是oracle的新手，我必须更新以下SQL以将客户端“ title”显示为第一个字段，将count显示为第二个字段，就像现在一样。如果您看下面的SQL是我现在拥有的，它运行的很好，但是有人可以告诉我如何从客户表中添加标题”
SQL：
select SUBSTR(activity, INSTR(activity, '/', 1, 4) + 1, INSTR(activity, '/', 1, 5) - INSTR(activity, '/', 1, 4) -1) AS FILENAME,
COUNT (SUBSTR(activity, INSTR(activity, '/', 1, 4) + 1, INSTR(activity, '/', 1, 5) - INSTR(activity, '/', 1, 4)-1)) AS DOWNLOADS
FROM access_log where id = 5555 and time_stamp BETWEEN TO_DATE ('2014/05/01', 'yyyy/mm/dd') AND TO_DATE ('2014/10/31', 'yyyy/mm/dd')
GROUP BY SUBSTR(activity, INSTR(activity, '/', 1, 4) + 1, INSTR(activity, '/', 1, 5) - INSTR(activity, '/', 1, 4) -1)
ORDER BY DOWNLOADS DESC;
表格：
Table name: access_log
Col name: activity
Value: Download file:/webdocs/data/groupXXX/case/03_28_54_9_0000011856.pdf
Col name: id
Value: 5555
Table name: client
Col name: id
Value: 5555
Col name: title
Value: dj cafe
如果可以，请你帮助我...
应该是这样的
**TITLE|DOWNLOADS
dj cafe|22**
如果要从多个表中生成结果集工程图，则必须在涉及的表上执行适当的联接。根据表之间的关系判断，使连接“适当”的原因主要由连接谓词来衡量。在您的情况下，如果表access_log和client通过各自的id字段关联（这有点奇怪），则此查询将完成此工作：
SELECT
client.title AS TITLE,
COUNT(SUBSTR(activity, INSTR(activity, '/', 1, 4) + 1, INSTR(activity, '/', 1, 5) - INSTR(activity, '/', 1, 4)-1)) AS DOWNLOADS
FROM
access_log
JOIN client
ON client.id = access_log.id
WHERE
client.id = 5555
AND time_stamp BETWEEN TO_DATE ('2014/05/01', 'yyyy/mm/dd') AND TO_DATE ('2014/10/31', 'yyyy/mm/dd')
GROUP BY SUBSTR(activity, INSTR(activity, '/', 1, 4) + 1, INSTR(activity, '/', 1, 5) - INSTR(activity, '/', 1, 4) -1)
ORDER BY DOWNLOADS DESC;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句