iT邦幫忙

0

MySQL資料處理

延續 SQL+PHP請教 的問題

加入以下幾種情況(表格下載連結)
https://ithelp.ithome.com.tw/upload/images/20180331/20106496boogFRLG6Y.jpg

請問如何產生以下表格?
https://ithelp.ithome.com.tw/upload/images/20180331/20106496uXiBepGv6o.jpg

謝謝

作業自己寫

2 個回答

1
暐翰
iT邦大師 1 級 ‧ 2018-03-31 17:33:40

第一版(未優化版本)

-- 測試資料建立
drop table Tem_Table;
create table Tem_Table (`No` int,`Qes` int,`User` nvarchar(20),`Ans` int,`Group` nvarchar(20) );
insert into Tem_Table (`No`,`Qes`,`User`,`Ans`,`Group`) values 
 (1,1,'Amy',5,'A'),(2,2,'Amy',4,'A') ,(3,3,'Amy',3,'A') ,(4,1,'May',5,'B') ,(5,2,'May',3,'B') ,(6,3,'May',2,'B') ,(7,4,'Jason',1,'A') ,(8,4,'Shelly',1,'A') ,(9,5,'Shelly',3,'A') ,(10,6,'Shelly',2,'A') ,(11,4,'Annie',1,'B') ,(12,6,'Annie',2,'B') ,(13,7,'Ray',4,'A') ,(14,9,'Ray',3,'A') ,(15,7,'Juliea',4,'B') ,(16,9,'Juliea',3,'B') ,(17,10,'Peter',4,'A') ,(18,11,'Peter',0,'A') ,(19,12,'Peter',3,'A') ,(20,10,'Wendy',4,'B') ,(21,11,'Wendy',0,'B') ,(22,12,'Wendy',3,'B')
    
;

#依照題目分組 每三題一組
CREATE TEMPORARY TABLE IF NOT EXISTS 依照題目分組 AS (
    SELECT user,`Group`,CEILING(max(QES)/3) '題組'  #使用向上取整
        ,count(1) '答題數'
    FROM Tem_Table 
    group by user,`Group`
    order by max(QES)
);
#select * from 依照題目分組;

#每組題目挑出答題最多的兩個人 (達到明顯未作三題且A、B兩組都只能派一人效果)
set @QesGroupRank := 0, @QesGroup := '',@tem_qes := 0;
CREATE TEMPORARY TABLE IF NOT EXISTS 篩選掉明顯未作題的人 AS (
    SELECT user,`Group`,`題組`
    FROM
     (SELECT *, 
                  @QesGroupRank := IF(@QesGroup = T100.題組, @QesGroupRank + 1, 1) AS QesGroupRank,
                  @QesGroup := 題組
       FROM 依照題目分組 T100
       ORDER BY 題組, 答題數 DESC
     ) ranked
    WHERE QesGroupRank <= 2   
);
#select * from 篩選掉明顯未作題的人;

#題組明細,每個人是否答完自己的題組
CREATE TEMPORARY TABLE IF NOT EXISTS 題組明細 
(`User` nvarchar(20),`Group` nvarchar(20),`題組` int ,`Qes` int,`Ans` int );

INSERT INTO 題組明細  select T100.*,1+3*(`題組`-1) `Qes`,T200.`ANS`
    from 篩選掉明顯未作題的人 T100
    left join Tem_Table T200 on T100.`User` = T200.`User` and T100.`Group` = T200.`Group` and 1+3*(`題組`-1) = T200.`Qes`     
;

INSERT INTO 題組明細  select T100.*,2+3*(`題組`-1) `Qes`,T200.`ANS`
    from 篩選掉明顯未作題的人 T100
    left join Tem_Table T200 on T100.`User` = T200.`User` and T100.`Group` = T200.`Group` and 2+3*(`題組`-1) = T200.`Qes`     
;

INSERT INTO 題組明細  select T100.*,3+3*(`題組`-1) `Qes`,T200.`ANS`
    from 篩選掉明顯未作題的人 T100
    left join Tem_Table T200 on T100.`User` = T200.`User` and T100.`Group` = T200.`Group` and 3+3*(`題組`-1) = T200.`Qes`     
