关于统计的一些查询？
create table question_history(
ID int primary key identity(1,1) not null,
uID int not null,
questionID int not null,
true smallint not null
)
insert into question_history(uID,questionID,ture) value (123,800456,1)
insert into question_history(uID,questionID,ture) value (123,800456,0)
insert into question_history(uID,questionID,ture) value (123,800456,1)
insert into question_history(uID,questionID,ture) value (123,800123,1)
insert into question_history(uID,questionID,ture) value (123,800123,0)
insert into question_history(uID,questionID,ture) value (456,800456,1)
insert into question_history(uID,questionID,ture) value (456,800123,0)
insert into question_history(uID,questionID,ture) value (456,800123,1)
--查询某用户总做题数（去重）
select count(distinct(questionID)) from question_history
where uID = 123
--查询某用户总作对题数
select count(distinct(questionid)) from question_history
where uID = 123
and true = 1
历史记录数据较多
如果将以上两个查询合并为一个查询
另外一个问题，如果查询出这样的列表出来
uid,total,true
--------------------------
123 5 3
456 3 2
我知道了 是这样
create table question_history(
ID int primary key identity(1,1) not null,
uID int not null,
questionID int not null,
true smallint not null
)
insert into question_history(uID,questionID,true) values (123,800456,1)
insert into question_history(uID,questionID,true) values (123,800456,0)
insert into question_history(uID,questionID,true) values (123,800456,1)
insert into question_history(uID,questionID,true) values (123,800123,1)
insert into question_history(uID,questionID,true) values (123,800123,0)
insert into question_history(uID,questionID,true) values (456,800456,1)
insert into question_history(uID,questionID,true) values (456,800123,0)
insert into question_history(uID,questionID,true) values (456,800123,1)
SELECT
UID,
COUNT(DISTINCT questionID) AS TOTAL,
SUM(CASE WHEN true=1 THEN 1 ELSE 0 END) AS 'TRUE'
FROM
(SELECT DISTINCT uID,questionID,true FROM question_history)t
GROUP BY
UID
DROP TABLE question_history
/*UID TOTAL TRUE
----------- ----------- -----------
123 2 2
456 2 2
(2 行受影响)*/
