我这有张表,里面存了省市级、地市级、区县级、科所级税务机关的代码、名称、简称,大气污染物排放户数、年份和季度,能不能写一条sql,展示出省市级下税务机关大气排放户数的数值,同比,正比?
时间: 2023-12-28 22:20:53 浏览: 70
可以使用以下SQL语句展示省市级下税务机关大气排放户数的数值、同比、正比:
```
SELECT
t1.`省市级税务机关代码`,
t1.`省市级税务机关名称`,
t1.`省市级税务机关简称`,
SUM(t1.`大气污染物排放户数`) AS `大气污染物排放户数`,
t1.`年份`,
t1.`季度`,
(SUM(t1.`大气污染物排放户数`) / t2.`last_year`) - 1 AS `同比`,
(SUM(t1.`大气污染物排放户数`) / t2.`last_quarter`) - 1 AS `正比`
FROM
`表名` t1
LEFT JOIN
(
SELECT
`省市级税务机关代码`,
`年份`,
`季度`,
SUM(`大气污染物排放户数`) AS `last_quarter`,
SUM(
CASE
WHEN `季度` = 4 THEN `大气污染物排放户数`
ELSE NULL
END
) AS `last_year`
FROM
`表名`
GROUP BY
`省市级税务机关代码`,
`年份`,
`季度`
) t2
ON
t1.`省市级税务机关代码` = t2.`省市级税务机关代码`
AND t1.`年份` = t2.`年份`
AND t1.`季度` = t2.`季度`
WHERE
t1.`地市级税务机关代码` IS NULL
AND t1.`区县级税务机关代码` IS NULL
AND t1.`科所级税务机关代码` IS NULL
GROUP BY
t1.`省市级税务机关代码`,
t1.`省市级税务机关名称`,
t1.`省市级税务机关简称`,
t1.`年份`,
t1.`季度`
```
其中,`表名`需要替换为实际的表名。如果表中没有记录季度,则可以将季度改为月份。如果需要计算更多的指标,可以在SELECT子句中添加对应的计算逻辑。
阅读全文