0

## （已解決）SQL+PHP請教

table如下：

``````No Qes User Ans Group
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
``````

``````No Group_A Group_B Qes
1    Amy     May    2
2    Amy     May    3
``````

### 1 個回答

1

iT邦大師 2 級 ‧ 2018-03-28 16:00:05

``````--測試資料建立
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')
;

--組合你要的結果
select
ROW_NUMBER() OVER (order by T300.Qes) No,
T300.Group_A,
T300.Group_B,
T300.Qes
from (
select
case
when T100.[Group] = 'A' then T100.[User]
when T200.[Group] = 'A' then T200.[User]
end
as Group_A,
case
when T100.[Group] = 'B' then T100.[User]
when T200.[Group] = 'B' then T200.[User]
end
as Group_B,
T100.Qes Qes,
ROW_NUMBER() OVER (PARTITION BY T100.Qes order by T100.Qes) [rank]
from #Tem_Table T100
inner join #Tem_Table T200 on T100.qes = T200.qes and T100.ans <> T200.ans
) as T300
where T300.[rank] = 1
``````

#### 更新MySQL版本:

``````-- 測試資料建立
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')
;

select
@rownum:=@rownum + 1 as No,T300.*
from (
select DISTINCT
case
when T100.Group = 'A' then T100.User
when T200.Group = 'A' then T200.User
end
as Group_A,
case
when T100.Group = 'B' then T100.User
when T200.Group = 'B' then T200.User
end
as Group_B,
T100.Qes Qes
from Tem_Table as T100
inner join Tem_Table as T200 on T100.qes = T200.qes and T100.ans <> T200.ans
) as T300
,(SELECT @rownum := 0) r;
``````

# 原理:

distinct是因為使用inner join會產生重複資料，要篩選掉，所以使用

1. T200是指什麼
2. 這句→T100.Qes Qes
3. 這句→,(SELECT @rownum := 0) r;

T200是指什麼

T100.Qes Qes

(SELECT @rownum := 0) r;