先說,這個寫法也許並不是最好的寫法 (應該有更好的寫法)
但可以解決問題
這個是 MSSQL的寫法 , MYSQL 應該也差不多

select A.A,B.B,C.C
from(
select ROW_NUMBER() OVER (order  by  id  ) as row_num ,A=id,B=0,C=0 
from accsubject
where field=1 ) A FULL JOIN
(
select ROW_NUMBER() OVER (order  by  id  ) as row_num, A=0,B=id,C=0
from accsubject
where field=2
) B
on A.row_num=B.row_num FULL JOIN
(
select ROW_NUMBER() OVER (order  by  id  ) as row_num,A=0,B=0,C=id
from accsubject
where field=3 ) C
ON A.row_num=C.row_num or B.row_num=C.row_num

那我分享一個mysql 5.6的版本
如果mysql支援PIVOT的話應該可以很簡短，不用這麼麻煩
目前因為已知分成ABC三個了，所以是寫死的，如果不確定field有多少種的話...
大概只能動態組合sql後再執行了，或是有更好的做法?

set @row0=0,@rowA=0,@rowB=0,@rowC=0;
-- 取得數量最多是哪一個Field，處理row number用
set @maxField = (select field from
(select field, count(1) cnt from accsubject group by field order by cnt desc limit 1)
t limit 1);    

select myIndex.row, ifnull(AA.id,'') A, ifnull(BB.id,'') B, ifnull(CC.id,'') C
from(
  -- row number
  select @row0 := @row0 + 1 row from accsubject where field=@maxField
) myIndex
left join
(
  -- A
  select @rowA:=@rowA+1 row, id from accsubject where field=1
) AA on myIndex.row=AA.row
left join
(
  -- B
  select @rowB:=@rowB+1 row, id from accsubject where field=2
) BB on myIndex.row=BB.row
left join
(
  -- C
  select @rowC:=@rowC+1 row, id from accsubject where field=3
) CC on myIndex.row=CC.row

結果見
http://sqlfiddle.com/#!9/24a4d78/25

這題..跟我上禮拜回答有一題一模一樣@@..
https://ithelp.ithome.com.tw/questions/10196340

這是MSSQL

declare @accsubject table(
	id varchar(4)
	,field int
)

INSERT INTO @accsubject 
VALUES ('1101', 3),('1102', 1),('1103', 1),('1104', 3)
,('1105', 3),('1141', 1),('1144', 1),('1149', 3)
,('1178', 1),('1201', 1),('1202', 2),('1203', 2)
,('1251', 3),('1252', 1),('1253', 1),('1257', 3)
,('1259', 1),('1281', 3),('1289', 1),('1402', 1)
,('1403', 1),('1428', 3),('1429', 1),('1444', 1)
,('1457', 1),('1501', 1),('1502', 2),('1521', 3)
,('1522', 2),('1541', 1),('1542', 2),('1551', 3)
,('1552', 2),('1821', 3),('1824', 3),('1825', 3)
,('1827', 3),('2102', 2),('2109', 2),('2141', 2)
,('2144', 2),('2147', 2),('2148', 2),('2154', 2)
,('2155', 2),('2156', 2),('2157', 2),('2175', 2)
,('2176', 3),('2177', 2),('2178', 2),('2251', 3)
,('2253', 2),('2259', 2),('2281', 2),('2504', 2)
,('2821', 2),('2824', 2),('2825', 2),('3101', 2)
,('3311', 2),('3312', 2),('3317', 2),('3318', 2)
,('3319', 2),('4101', 2),('4102', 1),('4103', 1)
,('4104', 3),('4105', 2),('4106', 2),('5101', 1)
,('5102', 1),('5103', 3),('5201', 1),('5203', 2)
,('5204', 2),('5205', 2),('6201', 3),('6203', 3)
,('6204', 3),('6205', 3),('6206', 1),('6207', 3)
,('6208', 1),('6209', 1),('6210', 3),('6211', 1)
,('6212', 1),('6213', 3),('6214', 1),('6215', 3)
,('6216', 3),('6217', 1),('6218', 1),('6219', 3)
,('6221', 1),('6222', 3),('6223', 1),('6224', 1)
,('6225', 3),('6226', 1),('6227', 1),('6228', 3)
,('6229', 1),('6245', 3),('6248', 1),('6249', 3)
,('7101', 2),('7102', 2),('7104', 2),('7105', 3)
,('7111', 2),('7112', 3),('7301', 1),('7302', 3)
,('7303', 1),('7305', 3),('7311', 3),('7312', 1)
,('7329', 3),('8101', 1)


select [1] A
,[2] B
,[3] C
from (
	select Row_Number()Over(partition by field order by id) x
	,field
	,id
	from @accsubject
) k
pivot(
	max(id)
	for field in([1],[2],[3])
) p

更新以ID做排序了

MySQL 8.0

