我正在嘗試獲取居住在相同郵政編碼且郵政編碼計數 > 1 的學生串列。
我嘗試了以下操作，但在我的查詢中一無所獲。如果我洗掉 s.Student，我會得到郵政編碼和計數的結果，但我也想包括學生。
SELECT s.Student, z.ZipCode, COUNT(s.ZipCodeId) As 'Zip Code Count'
FROM Students s
INNER JOIN ZipCodes z ON z.ZipCodeId = s.ZipCodeId
GROUP BY s.Student, z.ZipCode
HAVING COUNT(z.ZipCode) > 1
以下是我正在使用的資料庫表。
CREATE TABLE [dbo].[Instructors](
[InstructorId] [int] IDENTITY(1,1) NOT NULL,
[Instructor] [varchar](50) NOT NULL,
[ZipCodeId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Students](
[StudentId] [int] IDENTITY(1,1) NOT NULL,
[Student] [varchar](50) NOT NULL,
[ZipCodeId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ZipCodes](
[ZipCodeId] [int] IDENTITY(1,1) NOT NULL,
[ZipCode] [varchar](9) NULL,
[City] [varchar](50) NULL,
[State] [varchar](25) NULL
) ON [PRIMARY]
uj5u.com熱心網友回復：
我認為您需要查詢多次使用的郵政編碼，然后加入學生以及郵政編碼詳細資訊，例如
SELECT S.Student, Z.ZipCode, Z1.Num AS "Zip Code Count"
FROM (
SELECT COUNT(*) Num, ZipCodeId
FROM Students S
GROUP BY ZipCodeId
HAVING COUNT(*) > 1
) Z1
INNER JOIN Students S on S.ZipCodeId = Z1.ZipCodeId
INNER JOIN ZipCodes Z on Z.ZipCodeId = Z1.ZipCodeId;
注意：不要使用單引號 ( ') 來分隔列名 - 您使用雙引號 ( ") 或方括號 ( [])。
此外，樣本資料將允許測驗我們的解決方案。
uj5u.com熱心網友回復：
您可以使用視窗函式執行此操作，而無需重新加入
SELECT
S.Student,
Z.ZipCode,
Z.Num AS [Zip Code Count]
FROM (
SELECT *,
COUNT(*) OVER (PARTITION BY S.ZipCodeId) Num
FROM Students S
) S
INNER JOIN ZipCodes Z on Z.ZipCodeId = S.ZipCodeId
WHERE S.Num > 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/466599.html
下一篇：使用交叉應用同時顯示兩列