받은 Grade 뿐 아니라 Grade 는 다 나오게 하고 싶은데 잘 안되네요.
Grade | Cnt |
A | 2 |
B | 1 |
C | 0 |
D | 0 |
CREATE TABLE [dbo].[StudentGrade](
[Name] [nvarchar](50) NULL,
[Subject] [nvarchar](50) NULL,
[Grade] [nvarchar](50) NULL
) ON [PRIMARY]
--INSERT INTO [UMS].[dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Kim', 'KOR', 'A')
--INSERT INTO [UMS].[dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Kim', 'ENG', 'A')
--INSERT INTO [UMS].[dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Kim', 'Math', 'B')
이것 만으로 안돼서 Grade 테이블을 만들었는데...
CREATE TABLE [dbo].[Grade](
[Grade] [nvarchar](50) NULL
) ON [PRIMARY]
--INSERT INTO [UMS].[dbo].[Grade] ([Grade]) VALUES ('A')
--INSERT INTO [UMS].[dbo].[Grade] ([Grade]) VALUES ('B')
--INSERT INTO [UMS].[dbo].[Grade] ([Grade]) VALUES ('C')
--INSERT INTO [UMS].[dbo].[Grade] ([Grade]) VALUES ('D')
select g.Grade, count(*) from Grade as g
Left Outer Join studentGrade as s on (g.Grade = s.Grade)
where Name = 'Kim'
group by g.Grade
도움 요청합니다.
Comment 2
-
이리
2015.06.10 09:03
SELECT g.Grade, count(s.Grade)FROM Grade AS gLEFT OUTER JOIN studentGrade AS sON (g.Grade = s.GradeAND s.NAME = 'Kim')GROUP BY g.Grade -
한태
2016.02.04 20:09
CREATE TABLE [dbo].[StudentGrade](
[Name] [nvarchar](50) NULL,
[Subject] [nvarchar](50) NULL,
[Grade] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Kim', 'KOR', 'A')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Kim', 'ENG', 'A')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Kim', 'Math', 'B')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Han', 'KOR', 'A')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Han', 'ENG', 'D')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('Han', 'Math', 'C')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('M', 'KOR', 'B')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('M', 'ENG', 'D')
INSERT INTO [dbo].[StudentGrade] ([Name],[Subject],[Grade]) VALUES ('M', 'Math', 'B')
CREATE TABLE [dbo].[Grade](
[Grade] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[Grade] ([Grade]) VALUES ('A')
INSERT INTO [dbo].[Grade] ([Grade]) VALUES ('B')
INSERT INTO [dbo].[Grade] ([Grade]) VALUES ('C')
INSERT INTO [dbo].[Grade] ([Grade]) VALUES ('D')
select g.grade, s.cnt
from (select grade, COUNT(*) cnt
from StudentGrade
where Name='Kim'
group by grade)s right outer join Grade g
on s.Grade=g.Grade
