iT邦幫忙

0

趣味SQL 排排站吃菓菓

我的比一級屠豬士大大簡單多了,

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

規則很簡單,
field=1 , 排在 A 欄
field=2 , 排在 B 欄
field=3 , 排在 C 欄

答案如下,請問 sql 指令如何下?

A B C
1102 1202 1101
1103 1203 1104
1141 1502 1105
1144 1522 1149
1178 1542 1251
1201 1552 1257
1252 2102 1281
1253 2109 1428
1259 2141 1521
1289 2144 1551
1402 2147 1821
1403 2148 1824
1429 2154 1825
1444 2155 1827
1457 2156 2176
1501 2157 2251
1541 2175 4104
4102 2177 5103
4103 2178 6201
5101 2253 6203
5102 2259 6204
5201 2281 6205
6206 2504 6207
6208 2821 6210
6209 2824 6213
6211 2825 6215
6212 3101 6216
6214 3311 6219
6217 3312 6222
6218 3317 6225
6221 3318 6228
6223 3319 6245
6224 4101 6249
6226 4105 7105
6227 4106 7112
6229 5203 7302
6248 5204 7305
7301 5205 7311
7303 7101 7329
7312 7102
8101 7104
  7111
看更多先前的討論...收起先前的討論...
好長0.0
這個已經不算趣味了吧。
ccutmis iT邦高手 8 級 ‧ 2019-12-12 09:18:10 檢舉
選我正解~ 咯!咯!咯!咯!咯!
https://www.youtube.com/watch?v=3m6KeuVY5Fo
舜~ iT邦高手 1 級 ‧ 2019-12-12 09:21:57 檢舉
好像只能透過PIVOT ?
ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 09:25:17 檢舉
insert 的資料長一點而已啦,
規則很簡單,解題指令也很短。
這題麻煩的地方在 不一樣多.....
slime iT邦大師 1 級 ‧ 2019-12-12 11:48:47 檢舉
複製到 Excel 工作表 1
篩選 field = 1 , 複製到工作表 2 的 A 欄
篩選 field = 2 , 複製到工作表 2 的 B 欄
篩選 field = 3 , 複製到工作表 2 的 C 欄
SQL 建一個新表格
把工作表 2 複製到新表格內
(以上純屬惡搞)

對不起補一下 SQL :
select * from 新表格
複製到Excel 這招別出心裁.
ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 14:36:02 檢舉
不管黑貓白貓,能抓住老鼠就是好貓。
ckp6250 iT邦研究生 4 級 ‧ 2019-12-13 10:51:48 檢舉
感謝各位捧場,戲法人人會變,各有巧妙不同,只要有答案,就是好答案。

我個人的做法是
```
with temp2 AS (
with temp as (
SELECT
id,
field,
ROW_NUMBER() OVER (PARTITION BY field ORDER BY id ) AS row_num
FROM accsubject
)
SELECT
row_num,
IF( field = 1, id, '' ) AS `A`,
IF( field = 2, id, '' ) AS `B`,
IF( field = 3, id, '' ) AS `C`
FROM temp
GROUP BY row_num,field
)
SELECT
max( A ) AS A,
max( B ) AS B,
max( C ) AS C
FROM temp2
GROUP BY row_num ;
```

為什麼不會縮排?
0
twyes
iT邦新手 4 級 ‧ 2019-12-12 09:16:18

先說,這個寫法也許並不是最好的寫法 (應該有更好的寫法)
但可以解決問題
這個是 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

https://ithelp.ithome.com.tw/upload/images/20191212/20123199pIkfNg6q4N.jpg

0
舜~
iT邦高手 1 級 ‧ 2019-12-12 10:47:26

那我分享一個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

舜~ iT邦高手 1 級 ‧ 2019-12-12 10:49:15 檢舉
0
純真的人
iT邦高手 1 級 ‧ 2019-12-12 10:51:48

這題..跟我上禮拜回答有一題一模一樣@@..
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做排序了

ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 15:05:43 檢舉

感謝捧場,
麻煩答案上再加個排序一下。

ckp6250
我改SQL了~以ID做排序~

0
rogeryao
iT邦高手 1 級 ‧ 2019-12-12 11:34:04

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

看更多先前的回應...收起先前的回應...

一直想問,為何都會出現 where 1 = 1

老一輩的寫法。然後又有傳言說用 1=1 查尋會變快。(對岸文意亂說的東東)
所以就會很常出現了。

雖然我完全不想這樣用。

rogeryao iT邦高手 1 級 ‧ 2019-12-12 11:55:41 檢舉

