sql计算某个值的占⽐_SQL：计算共享公⽤列值的⾏组
sql 计算某个值的占⽐
在本⽂中，我将重点介绍使⽤简单的 语句对满⾜特定条件的表中的⾏数进⾏计数，并将结果按表的特定列分组。 这些都是基本概念，但是将它们混合使⽤就可以对关系数据库中存储的数据进⾏不同且有⽤的表⽰。 本⽂中涉及SQL查询的特定⽅⾯并⽤简单的⽰例进⾏了说明，它们是聚合函数count() ， WHERE ， GROUP BY和HAVING 。 这些将⽤于构建简单的单个SQL查询，该查询指⽰表中与该表中给定列的不同值匹配的⾏数。
我需要⼀些简单SQL数据进⾏演⽰。 以下SQL代码演⽰了在创建名为ALBUMS的表，然后使⽤语句填充该表。createAndPopulateAlbums.sql
CREATE TABLE albums
(
title text,
artist text,
year integer
);
INSERT INTO albums (title, artist, year)
VALUES ('Back in Black', 'AC/DC', 1980);
INSERT INTO albums (title, artist, year)
VALUES ('Slippery When Wet', 'Bon Jovi', 1986);
INSERT INTO albums (title, artist, year)
VALUES ('Third Stage', 'Boston', 1986);
INSERT INTO albums (title, artist, year)
VALUES ('Hysteria', 'Def Leppard', 1987);
INSERT INTO albums (title, artist, year)
VALUES ('Some Great Reward', 'Depeche Mode', 1984);
INSERT INTO albums (title, artist, year)
VALUES ('Violator', 'Depeche Mode', 1990);
INSERT INTO albums (title, artist, year)
VALUES ('Brothers in Arms', 'Dire Straits', 1985);
INSERT INTO albums (title, artist, year)
VALUES ('Rio', 'Duran Duran', 1982);
INSERT INTO albums (title, artist, year)
VALUES ('Hotel California', 'Eagles', 1976);
INSERT INTO albums (title, artist, year)
VALUES ('Rumours', 'Fleetwood Mac', 1977);
INSERT INTO albums (title, artist, year)
VALUES ('Kick', 'INXS', 1987);
INSERT INTO albums (title, artist, year)
VALUES ('Appetite for Destruction', 'Guns N'' Roses', 1987);
INSERT INTO albums (title, artist, year)
VALUES ('Thriller', 'Michael Jackson', 1982);
INSERT INTO albums (title, artist, year)
VALUES ('Welcome to the Real World', 'Mr. Mister', 1985);
INSERT INTO albums (title, artist, year)
VALUES ('Never Mind', 'Nirvana', 1991);
INSERT INTO albums (title, artist, year)
VALUES ('Please', 'Pet Shop Boys', 1986);
INSERT INTO albums (title, artist, year)
VALUES ('The Dark Side of the Moon', 'Pink Floyd', 1973);
INSERT INTO albums (title, artist, year)
VALUES ('Look Sharp!', 'Roxette', 1988);
INSERT INTO albums (title, artist, year)
VALUES ('Songs from the Big Chair', 'Tears for Fears', 1985);
INSERT INTO albums (title, artist, year)
VALUES ('Synchronicity', 'The Police', 1983);
INSERT INTO albums (title, artist, year)
VALUES ('Into the Gap', 'Thompson Twins', 1984);
INSERT INTO albums (title, artist, year)
VALUES ('The Joshua Tree', 'U2', 1987);
INSERT INTO albums (title, artist, year)
VALUES ('1984', 'Van Halen', 1984);
接下来的两个屏幕快照显⽰了在中运⾏此脚本的结果：
在这⼀点上，如果我想查看每年发⾏了多少张专辑，可以使⽤以下⼏个单独SQL查询语句：
SELECT count(1) FROM albums where year = 1985;
SELECT count(1) FROM albums where year = 1987;
可能需要查看每年发⾏多少张专辑⽽⽆需每年进⾏单独查询。 这是在使⽤带有⼦句的类的聚合函数时很⽅便的地⽅。 下⼀个查询很简单，但是利⽤来显⽰按专辑发⾏年分组的每个“组”⾏的计数。
SELECT year, count(1)
FROM albums
GROUP BY year;
通过指定缩⼩条件，可以正常使⽤WHERE⼦句来缩⼩返回的⾏数。 例如，以下查询返回在1988年之后的⼀年中发⾏的专辑。
SELECT year, count(1)
FROM albums
WHERE year > 1988
GROUP BY year;values翻译
我们可能只想返回表中有多张专辑（不⽌⼀张）的年份。 第⼀个天真的⽅法可能如下所⽰（不起作⽤，如下⾯的屏幕快照所⽰）：
-- Bad Code!: Don't do this.
SELECT year, count(1)
FROM albums
WHERE count(1) > 1
GROUP BY year;
上⼀个屏幕快照显⽰“在WHERE中不允许使⽤聚合函数”。 换句话说，我们不能在WHERE⼦句中使⽤count() 。 这是⼦句有⽤的地⽅，因为缩⼩结果的⽅式与WHERE相似，但是与聚合函数和GROUP BY 。
下⼀个SQL清单演⽰了如何使⽤HAVING⼦句来完成较早尝试的任务（列出表中存在多个专辑⾏的年份）：
SELECT year, count(1)
FROM albums
GROUP BY year
HAVING count(1) > 1;
最后，我可能希望对结果进⾏排序，以便将它们以递增（较晚）的年份列出。 这⾥显⽰了前⾯演⽰的两个SQL查询，其中添加了 。
SELECT year, count(1)
FROM albums
GROUP BY year
ORDER BY year;
SELECT year, count(1)
FROM albums
GROUP BY year
HAVING count(1) > 1
ORDER BY year;
与我刚开始使⽤SQL相⽐，SQL已经成为⼀种更加丰富的语⾔，但是多年来可⽤的基本SQL仍然有效且有⽤。 尽管本⽂中的⽰例已使⽤PostgreSQL进⾏了演⽰，但这些⽰例应在实现ANSI SQL的⼤多数关系数据库上运⾏。
翻译⾃:
sql 计算某个值的占⽐

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。