select TempA.id,TempB.id,TempC.id
from (
select ROW_NUMBER() OVER (order by id) as row_num 
from accsubject
) as TempX
--
left join (
select ROW_NUMBER() OVER (order by id) as row_num,id,field 
from accsubject
where 1=1
and field=1
) as TempA on TempA.row_num=TempX.row_num
--
left join (
select ROW_NUMBER() OVER (order by id) as row_num,id,field 
from accsubject
where 1=1
and field=2
) as TempB on TempB.row_num=TempX.row_num
--
left join (
select ROW_NUMBER() OVER (order by id) as row_num,id,field 
from accsubject
where 1=1
and field=3
) as TempC on TempC.row_num=TempX.row_num
where 1=1
and not (
TempA.id=''
and TempB.id=''
and TempC.id=''
)

Demo

陪你玩一下好了

SELECT f1.id AS A,f2.id AS B,f3.id AS C FROM (
    SELECT @SN:=@SN+1 AS sn FROM accsubject,(SELECT @SN:= 0) AS sn
) AS mdb

LEFT JOIN (SELECT @SN1:=@SN1+1 as sn,id FROM accsubject,(SELECT @SN1:= 0) AS sn1 WHERE field=1) AS f1 ON mdb.sn=f1.sn
LEFT JOIN (SELECT @SN2:=@SN2+1 as sn,id FROM accsubject,(SELECT @SN2:= 0) AS sn2 WHERE field=2) AS f2 ON mdb.sn=f2.sn
LEFT JOIN (SELECT @SN3:=@SN3+1 as sn,id FROM accsubject,(SELECT @SN3:= 0) AS sn3 WHERE field=3) AS f3 ON mdb.sn=f3.sn

WHERE NOT (f1.id IS NULL AND f2.id  IS NULL AND f3.id IS NULL)

上面的寫法花了我1個多小時想。
原理如下。
1.直接拿主表當序號值。省下判斷最大序號。反正不管怎麼樣都不可能超過主表的行數
2.用left join將1 2 3表各別取出並做序號編號。並與主表的序號對應
3.主表已經有對應的序號了。依序帶入值
4.最後再將三個值都是null的清除。

好了。成果就如上了。

with t1 as (
select field
     , count(id) as cnt
  from accsubject
 group by field
), t2 as (
select max(cnt) as maxcnt
  from t1
), t3 as (
select n
  from t2
     , generate_series(1, t2.maxcnt) as g(n)
), t4 as (
select id
     , row_number() over(order by id) as sn
  from accsubject
 where field = 1
), t5 as (
select id
     , row_number() over(order by id) as sn
  from accsubject
 where field = 2
), t6 as (
select id
     , row_number() over(order by id) as sn
  from accsubject
 where field = 3
)
select t4.id as "A"
     , t5.id as "B"
     , t6.id as "C"
  from t3
  left join t4
    on (t3.n = t4.sn)
  left join t5
    on (t3.n = t5.sn)
  left join t6
    on (t3.n = t6.sn);

+------+------+------+
|  A   |  B   |  C   |
+------+------+------+
| 1102 | 1202 | 1101 |
| 1103 | 1203 | 1104 |
...
| 7301 | 5205 | 7311 |
| 7303 | 7101 | 7329 |
| 7312 | 7102 | ¤    |
| 8101 | 7104 | ¤    |
| ¤    | 7111 | ¤    |
+------+------+------+
(42 rows)

跟浩瀚星空大大一樣的思路.
話說哥哥你出題目,這資料筆數太多了一點,少一點也一樣吧.

T-SQL

DECLARE @table as Table(A int,B int,C int)
DECLARE @field as int
DECLARE @id as int
DECLARE @m as int 
Declare rs Cursor For SELECT * FROM accsubject ORDER BY id
OPEN rs
FETCH NEXT FROM rs INTO @id,@field
WHILE @@FETCH_STATUS = 0 
BEGIN 
	SELECT @m = @field % 3 
	IF @m=1 
		IF NOT EXISTS (SELECT * FROM @table WHERE A is null) INSERT INTO @table (A) VALUES (@id) 
		ELSE UPDATE t1 SET t1.A=@id FROM (SELECT TOP 1 A FROM @table WHERE A IS NULL) t1
	IF @m=2 
		IF NOT EXISTS (SELECT * FROM @table WHERE B is null) INSERT INTO @table (A) VALUES (@id) 
		ELSE UPDATE t1 SET t1.B=@id FROM (SELECT TOP 1 B FROM @table WHERE B IS NULL) t1
	IF @m=0 
		IF NOT EXISTS (SELECT * FROM @table WHERE C is null) INSERT INTO @table (A) VALUES (@id) 
		ELSE UPDATE t1 SET t1.C=@id FROM (SELECT TOP 1 C FROM @table WHERE C IS NULL) t1
	Fetch Next From rs Into @id , @field
END
Close rs 
Deallocate rs 
SELECT * FROM @table