PGSQL 查询

SELECT
q.content_type,
kr.knowledge,
COUNT ( 1 )
FROM
"fd_content_quality_2WIQRCZAPA" AS q
JOIN "fd_content_knowledge_relation_2WIQRCZAPA" AS kr ON q.mid = kr.mid
GROUP BY
q.content_type,
kr.knowledge
LIMIT 100;

 

 

SELECT

ks."name",
q.content_type,
COUNT ( 1 )
FROM
"fd_content_quality_2WIQRCZAPA" AS q
JOIN "fd_content_knowledge_relation_2WIQRCZAPA" AS kr ON q.mid = kr.mid
JOIN "fd_content_knowledge_store_2WIQRCZAPA" AS ks ON ks."id" = kr.knowledge
GROUP BY
q.content_type,
kr.knowledge,
ks."name"
LIMIT 100;

 

SELECT
ks."name",
aaa.knowledge,
aaa.num,
CASE
WHEN aaa.content_type = 7 THEN
'图书'
WHEN aaa.content_type = 8 THEN
'音频专辑'
WHEN aaa.content_type = 9 THEN
'音频'
WHEN aaa.content_type = 10 THEN
'视频专辑'
WHEN aaa.content_type = 11 THEN
'视频'
WHEN aaa.content_type = 12 THEN
'微课'
WHEN aaa.content_type = 13 THEN
'课程'
END "内容类型"
FROM
"fd_content_knowledge_store_2WIQRCZAPA" AS ks
JOIN (
SELECT
q.content_type,
kr.knowledge,
COUNT ( 1 ) AS num
FROM
"fd_content_quality_2WIQRCZAPA" AS q
JOIN "fd_content_knowledge_relation_2WIQRCZAPA" AS kr ON q.mid = kr.mid
GROUP BY
q.content_type,
kr.knowledge
) AS aaa ON ks."id" = aaa."knowledge"
LIMIT 100;