習慣寫法,下一個條件通常接 and 或是 or ;動態組 SQL 時就不用去處理 where 後面的條件

以前用來組條件時, where 1 = 1 開頭,比較好寫後續組合.
這個我經歷過,我知道.
但是之前我有看到一個小朋友在鐵人賽的,他以為要 where 1 = 1
才能做 cross join.
這個誤解大了.

其實我早期沒用框架。在自已組sql。是只用 where 1 的
後來有聽說這樣會讓mysql跑額外建表。所以也改用1=1

其最大的目的,的確是為了方便組sql where用。

但在後期,不知道為何開始傳出 1=1 是必要性的傳言。
還有人說加上1=1。查尋速度會快一倍。

這還造成後輩學習sql語法。誤解了1=1的原本用意了。
在框架的時代裏,1=1已經成為過去式的做法了。
但1=1還一直存在誤解及誤會在網路上流傳。

ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 14:45:14 檢舉

【加上1=1。查尋速度會快一倍。】
快一倍?打死我都不相信。

那個1=1跟SQL的確無關...
主要如以上大大所言~是搭配程式語言~方便組SQL字串條件的~

0
浩瀚星空
iT邦超人 1 級 ‧ 2019-12-12 11:36:07

陪你玩一下好了

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的清除。

好了。成果就如上了。

對了對了,有誰可以幫我試試。能否用在mssql上。
手頭沒mssql可以玩。

寫這段其實是因為某篇的影響。所以才想寫個萬用的。
搞死我不少的腦細胞。

ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 14:55:46 檢舉

搞死我不少的腦細胞。

這都要怪一級屠豬士啦,都是他歹鬼仔帶頭。

0
一級屠豬士
iT邦大師 1 級 ‧ 2019-12-12 12:42:59
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)

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

看更多先前的回應...收起先前的回應...
ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 14:34:48 檢舉

資料是可以少一點沒錯啦,
只是,我直接由現成資料表中倒出來,懶得去整理。

突然發現你的還有做排序。我好像忘了這一點了。

星空大,換你出題了吧 /images/emoticon/emoticon82.gif

ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 15:02:13 檢舉

紅蘿蔔蹲,紅蘿蔔蹲,紅蘿蔔蹲完黃蘿蔔蹲。
https://ithelp.ithome.com.tw/upload/images/20191212/20119662olxwxnVxN0.png

汗!
因該要叫叔叔出才對,我很懶說。

而且如果我出題的話,因該會很奇特的問題吧。
好吧。我想想要出什麼題目好了。

現在腦袋空白中。

ckp6250 iT邦研究生 4 級 ‧ 2019-12-12 15:13:30 檢舉

https://ithelp.ithome.com.tw/upload/images/20191212/20119662VJyLOTiuTU.jpg

0
japhenchen
iT邦高手 1 級 ‧ 2019-12-13 17:00:46

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
B       C       A
----    ----    ----
1101	1102	1202
1103	1141	1203
1104	1144	1502
1105	1178	1522
1149	1201	1542
1251	1252	1552
1253	1259	2102
1257	1289	2109
1281	1402	2141
1403	1429	2144
1428	1444	2147
1457	1501	2148
1521	1541	2154
1551	4102	2155
1821	4103	2156
1824	5101	2157
1825	5102	2175
1827	5201	2177
2176	6206	2178
2251	6208	2253
2259	6209	2281
2504	6211	2821
2824	6212	2825
3101	6214	3311
3312	6217	3317
3318	6218	3319
4101	6221	4105
4104	6223	4106
5103	6224	5203
5204	6226	5205
6201	6227	7101
6203	6229	7102
6204	6248	7104
6205	7301	7111
6207	7303	NULL
6210	7312	NULL
6213	8101	NULL
6215	NULL	NULL
6216	NULL	NULL
6219	NULL	NULL
6222	NULL	NULL
6225	NULL	NULL
6228	NULL	NULL
6245	NULL	NULL
6249	NULL	NULL
7105	NULL	NULL
7112	NULL	NULL
7302	NULL	NULL
7305	NULL	NULL
7311	NULL	NULL
7329	NULL	NULL
ckp6250 iT邦研究生 4 級 ‧ 2019-12-14 05:26:50 檢舉

答案不太對,
例如,【8101】的 field=1 , 應該在排第 A 欄才對。

SORRY我發現是我沒把複製下來的SQL改欄位名了
https://ithelp.ithome.com.tw/upload/images/20191214/20117954qAyrgz0s3n.png
改這樣就對了

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 (B) 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 (C) 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

我要發表回答

立即登入回答