56,685
社区成员
发帖
与我相关
我的任务
分享
SELECT
col_name, SUM(count) AS value
FROM
table_name
WHERE
sys_date BETWEEN '2017-10-01' AND '2017-10-31'
GROUP BY col_name
ORDER BY value DESC
SELECT col_name, SUM(count) AS value
FROM
(
SELECT
col_name, count
FROM
table_name
WHERE
sys_date BETWEEN '2017-10-01' AND '2017-10-31'
) A
GROUP BY col_name
ORDER BY value DESC
EXPLAIN
SELECT
A.execute_app AS name, IFNULL(category_mapping.view_name, execute_app) AS mappingName
FROM
(
SELECT
sys_date,
month_number,
execute_app, SUM(COUNT) AS value
FROM path_rule
WHERE month_number=10 AND YEAR = 2017)) AND execute_app IS NOT NULL AND LENGTH(execute_app) > 0
GROUP BY execute_app
ORDER BY value DESC)A
LEFT JOIN
category_mapping ON
A.execute_app = category_mapping.log_name AND category_mapping.kpi_name = 'path_rule_app'
ORDER BY A.value DESC
LIMIT 10
第二个SQL, group by 放外面的(快)
EXPLAIN
SELECT
A.execute_app AS name, IFNULL(category_mapping.view_name, execute_app) AS mappingName
FROM
(
SELECT execute_app, SUM(COUNT) AS value
FROM(
SELECT execute_app, COUNT
FROM
path_rule
WHERE month_number = 10 AND YEAR = 2017 AND execute_app IS NOT NULL AND LENGTH(execute_app) > 0 ) r
GROUP BY execute_app
ORDER BY value DESC)A
LEFT JOIN
category_mapping ON
A.execute_app = category_mapping.log_name AND category_mapping.kpi_name = 'path_rule_app'
LIMIT 10
Explain的结果上面贴过了
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "<derived2>" "ALL" \N \N \N \N "2576336" "Using temporary; Using filesort"
"1" "PRIMARY" "category_mapping" "ALL" \N \N \N \N "148" "Using where; Using join buffer (Block Nested Loop)"
"2" "DERIVED" "path_rule" "index" "INDEX_APPS,INDEX_APPS_WEEK" "INDEX_APPS" "306" \N "5152673" "Using where; Using temporary; Using filesort"
这是第二个的
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "<derived2>" "ALL" \N \N \N \N "2576336" \N
"1" "PRIMARY" "category_mapping" "ALL" \N \N \N \N "148" "Using where; Using join buffer (Block Nested Loop)"
"2" "DERIVED" "<derived3>" "ALL" \N \N \N \N "2576336" "Using temporary; Using filesort"
"3" "DERIVED" "path_rule" "ALL" "INDEX_APPS,INDEX_APPS_WEEK" \N \N \N "5152673" "Using where"