时间戳

首页 > 解决方案 > 如何使两列枢轴和其他正常

sql-server - 如何使两列枢轴和其他正常

问题描述

我有 3 个表名类，学生和宗教，以下数据如下

班级表

ClassId     ClassName
  1         class-1
  2         class-2
  3         class-3

宗教表 

ReligionId    RegionName
  1           Hindu
  2           Muslim

学生桌

employeeid  StudentName religionid  dateofbirth classid
    1         A               1     1990-12-04    1
    2         B               2     1999-12-04    2
    3         C               2     2000-12-04    1
    4         D               2     1988-12-04    1
    5         E               2     2003-12-04    2
    6         F             NULL    2002-12-04    1

如何从上表中获得以下记录

CLASSNAME  HINDU   MUSLIM  Noreligion
  class-1     1     2           1
  class-2     0     2           0

您可以从以下脚本创建表

CREATE TABLE [dbo].[class](
    [ClassId] [int] IDENTITY(1,1) NOT NULL,
    [ClassName] [varchar](250) NULL,
 CONSTRAINT [PK_class] PRIMARY KEY CLUSTERED 
(
    [ClassId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Religion](
    [ReligionId] [int] NOT NULL,
    [RegionName] [varchar](50) NULL,
 CONSTRAINT [PK_Religion] PRIMARY KEY CLUSTERED 
(
    [ReligionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Student](
    [employeeid] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [varchar](150) NULL,
    [religionid] [int] NULL,
    [dateofbirth] [date] NULL,
    [classid] [int] NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[class] ON 

INSERT [dbo].[class] ([ClassId], [ClassName]) VALUES (1, N'class-1')
INSERT [dbo].[class] ([ClassId], [ClassName]) VALUES (2, N'class-2')
INSERT [dbo].[class] ([ClassId], [ClassName]) VALUES (3, N'class-3')
SET IDENTITY_INSERT [dbo].[class] OFF
INSERT [dbo].[Religion] ([ReligionId], [RegionName]) VALUES (1, N'hindu')
INSERT [dbo].[Religion] ([ReligionId], [RegionName]) VALUES (2, N'muslim')
SET IDENTITY_INSERT [dbo].[Student] ON 

INSERT [dbo].[Student] ([employeeid], [StudentName], [religionid], [dateofbirth], [classid]) VALUES (1, N'A', 1, CAST(N'1990-12-04' AS Date), 1)
INSERT [dbo].[Student] ([employeeid], [StudentName], [religionid], [dateofbirth], [classid]) VALUES (2, N'B', 2, CAST(N'1999-12-04' AS Date), 2)
INSERT [dbo].[Student] ([employeeid], [StudentName], [religionid], [dateofbirth], [classid]) VALUES (3, N'C', 2, CAST(N'2000-12-04' AS Date), 1)
INSERT [dbo].[Student] ([employeeid], [StudentName], [religionid], [dateofbirth], [classid]) VALUES (4, N'D', 2, CAST(N'1988-12-04' AS Date), 1)
INSERT [dbo].[Student] ([employeeid], [StudentName], [religionid], [dateofbirth], [classid]) VALUES (5, N'E', 2, CAST(N'2003-12-04' AS Date), 2)
INSERT [dbo].[Student] ([employeeid], [StudentName], [religionid], [dateofbirth], [classid]) VALUES (6, N'F', NULL, CAST(N'2002-12-04' AS Date), 1)
SET IDENTITY_INSERT [dbo].[Student] OFF

是否需要使用数据透视表。我无法进行查询以获取此类记录。

标签： sql-serversql-server-2008

解决方案

这是比旋转更具条件的聚合。这将为您提供您想要的结果：

SELECT C.ClassName,
       COUNT(CASE R.RegionName WHEN 'Hindu' THEN 1 END) AS Hindu,
       COUNT(CASE R.RegionName WHEN 'Muslim' THEN 1 END) AS Muslim,
       COUNT(CASE WHEN  R.RegionName IS NULL THEN 1 END) AS NoReligion
FROM dbo.class C
     JOIN dbo.Student S ON C.ClassId = S.classid
     LEFT JOIN dbo.Religion R ON S.religionid = R.ReligionId
GROUP BY C.ClassName;

如果您不理解语法，请询问。

编辑：OP 现在已经声明，除了他们提供的 2 个宗教之外，还有更多的宗教，并且需要是动态的。因此，这给出：

--Additional sample row if you wish:
INSERT [dbo].[Religion] ([ReligionId], [RegionName])
VALUES (3, N'Catholic');
GO

DECLARE @SQL nvarchar(MAX);

SET @SQL = N'SELECT C.ClassName,' + NCHAR(10) +
           STUFF((SELECT N',' + NCHAR(10) +
                         N'       COUNT(CASE R.RegionName WHEN ' + QUOTENAME(R.RegionName,N'''') + N' THEN 1 END) AS ' + QUOTENAME(R.RegionName)
                  FROM dbo.Religion R
                  ORDER BY R.ReligionId
                  FOR XML PATH(N'')),1,2,N'') + N',' + NCHAR(10) +
           N'COUNT(CASE WHEN  R.RegionName IS NULL THEN 1 END) AS NoReligion' + NCHAR(10) +
           N'FROM dbo.class C' + NCHAR(10) +
           N'     JOIN dbo.Student S ON C.ClassId = S.classid' + NCHAR(10) +
           N'     LEFT JOIN dbo.Religion R ON S.religionid = R.ReligionId' + NCHAR(10) +
           N'GROUP BY C.ClassName;';

SELECT @SQL;
EXEC sp_executesql @SQL;

推荐阅读