SQLServer ストアドにてカーソルの記述方法を教えてください。（採番取得）

Question

美容学校システムにてSQLServerを使用

■やりたいこと
採番ルールによる番号を、学生ごとに付与します。

■採番ルール
クラスA～Fを全体を一括で採番します。　
現行：クラス順、名前ふりがな順の連番
改修：クラス順の中で、Eクラスのみ①②の順で採番をしたい。
①専攻=ネイル → 名前ふりがな
②専攻=エステ → 名前ふりがな
③専攻=①②以外→名前ふりがな

■求めたい結果
A～Dクラスは、クラス順、名前ふりがな順で採番
Eクラスは、専攻順、名前ふりがな順で採番
例：
あんどう　ネイル　1
いとう　 ネイル 2
あいだ　 エステ 3
いがわ　 エステ 4　
うらた　 エステ 5　
FクラスがEクラスのMAX番号からクラス順、名前ふりがな順で採番

■PGM（ストアド）
学生テーブルから「クラス」「名前ふりがな」「専攻」のカーソルを回し
採番された値を同テーブル「番号」にUPDATEしていく仕組みになっております。

既存のカーソル部分（ストアドより）
DECLARE CUR_S CURSOR FOR
SELECT 学生NO
FROM 学生TABLE
ORDER BY クラス,名前,ふりがな

■対象テーブル
学生テーブル
「学生NO」
「名前ふりがな」
「クラス」・・・A～F
「専攻」・・・「ネイル」「エステ」など
「番号」・・・採番してupdate

このカーソルのSELECT文をどのように改修したらよいか
ご教授いただけたらと存じます。

---

求めたい結果が変更がありました。

■採番ルール
入学年度順→クラス順
└Ｅクラス：専攻順→学生NO順
└他クラス：学生NO順→専攻順

■データ
Aクラス
001　　専攻05　　2021年入学
002　　専攻03　　2019年入学
003　　専攻01　　2021年入学

Eクラス
004　　専攻05　　2019年入学
005　　専攻03　　2021年入学
006　　専攻01　　2021年入学

Fクラス
007　　専攻05　　2019年入学
008　　専攻03　　2021年入学
009　　専攻01　　2021年入学

■求めたい順
002　　専攻03　　2019年入学 A
004　　専攻05　　2019年入学 E
007　　専攻05　　2019年入学 F
001　　専攻05　　2021年入学 A
003　　専攻01　　2021年入学 A
006　　専攻01　　2021年入学 E
005　　専攻03　　2021年入学 E
008　　専攻03　　2021年入学 F
009　　専攻01　　2021年入学 F

Comments

[sousuke]

学生テーブルの定義を提示したほうがよろしいかと

[Beer_Rabbit]

ご指摘ありがとうございます。早速、学生テーブルを反映してみます。

Answer 1

クラス＋(専攻順)の列で並び替えてrow_numberでいいような？
カーソル使ってももちろんいいですし、カーソルの勉強したかったらすみませんが。

select *,ROW_NUMBER() over(order by クラス専攻順,名前,ふりがな) as 採番
from (
SELECT 学生NO,クラス,名前,ふりがな,(専攻順)
  ,クラス + (専攻順) as クラス専攻順
FROM 学生TABLE
--ORDER BY クラス専攻順,名前,ふりがな
)

Eクラス以外でも全員の専攻が同じ前提があるなら
Eクラスだけ専攻を見るのではなく全クラス専攻双方をみて並べればいいと思います。
それ以外の状態だったとしてもcase when クラス='E' then とかすれば並び順は自由にできます。

追記

SELECT 学生NO,クラス,名前,ふりがな,専攻
  ,ROW_NUMBER() over(order by クラス,専攻 desc,名前,ふりがな) as 採番
FROM 学生TABLE
ORDER BY 採番

純粋な方を先に書けばよかった…

仕様変更

こうかな？order by内でもcaseは切れます。
前半はテストテーブル作成用なので下３行だけ

