IF OBJECT_ID('Score') IS NOT NULL
DROP TABLE Score
GOIF OBJECT_ID('Member') IS NOT NULL
DROP TABLE Member
Create Table Member (
MID Char(2) Primary Key,
MName Char(4)
);
GOINSERT INTO Member
SELECT '01','张萨' UNION ALL
SELECT '02','王强' UNION ALL
SELECT '03','李三' UNION ALL
SELECT '04','李四'
GOIF OBJECT_ID('Course') IS NOT NULL
DROP TABLE Course
Create Table Course (
FID Char(2) Primary Key,
FName Char(4)
);
GO
INSERT INTO Course
SELECT '01','语文' UNION ALL
SELECT '02','数学' UNION ALL
SELECT '03','英语' UNION ALL
SELECT '04','历史'
GOIF OBJECT_ID('Score') IS NOT NULL
DROP TABLE Score
Create Table Score (
SID int IDENTITY(1,1),
FID Char(2) REFERENCES Course(FID),
MID Char(2) REFERENCES Member(MID),
Score Int
);
GO
INSERT INTO Score
SELECT '01','01',78 UNION ALL
SELECT '01','02',89 UNION ALL
SELECT '01','03',70 UNION ALL
SELECT '01','04',80 UNION ALL
SELECT '02','01',67 UNION ALL
SELECT '02','02',67 UNION ALL
SELECT '02','03',87 UNION ALL
SELECT '02','04',78 UNION ALL
SELECT '03','01',89 UNION ALL
SELECT '03','02',84 UNION ALL
SELECT '03','03',92 UNION ALL
SELECT '03','04',97 UNION ALL
SELECT '04','01',76 UNION ALL
SELECT '04','02',96 UNION ALL
SELECT '04','03',56 UNION ALL
SELECT '04','04',66GO--行列转换
SELECT MName 姓名,
语文=SUM(case when B.FID='01' then ISNULL(C.Score,0) else 0 end),
数学=SUM(case when B.FID='02' then ISNULL(C.Score,0) else 0 end),
英语=SUM(case when B.FID='03' then ISNULL(C.Score,0) else 0 end),
历史=SUM(case when B.FID='04' then ISNULL(C.Score,0) else 0 end)
From Member a,Course b,Score c Where C.FID=B.FID AND C.MID=A.MID GROUP BY MName
/*
姓名 语文 数学 英语 历史
李三 70 87 92 56
李四 80 78 97 66
王强 89 67 84 96
张萨 78 67 89 76
*/
DROP TABLE Score
GOIF OBJECT_ID('Member') IS NOT NULL
DROP TABLE Member
Create Table Member (
MID Char(2) Primary Key,
MName Char(4)
);
GOINSERT INTO Member
SELECT '01','张萨' UNION ALL
SELECT '02','王强' UNION ALL
SELECT '03','李三' UNION ALL
SELECT '04','李四'
GOIF OBJECT_ID('Course') IS NOT NULL
DROP TABLE Course
Create Table Course (
FID Char(2) Primary Key,
FName Char(4)
);
GO
INSERT INTO Course
SELECT '01','语文' UNION ALL
SELECT '02','数学' UNION ALL
SELECT '03','英语' UNION ALL
SELECT '04','历史'
GOIF OBJECT_ID('Score') IS NOT NULL
DROP TABLE Score
Create Table Score (
SID int IDENTITY(1,1),
FID Char(2) REFERENCES Course(FID),
MID Char(2) REFERENCES Member(MID),
Score Int
);
GO
INSERT INTO Score
SELECT '01','01',78 UNION ALL
SELECT '01','02',89 UNION ALL
SELECT '01','03',70 UNION ALL
SELECT '01','04',80 UNION ALL
SELECT '02','01',67 UNION ALL
SELECT '02','02',67 UNION ALL
SELECT '02','03',87 UNION ALL
SELECT '02','04',78 UNION ALL
SELECT '03','01',89 UNION ALL
SELECT '03','02',84 UNION ALL
SELECT '03','03',92 UNION ALL
SELECT '03','04',97 UNION ALL
SELECT '04','01',76 UNION ALL
SELECT '04','02',96 UNION ALL
SELECT '04','03',56 UNION ALL
SELECT '04','04',66GO--行列转换
SELECT MName 姓名,
语文=SUM(case when B.FID='01' then ISNULL(C.Score,0) else 0 end),
数学=SUM(case when B.FID='02' then ISNULL(C.Score,0) else 0 end),
英语=SUM(case when B.FID='03' then ISNULL(C.Score,0) else 0 end),
历史=SUM(case when B.FID='04' then ISNULL(C.Score,0) else 0 end)
From Member a,Course b,Score c Where C.FID=B.FID AND C.MID=A.MID GROUP BY MName
/*
姓名 语文 数学 英语 历史
李三 70 87 92 56
李四 80 78 97 66
王强 89 67 84 96
张萨 78 67 89 76
*/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货