;
#select * from 題組明細;

CREATE TEMPORARY TABLE IF NOT EXISTS A組 AS (
    select * from
    題組明細
    where `Group` = 'A'
);

CREATE TEMPORARY TABLE IF NOT EXISTS B組 AS (
    select * from
    題組明細
    where `Group` = 'B'
);

#找出有問題的題目
select distinct  
    T100.user Group_A , T200.user Group_B,T100.Qes,T100.`ANS` Group_A_ANS,T200.`ANS` Group_B_ANS
from A組 T100
left join B組 T200 on T100.`Qes` = T200.`Qes` and T100.`題組` = T200.`題組` 
where (T100.`ANS` <> T200.`ANS`) or (T100.`ANS` = 0 or T200.`ANS`=null) or (T100.`ANS` is null or T200.`ANS` is null)
;

以下結果圖:


發現卡滿多時間在oracle、mssql習慣不一樣的地方
算了這是題外話~

正題:
我改變一下,不只列出有問題題目,也把雙方答案都列出來
我有時間會優化
你先看一下,有問題跟我講:-)

看更多先前的回應...收起先前的回應...
小斑 iT邦新手 4 級 ‧ 2018-03-31 18:53:56 檢舉
  1. 不好意思,已有更新excel資料下載連結在發問內容中。

疑問1:
明明Juliea、Ray只做兩題為甚麼在結果資料裡?

只要有做題目,資料都會上傳到資料庫中。

疑問2:
Annie怎麼知道他少作第五題而不是第4第2題?

一人要做三題,Annie做了第4和第6題,因為推論她是少做第5題。

疑問3:
Shelly明明有作第5題,為何被列在結果資料裡面而不是Annie?

主要是要找出Ans兩兩不同的資料
Shelly有做第5題,Ans是5
Annie沒有做第5題
因此這題算是Ans兩兩不同
所以被列在結果資料裡有Qes第5題,它的Group_A是Shelly,Group_B是NULL(因為這題沒有人做)

疑問4:
是不是每一個人負責題目是三題
而題目都是從1~3,4~6,7~9這樣分題目
再分給A、B兩組中的一個人

是的


若有描述不清楚的地方請再告知我,謝謝

暐翰 iT邦大師 1 級 ‧ 2018-03-31 19:11:48 檢舉

一個人要做3題,明顯未做滿3題,不考慮此資料

疑問:這個條件來看,明明Juliea、Ray只做兩題為甚麼在你想要的結果裡。它們不是應該篩選掉嗎?

疑問:為甚麼有兩個A組成員 Jason跟Shelly在答第四題

小斑 iT邦新手 4 級 ‧ 2018-03-31 21:38:34 檢舉

疑問:這個條件來看,明明Juliea、Ray只做兩>題為甚麼在你想要的結果裡。它們不是應該篩選掉嗎?

Jason的情況是只做了1題,剩下都沒做,所以篩選掉。
Juliea和Ray的第2題雖然漏掉了,但有做到第3題,可能只是不小心漏掉第2題,所以保留。

疑問:為甚麼有兩個A組成員 Jason跟Shelly在答第四題

因為Jason會被篩選掉,所以A組成員需要有另一人來答這第4∼6題(題號)。

暐翰 iT邦大師 1 級 ‧ 2018-04-01 17:53:40 檢舉

今天公司再盤點 我明天再把剩下的寫完

小斑 iT邦新手 4 級 ‧ 2018-04-02 10:23:37 檢舉

好的,沒關係,真的很謝謝您

暐翰 iT邦大師 1 級 ‧ 2018-04-02 21:38:41 檢舉

我更新答案了
有問題再跟我說 :)

小斑 iT邦新手 4 級 ‧ 2018-04-12 15:20:55 檢舉

謝謝,我最近剛好在忙別的事情,會再找時間做這部分的

WilliamHuang
iT邦研究生 1 級 ‧ 2018-03-31 18:24:26
【**此則訊息已被站方移除**】

我要發表回答

立即登入回答