;with 学生テーブル(クラス,学生NO,専攻,入学年度) as (
	select 'A' ,'001','専攻05','2021年入学'
	union all select 'A' ,'002','専攻03','2019年入学'
	union all select 'A' ,'003','専攻01','2021年入学'

	union all select 'E' ,'004','専攻05','2019年入学'
	union all select 'E' ,'005','専攻03','2021年入学'
	union all select 'E' ,'006','専攻01','2021年入学'

	union all select 'F' ,'007','専攻05','2019年入学'
	union all select 'F' ,'008','専攻03','2021年入学'
	union all select 'F' ,'009','専攻01','2021年入学'
)

select *
	,ROW_NUMBER() over(order by 入学年度,クラス,case when クラス='E' then 専攻 end,学生NO,専攻) as 採番
from 学生テーブル
order by 採番
コード

要件に「他クラス　学生NO→専攻順」とありますが
よく考えたら学生NOで並べたら専攻で並べる意味はないです。多分。

select *
	,ROW_NUMBER() over(order by 入学年度,クラス,case when クラス='E' then 専攻 end,学生NO) as 採番
from 学生テーブル
order by 採番

なので最後の専攻のorderは外しても結果は同じです。

Comments

[Beer_Rabbit]

ご回答いただきありがとうございます。
早速試してみます！

[Beer_Rabbit]

追記くださり本当にありがとうございます。試してみます。

[Beer_Rabbit]

ご教授いただいた内容が大変ヒントになり、
結果求めたい採番順に並び替えることができました。
この度はありがとうございました。

[Beer_Rabbit]

結果、下記で対応しました。

①Ｅクラス：専攻→ふりがな順
②他クラス：ふりなが→専攻順

SELECT 学生NO,クラス,名前,ふりがな,専攻
,CASE クラス WHEN 'E' THEN ROW_NUMBER() over(order by クラス,専攻,ふりがな)
ELSE ROW_NUMBER() over(order by クラス,ふりがな,専攻)
END AS 採番順
FROM 学生TABLE
ORDER BY クラス,採番順

[sousuke]

E以外のクラスの専攻は全員同じではないのでしょうか？
あと専攻がネイル、エステの順に並ばないのではと思いましたが特に問題なければいいのですが。

専攻がクラス単位で全員同じの場合は
「order by クラス,専攻,ふりがな」
「order by クラス,ふりがな,専攻」
の並び順は同じになるのでcase when はいらないと思いましたが
なぜcase文を使うことになったのかわからないですね…
解決しているのにアレですがちょっともったいないです。

[Beer_Rabbit]

ご指摘くださり大変ありがとうございます。
データと採番ルールに変更があり、質問内容の後半に追記しました。
この場合ですと、SQLの記述はどうなりますでしょうか。

[Beer_Rabbit]

早速、試しました！
求めたい結果が得られることができ感動しました。
caseの使いどころが、order by内でするという部分ということで腑に落ちました。
お聞きしてよかったです。

[Beer_Rabbit]

たびたび失礼いたします。

たとえば、学生NOに重複データが存在する場合、
ご教授いただいたSQLでは、
他クラスの場合に「学生NO→専攻順」にならないということですよね。

エラーがでてしまいうまくいってないのですが。。。。
下記を考えていました。

over(order by 入学年度,クラス
　　,case when クラス='E' then 専攻 else 学生NO end
　　,case when クラス='E' then 学生NO else 専攻 end　) as 採番

[Beer_Rabbit]

なぜか・・・ELSEを足すとエラーとなり
「varchar の値 をデータ型 int に変換できませんでした。」

CASTした結果はうまくいきました。
over(order by 入学年度,クラス
　　,case when クラス='E' then CAST(専攻 AS VARCHAR)
　　　　　else CAST(学生NO AS VARCHAR) end
　　,case when クラス='E' then CAST(学生NO AS VARCHAR)
　　　　　else CAST(専攻 NO AS VARCHAR) end　) as 採番

