代码之家 › 专栏 › 技术社区 › DancesWithBamboo

计算SQL Server 2005中的统计模式

sql-server-2005

DancesWithBamboo · 技术社区 · 15 年前

我有这两个表:课程(pk courseid,title)和参加者(pk atteeid,fk courseid,method)。
许多与会者可以通过6种方法中的任何一种来参加某一课程。

如何在课程表(courseid,title)中为每门课程打印一条记录,并对每门课程的所有与会者使用该方法的统计模式?

1 回复 | 直到 15 年前

Peter Mortensen icecrime 15 年前

好吧,据我所知你需要什么。

由此 Mode (statistics) ,

模式不一定是唯一的, 因为相同的最大频率可能以不同的价值获得。

所以这里去

DECLARE @Course TABLE(
        CourseID INT,
        Title VARCHAR(50)
)

INSERT INTO @Course (CourseID,Title) SELECT 1, 'AA'
INSERT INTO @Course (CourseID,Title) SELECT 2, 'BB'
INSERT INTO @Course (CourseID,Title) SELECT 3, 'CC'

DECLARE @Attendee TABLE(
        AttendeeID INT,
        CourseID INT,
        Method INT
)

INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 1, 1, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 2, 1, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 3, 1, 2
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 4, 1, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 5, 1, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 6, 1, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 7, 1, 4
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 8, 1, 4
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 9, 1, 5
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 10, 1, 6
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 11, 1, 6
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 12, 1, 6

INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 13, 2, 1
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 14, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 15, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 16, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 17, 2, 3
INSERT INTO @Attendee (AttendeeID,CourseID,Method) SELECT 18, 2, 6

DECLARE @Counts TABLE(
        CourseID INT,
        Title VARCHAR(50),
        Method INT,
        NumberMethodPerCourse INT
)

INSERT INTO @Counts (CourseID,Title,Method,NumberMethodPerCourse)
SELECT  c.CourseID,
        c.Title,
        a.Method,
        COUNT(a.Method) NumberMethodPerCourse
FROM    @Course c INNER JOIN
        @Attendee a ON c.CourseID = a.CourseID
GROUP BY    c.CourseID,
            c.Title,
            a.Method

SELECT  CourseMax.CourseID,
        CourseMax.Title,
        CourseMax.MaxNumber,
        Counts.Method
FROM    (
            SELECT  Counts.CourseID,
                    Counts.Title,
                    MAX(NumberMethodPerCourse) MaxNumber
            FROM    @Counts Counts
            GROUP BY    Counts.CourseID,
                        Counts.Title
        ) CourseMax INNER JOIN
        @Counts Counts  ON  CourseMax.CourseID = Counts.CourseID
                        AND CourseMax.MaxNumber = Counts.NumberMethodPerCourse