社員のシフト管理表を作成しています。
visual web developer2008とSQL Server2005にて社内で利用するWEBアプリを作成しています。
CSVにてエクスポートしたいという要件が出てきており、その抽出条件が私には難しくお知恵をお借りしたいと思い投稿いたしました。
予定テーブル(tbl_yotei)には日付と必要人数と場所が入っており、社員予定テーブル(tbl_shain_yotei)には予定IDと社員名が入っています。
■予定テーブル(tbl_yotei)
yotei_id jishi_day ninzu shiten
1 2011/7/1 2 支店A
2 2011/7/2 1 支店A
3 2011/7/3 3 支店A
4 2011/7/2 1 支店B
5 2011/7/3 2 支店B
6 2011/7/4 3 支店A
------
■社員予定テーブル(tbl_shain_yotei)
shain_yotei_id yotei_id shain_nam
1 1 田中
2 1 山根
3 2 杉下
4 3 田中
5 3 一条
6 4 魚島
これを予定テーブルの人数分の行を表示し、その行にアサインされている社員が表示される表を作成したくご質問いたしました。
まだアサインされていない予定については社員はnullで人数分の行を作成したいと思います。
入力時の制限として予定テーブルに入力されている「人数」よりも多い数の社員は割り当てていません。予定テーブルのある行の人数が2なら社員予定テーブルの行数は0~2行までです。
ストアドプロシージャで予定テーブルに入っている人数分の行を出力する事は出来たのですが、そこから社員予定テーブルを参照して下記のような表になるようにする方法がわかりません。
例えば
tbl_yoeti.yotei_idが3のレコードは3行表示し、shain_yotei_id.yotei_idが3のレコードは2行あるので2行はshain_namに社員の氏名が入り、1行はshain_namがnullのレコードを表示させたいです。
yotei_id jishi_day ninzu shiten shain_yotei_id shain_nam
1 2011/7/1 2 支店A 1 田中
1 2011/7/1 2 支店A 2 山根
2 2011/7/2 1 支店A 3 杉下
3 2011/7/3 3 支店A 4 田中
3 2011/7/3 3 支店A 5 一条
3 2011/7/3 3 支店A Null Null
4 2011/7/4 1 支店B 6 魚島
5 2011/7/5 2 支店B Null Null
5 2011/7/5 2 支店B Null Null
6 2011/7/6 3 支店A Null Null
6 2011/7/6 3 支店A Null Null
6 2011/7/6 3 支店A Null Null
----
出来ているストアドプロシージャ
CREATE PROCEDURE dbo.StoredProcedure2
DECLARE @yotei_id int
DECLARE @jishiday datetime
DECLARE @ninzu int
DECLARE @shiten nvarchar(50)
DECLARE c_yotei cursor FOR
SELECT yotei_id,ninzu,jishiday,shiten FROM tbl_yotei
CREATE TABLE #TMP
(
yotei_id int,ninzu int, jishiday datetime, shiten nvarchar(50)
)
OPEN c_yotei
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu, @jishiday, @shiten
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @kaisu int
SET @kaisu = 1
WHILE @kaisu <= @ninzu
BEGIN
INSERT INTO #TMP (
yotei_id,ninzu,jishiday,shiten
) VALUES (
@yotei_id,@ninzu,@jishiday,@shiten
)
SET @kaisu = @kaisu + 1
END
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu, @jishiday, @shiten
END
CLOSE c_yotei
DEALLOCATE c_yotei
/*
ここからどのようにしたらよいでしょうか。。
*/
SELECT * FROM #TMP
RETURN
----
上記のストアドからでも全く新しいアイデアでもかまいませんので皆様お知恵をお貸しください。
どうかよろしくお願いいたします。
No.2ベストアンサー
- 回答日時:
あ、ごめんなさい。
ANo1のものです。
ミスしてます。
ANo1では、
yotei_id jishi_day ninzu shiten shain_yotei_id shain_nam
5 2011/7/5 3 支店B Null Null
ができてしまいます。
>insert into #TMP
>select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,b.shain_yotei_id,b.shain_nam
>from tbl_yotei a,tbl_shain_yotei b where a.yotei_id = b.yotei_id
>
>を実行する。(shain_yotei_idとshain_namがNULLでないレコードが格納される)
まではいいのですが、
>select max(ninzu) from tbl_yotei
>の値を取得する。
ここは、
select yotei_id,ninzu from tbl_yotei
をカーソルで取得して、1件ずつ処理する必要があります。
>
>while等を使って1から上で取得したmax(ninzu)まで以下のSQLを繰り返す。
>insert into #TMP
>select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
>from tbl_yotei a
>where (select count(*) from #temp b where b.yotei_id = a.yotei_id) < 繰り返しの値
これは、上で取得したレコード毎に繰り返し処理します。
ということで、
(元質問に書かれているのと同様に)
DECLARE c_yotei cursor FOR
SELECT yotei_id,ninzu FROM tbl_yotei
OPEN c_yotei
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
WHILE @@FETCH_STATUS = 0
BEGIN
set @i = 1
while @i > @ninzu
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
from tbl_yotei a
where a.yotei_id = @wk_yotei_ID and
(select count(*) from #temp b where b.yotei_id = @wk_yotei_ID) < @i
set @i = @i + 1
end
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
end
ということで。
(すいませんでした。)
この回答への補足
Siegrune様
できました!!ありがとうございました!!
下記ストアドプロシージャで思った結果が出てきました。
---
ALTER PROCEDURE dbo.StoredProcedure1 AS
DECLARE @yotei_id INT
DECLARE @ninzu int
--一時テーブル
CREATE TABLE #TMP
(
yotei_id INT,
jishi_day DATETIME,
ninzu INT,
shiten NVARCHAR(50),
shain_yotei_id INT,
shain_nam NVARCHAR(50)
)
INSERT INTO #TMP
SELECT
a.yotei_id,
a.jishi_day,
a.ninzu,
a.shiten,
b.shain_yotei_id,
b.shain_nam
FROM
tbl_yotei a,
tbl_shain_yotei b
WHERE a.yotei_id = b.yotei_id
DECLARE c_yotei CURSOR FOR
SELECT yotei_id,ninzu FROM tbl_yotei
OPEN c_yotei
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @i INT
SET @i = 0
WHILE @i < @ninzu
BEGIN
INSERT INTO #TMP
SELECT
a.yotei_id,
a.jishi_day,
a.ninzu,
a.shiten,
NULL,
NULL
FROM tbl_yotei a
WHERE a.yotei_id = @yotei_id AND
(SELECT COUNT(*) FROM #TMP b WHERE b.yotei_id = @yotei_id) <= @i
SET @i = @i + 1
END
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
END
SELECT * FROM #TMP ORDER BY yotei_id
RETURN
---
【結果】
yotei_id jishi_day ninzu shiten shain_yotei_id shain_nam
1 2011/07/01 0:00:00 2 支店A 1 田中
1 2011/07/01 0:00:00 2 支店A 2 山根
2 2011/07/02 0:00:00 1 支店A 3 杉下
3 2011/07/03 0:00:00 3 支店A 4 田中
3 2011/07/03 0:00:00 3 支店A 5 一条
3 2011/07/03 0:00:00 3 支店A
4 2011/07/02 0:00:00 1 支店B 6 魚島
5 2011/07/03 0:00:00 2 支店B
5 2011/07/03 0:00:00 2 支店B
6 2011/07/04 0:00:00 3 支店A
6 2011/07/04 0:00:00 3 支店A
6 2011/07/04 0:00:00 3 支店A
---
助かりました。本当にありがとうございました。
Siegrune様
再度投稿ありがとうございます。
>すいませんでした。
とんでもないです。こちらこそ何度もお答えいただいてありがとうございます。
実は先ほど一度教えていただいたストアドを書いてみて今回ご指摘の結果になる事にきづき、改めて考えていた所でございました!
各レコードのninzuの数だけ調べるという事ですね。ありがとうございます。
しかし、今回の大きなヒントになったのはNULLを後にインサートするという所がポイントとなり前に進めました!
ありがとうございます。
No.1
- 回答日時:
## 単に外部結合するだけで実現できると思いきや、甘かった。
方法案
#TMPは、yotei_id,jishi_day,ninzu,shiten,shain_yotei_id,shain_nam
にする。
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,b.shain_yotei_id,b.shain_nam
from tbl_yotei a,tbl_shain_yotei b where a.yotei_id = b.yotei_id
を実行する。(shain_yotei_idとshain_namがNULLでないレコードが格納される)
select max(ninzu) from tbl_yotei
の値を取得する。
while等を使って1から上で取得したmax(ninzu)まで以下のSQLを繰り返す。
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
from tbl_yotei a
where (select count(*) from #temp b where b.yotei_id = a.yotei_id) < 繰り返しの値
例示されたものでは、繰り返しの値は、1から3まで処理されます。
yotei_id=3の部分だけでいうと、count(*)は2。
繰り返しの値が1のときは、
(select count(*) from #temp b where b.yotei_id = a.yotei_id) < 1
何もしない。
繰り返しの値が2のときは、
(select count(*) from #temp b where b.yotei_id = a.yotei_id) < 2
何もしない。
繰り返しの値が3のときは、
(select count(*) from #temp b where b.yotei_id = a.yotei_id) < 3 は成立。
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
from tbl_yotei a
が実行される。
といった具合です。
Siegrune様
投稿ありがとうございます。
そうなんです。聞いた感じだとまあできそうな気がしていたのですが、いざやってみるととても大変でびっくりしています。
データのある行を先に#TMPに入れてしまってその後nullだけという二段階のインサート!私はぜんぜん思いつきませんでした!
いただいたアイデアを元に作成してみます。
max(ninzu)の結果を変数に入れる方法などまだわかっていないので調べて組み立ててみます。
本当にありがとうございます!
他のアイデアがありましたらどうぞ投稿お願いいたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL PHPとMySQLを使った掲示板の作り方 1 2022/06/02 13:00
- MySQL my_itemsテーブルのIDにAUTO_INCREMENT を追加ができるかで 1 2023/01/03 09:09
- Access(アクセス) アクセス テーブルの空白を変数に置換するボタンが作りたい 4 2022/07/08 11:19
- MySQL mysqlの結合について教えてください 1 2022/05/19 15:13
- Access(アクセス) アクセス 削除するレコードを含んだテーブルを指定してくださいのエラー対処方法 1 2022/11/24 15:01
- MySQL MYSQL エラー 2 2022/10/18 11:37
- PHP PHPでCSVを出力するさいに、ループの中で前の行の値を変更したい 3 2022/10/27 17:44
- C言語・C++・C# c言語の問題の説明、各所ごとに 5 2023/07/26 11:03
- MySQL エラー 1068 (42000): 複数の主キーが定義されていますエラー 2 2022/11/17 04:36
- AJAX JavascriptからPHPへのAjax通信でnullが返ってくる 3 2022/08/03 22:00
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
マイクラPC版のコマンドで効率...
-
エクセルで最後の文字だけ置き...
-
阪急三番街 ATM(ゆうちょ)は...
-
同一のユーザー、同一商品のと...
-
Unionした最後にGROUP BYを追加...
-
DataTableで重複行を削除したい
-
書籍の内容はまともでしょうか?
-
副問合せの書き方について
-
SQL Left Join で重複を排除す...
-
WHERE id = ? について
-
min句のSQLを改造し二番目に小...
-
MySQLのint型で001と表示する方...
-
別テーブルからSELECTした値を...
-
所有格の意味
-
select文のwhere句に配列を入れ...
-
スクリーンセイバー
-
MySQLの抽出について
-
ポスグレでの幾何学データ配列...
-
AUTO_INCREMENTのあるテーブル...
-
【MySQL】本当に困っているので...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
副問合せの書き方について
-
SQLサーバから、項目の属性(型...
-
VIEWの元のテーブルのindexって...
-
select文のwhere句に配列を入れ...
-
selectした大量データをinsert...
-
センノシド異性体構造式
-
Unionした最後にGROUP BYを追加...
-
insertを高速化させたい
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
sqlで、600行あるテーブルを100...
-
エクセルの関数について教えて...
-
Access パラメータクエリをcsv...
-
URL と行番号の指定
-
複数テーブルのGROUP BY の使い...
-
PL/SQLの変数について
おすすめ情報