AdventureWorks2017 の [HumanResources].[Employee]テーブルの社員情報から、生年別の人数を求める。
AdventureWorks サンプルデータベース
https://docs.microsoft.com/ja-jp/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms
社員の生年月日を出力するSQL
use [AdventureWorks2017] go select [BusinessEntityID] ,substring([LoginID],0,30) as LoginID ,[BirthDate] from [HumanResources].[Employee] go
実行結果
BusinessEntityID LoginID BirthDate ---------------- ------------------------------ ---------- 1 adventure-works\ken0 1969-01-29 2 adventure-works\terri0 1971-08-01 3 adventure-works\roberto0 1974-11-12 4 adventure-works\rob0 1974-12-23 5 adventure-works\gail0 1952-09-27 6 adventure-works\jossef0 1959-03-11 7 adventure-works\dylan0 1987-02-24 8 adventure-works\diane1 1986-06-05 9 adventure-works\gigi0 1979-01-21 10 adventure-works\michael6 1984-11-30 11 adventure-works\ovidiu0 1978-01-17 12 adventure-works\thierry0 1959-07-29 13 adventure-works\janice0 1989-05-28 ... (290 rows affected) Completion time: 2020-12-13T22:43:22.5662726+09:00
生年別の人数を求めるSQL
use [AdventureWorks2017] go select convert(char(4), birthdate, 112) as "birth year", count(convert(char(4), birthdate, 112)) as "number of people" from humanresources.employee group by convert(char(4), birthdate, 112) order by convert(char(4), birthdate, 112) go
実行結果
birth year number of people ---------- ---------------- 1951 1 1952 3 1953 1 1954 1 1955 1 1956 10 1957 1 1959 2 1961 1 1962 3 1963 1 1964 1 1966 5 1967 1 1968 4 1969 2 1970 11 1971 13 1972 5 1973 8 1974 12 1975 11 1976 14 1977 18 1978 15 1979 11 1980 7 1981 4 1982 6 1983 10 1984 18 1985 8 1986 25 1987 16 1988 14 1989 14 1990 9 1991 3 (38 rows affected) Completion time: 2020-12-13T22:58:50.7384627+09:00
