SQL连接查询如何

Question

CREATE TABLE interview (uniqueID int identity(1,1), 
                        date datetime, 
                        recordtype int, 
                        amount numeric(18, 4))

INSERT INTO interview values('6/30/13', 1, 27.95)
INSERT INTO interview values('5/20/13', 1, 21.85)
INSERT INTO interview values('5/22/13', 2, 27.90)
INSERT INTO interview values('12/11/12', 2, 23.95)
INSERT INTO interview values('6/13/13', 3, 24.90)
INSERT INTO interview values('6/30/13', 2, 27.95)
INSERT INTO interview values('5/20/13', 2, 21.85)
INSERT INTO interview values('5/22/13', 1, 27.90)
INSERT INTO interview values('12/11/12',1, 23.95)
INSERT INTO interview values('6/13/13', 3, 24.90)
INSERT INTO interview values('6/30/13', 3, 27.95)
INSERT INTO interview values('5/20/13', 3, 21.85)
INSERT INTO interview values('5/22/13', 2, 27.90)
INSERT INTO interview values('12/11/12', 1, 23.95)
INSERT INTO interview values('6/13/13', 1, 24.90)

如何得到以下结果？查询会是什么样子？

​

​

我只能勉强工作，但我的答案是不正确的。我需要以某种方式加入查询。

select distinct date, count(RecordType)as Count_unique1
from interview
where RecordType = '1'
group by date

select distinct date, count(RecordType)as Count_unique2
from interview
where RecordType = '2'
group by date

select distinct date, count(RecordType)as Count_unique3
from interview
where RecordType = '3'
group by date

Answer 1

select 
date, 
sum(case when RecordType = '1' then 1 else 0 end) as Count_unique1,
sum(case when RecordType = '2' then 1 else 0 end) as Count_unique2,
sum(case when RecordType = '3' then 1 else 0 end) as Count_unique3
from interview
group by date

Answer 2

如果RecordType是1,2和3，在所有情况下，这就足够了。

select date, 
sum(RecordType = '1') as Count_unique1,
sum(RecordType = '2') as Count_unique2,
sum(RecordType = '3') as Count_unique3
from interview
group by date