在程序中使用GoupBy-SQLServer

我的資料庫中有兩個表：

/* Create class table */
CREATE TABLE Class
(
    CId INT NOT NULL,
    ClassName VARCHAR (50) NOT NULL,
    ClassDescription VARCHAR (MAX) NULL,
    ClassStatus VARCHAR (50) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Degree VARCHAR (50) NOT NULL,
    TeacherName VARCHAR (50) NOT NULL,
    ClassTopic VARCHAR (50) NOT NULL,
    CONSTRAINT CHK_Dates CHECK (EndDate > StartDate),
    CONSTRAINT CHK_Status CHECK (ClassStatus = 'Active' Or ClassStatus = 'NAct' Or ClassStatus = 'Archive'),
    CONSTRAINT CHK_Degree CHECK (Degree = '1st' Or
                                    Degree = '2nd' Or
                                    Degree = '3rd' Or
                                    Degree = '4th' Or
                                    Degree = '5th' Or
                                    Degree = '6th' Or
                                    Degree = '7th' Or
                                    Degree = '8th' Or
                                    Degree = '9th' Or
                                    Degree = '10th' Or
                                    Degree = '11th' Or
                                    Degree = '12th'),
    CONSTRAINT CHK_Topic CHECK (ClassTopic = 'Mth' Or
                                    ClassTopic = 'Ph' Or
                                    ClassTopic = 'Ch' Or
                                    ClassTopic = 'Bio' Or
                                    ClassTopic = 'Fr' Or
                                    ClassTopic = 'En' Or
                                    ClassTopic = 'Arb' Or
                                    ClassTopic = 'Rgs'),
    PRIMARY KEY (CId)
);

/* Create Student table*/
CREATE TABLE Student
(
    _SId INT NOT NULL,
    UserName VARCHAR (50) NOT NULL,
    Bdate DATE NOT NULL,
    SPassword VARCHAR (50) NOT NULL,
    SName VARCHAR (50) NOT NULL,
    SLastName VARCHAR (50) NOT NULL,
    NationalCode VARCHAR (10) NOT NULL,
    Email VARCHAR (MAX) NULL,
    StudentClass INT NOT NULL,
    HomePhone VARCHAR (8) NOT NULL,
    CONSTRAINT CHK_Email CHECK (Email like '%_@__%.__%'),
    PRIMARY KEY (_SId),
    FOREIGN KEY (StudentClass) REFERENCES Class (CId) ON DELETE CASCADE
);
ALTER TABLE Student
ADD CONSTRAINT New_CHK_NCode CHECK (NationalCode LIKE '%[0-9]%');

ALTER TABLE Student
ADD CONSTRAINT New_CHK_Phone CHECK (HomePhone LIKE '%[0-9]%');

我在每個記錄中都插入了以下記錄：

USE School


/* Inserting data into tables */

INSERT INTO dbo.Class (CId, ClassName, ClassDescription, ClassStatus, StartDate, EndDate, Degree, TeacherName,  ClassTopic)
VALUES (1, 'aaa', NULL, 'Active', '20020907', '20030907', '1st','Eetemadi', 'Mth'),
        (2, 'bbb', NULL, 'Active', '20020907', '20030907', '1st','Rahmani', 'Ph'),
        (3, 'ccc', NULL, 'Active', '20020907', '20030907', '2nd','Entezari', 'Ch'),
        (4, 'ddd', NULL, 'Active', '20020907', '20030907', '2nd','Beytollahi', 'Bio'),
        (5, 'eee', NULL, 'Active', '20020907', '20030907', '3rd','Zahirpour', 'Fr');

INSERT INTO dbo.Student (_SId, UserName, Bdate, SPassword, SName, SLastName, NationalCode, Email, StudentClass, HomePhone)
VALUES (1, 'aaa', '20020807', '1234', 'maryam', 'vahdati', '1234567890', '[email protected]', 1, '12345678'),
        (2, 'bbb', '20020707', '4321', 'marjan', 'vahdati', '1234578906', '[email protected]', 1, '12345678'),
        (3, 'ccc', '20020607', '1342', 'masomeh', 'vahdati', '1234567809', '[email protected]', 2, '12345678'),
        (4, 'ddd', '20020507', '1243', 'mohammad', 'vahdati', '1234568907', '[email protected]', 2, '12345678'),
        (5, 'eee', '20020407', '1342', 'mahmod', 'vahdati', '1245678903', '[email protected]', 3, '12345678');

現在我必須在 a 中撰寫一個查詢Procedure來根據學位對班級進行分組，顯示每個年級的學生人數，以及班級總數。我寫了以下查詢：

ALTER PROCEDURE dbo.FirstReport
AS
BEGIN
    Select Degree, COUNT(Degree) as numberOfClasses, COUNT(StudentClass) as numberOfStudents
    FROM Class C left outer join Student S ON C.CId = S.StudentClass
    GROUP BY Degree

    UNION ALL
    SELECT 'SUM' Degree, COUNT(Degree), COUNT(StudentClass)
    FROM Class C join Student S ON C.CId = S.StudentClass;
END

輸出是：

| Degree| numberOfClasses | numberOfStudents|
|:-----------------------------------------:|
|1st    |           4     |        4        |
|2nd    |           2     |        1        |
|3rd    |           1     |        0        |
|SUM    |           5     |        5        |

但是 numberOfClasses 必須2在Degreeis時1st。

我不知道如何使它正確。我會很感激你的幫助。

uj5u.com熱心網友回復：

正如我在評論中提到的，你在這里有一個多對一的加入，因此COUNT你得到的是正確的，因為說一對多加入；您有 2 行，每行都連接到其他 2 行，并且2 * (1 * 2) = 4.

相反，請在 ID 列DISTINCT的第一個上使用 a。COUNT此外，不需要UNION ALL; 您可以使用GROUPING SETS或ROLLUP獲取“總計”行：

SELECT ISNULL(C.Degree,'SUM') AS Degree,
       COUNT(DISTINCT C.CId) AS NumberOfClasses,
       COUNT(S.StudentClass) AS NumberOfStudents
FROM dbo.Class C
     LEFT OUTER JOIN dbo.Student S ON C.CId = S.StudentClass
GROUP BY GROUPING SETS(Degree,());

標籤：sql服务器 通过...分组 程序

上一篇：SQL查詢根據另一列中的最大值更新記錄

下一篇：在表格的所有列上搜索關鍵字