[sousuke]

学生NOと専攻はサンプルではどう見ても文字列ですが、ここに載せるにあたって
違うデータを乗せているのであればこちらからは見えていません。

また学生NOは重複するんですか？学生NOより先に入学年度とクラスで並び替えているので
「同じ入学年度で同じクラスで同じ学生NOの学生が専攻違いで複数人存在する」必要がありますが
一般的に考えてそのようなことはありえないと思います。

上記の前提では上のelseがあってもなくても結果は同じはずで
わざわざelseを書く必要性が感じられないです。
order by 入学年度,クラス,case when クラス='E' then 専攻 end,学生NO

またデータベースの質問をする場合はテーブルのcreate文を載せたほうがいいです。
SQL Server Management Studioを使っているなら
テーブルを右クリック→テーブルをスクリプト化→新規作成→新しいクエリ
で「テーブルの定義」が出ますのでそれを載せるべきです。

[Beer_Rabbit]

仰る通りですね。
以後、create文をのせて質問内容を明確にいたします。
何度もやりとりさせる結果となってしまい申し訳ございませんでした。

「varchar の値 をデータ型 int に変換できませんでした。」
このエラーに関しては、学生NOはINT型、専攻がvarchar型でした。　
CASE内で、違う型のカラム同士を記述したことによるものでした。
サンプルデータは、文字で記載してしまい失礼いたしました。

補足しますと。
学生NOが重複する理由は、別に学籍NOでユニークキーをもっています。
よって、この学生NOにおいては、重複するデータが存在します。

余計な情報を割愛したつもりがかえって混乱させてしまいました。
最後までお付き合いくださりありがとうございました。

[sousuke]

重複あるんですか…それなら学籍NOとかで並べたほうが早くないですか？
order by 入学年度,クラス,case when クラス='E' then 専攻 end,学籍NO

テーブルの定義に問題がありそうな気がしますが最悪それでも
order by 入学年度,クラス,case when クラス='E' then 専攻 end,学生NO,専攻
と書けばいいのでやはりelseは不要だとは思います。
またEクラスで学生NOが重複した場合は解決できてないので
テーブルの定義をしっかりして一意に定まるまでorderbyを書くべきです。
order by 入学年度,クラス,case when クラス='E' then 専攻 end,学生NO,専攻,学籍NO

質問とは関係ありませんがカウントを仕切りなおすpartition byもあります。
select *
,ROW_NUMBER() over(partition by 入学年度 order by クラス,case when クラス='E' then 専攻 end,学生NO,専攻,学籍NO) as 採番
from 学生テーブル
order by 入学年度,採番

[Beer_Rabbit]

order by 入学年度,クラス,case when クラス='E' then 専攻 end,学生NO,専攻

Eクラスのとき、ORDER BY は、
入学年度→クラス→専攻→学生NO→専攻

専攻が２回でてくるが、並び順が変わるわけではないので問題ない
という認識であっておりますか。

partition byについてもありがとうございます！

[sousuke]

その認識で大丈夫です。

[Beer_Rabbit]

ありがとうございました。

[sousuke]

一つ思い出したのでついでで。
CAST(学生NO AS VARCHAR)
あまり気にせずキャストしたんだと思いますが数値だと
1→10→20→100
ですけど文字列だと
1→10→100→20
になるので表示だけならともかくorderbyで気軽に使えるものじゃないです。
elseとかでまとめないで項目で区切りってしまいましょう。
order by 入学年度,クラス,case when クラス='E' then 専攻 end,case when クラス<>'E' then 学生NO end,case when クラス<>'E' then 専攻 end

あとnvarcharという文字列型もあるのでそれも覚えておくといいかもです。

[Beer_Rabbit]

確かにそうでした。
文字列による並び順への配慮が足りていませんでした。
各項目でCASE文を完結させるよう組みなおしました。
ありがとうございます。