SELECT時に可能な動作は？

質問

例えば
Aテーブル
NO　名前
1　aaa
3　bbb
4 ddd
6 eee

というデータの入ったテーブルからデータを取得する際に

1　aaa
2　0
3　bbb
4　ddd
5　0
6　eee

という風に間が抜けたデータ(この場合2と5)をフェイクとして行に追加した状態で取得するSQL文は存在しますでしょうか？

回答No.3

7marine · ベストアンサー

あと少しで解決ですね！

create table #TableA(
num int,
val nvarchar(50)
)
go
insert #TableA values(1,N'aaa')
insert #TableA values(1,N'aaa')
insert #TableA values(3,N'bbb')
insert #TableA values(4,N'ddd')
insert #TableA values(4,N'ddd')
insert #TableA values(6,N'eee')
--Distinct
select distinct nums.num,isnull(val,N'0') as val from(
select
bit0 * power(2,0) +
bit1 * power(2,1) +
bit2 * power(2,2) +
1 as num from
(select 0 as bit0 union all select 1) as Bits0
cross join (select 0 as bit1 union all select 1) as Bits1
cross join (select 0 as bit2 union all select 1) as Bits2
) as nums left join #TableA
on nums.num=#TableA.num
order by nums.num
--Group By
select nums.num,isnull(val,N'0') as val from(
select
bit0 * power(2,0) +
bit1 * power(2,1) +
bit2 * power(2,2) +
1 as num from
(select 0 as bit0 union all select 1) as Bits0
cross join (select 0 as bit1 union all select 1) as Bits1
cross join (select 0 as bit2 union all select 1) as Bits2
) as nums left join #TableA
on nums.num=#TableA.num
group by nums.num,val
order by nums.num
--どうでしょう？

お礼

再度回答ありがとうございます！
実行してみたのですが、TableAの値が引っ張れなかったです…
しかし、教えていただいたものを流用して
やりたいSQLが組めそうです。
ありがとうございます！！！

回答No.5

7marine

ANo2～4です。
もう解決されたのかな？
もしまだであれば、ここまで来たのなら何とかここで解決したいですね。

少し意図がわからなくなってきました。
もし可能なら、実際のテーブルAの実データに近い形で
さらにこうなって欲しい結果も同時に補足していただけると
イメージがつきやすいのですが。

回答No.4

7marine

ANo2～3です。
直感ですが！

もしかしてテーブルAの連番を
Select か Group By で指定していませんか？
連番を指定するときは
テーブルAではなく連番テーブルを指定してみてください。
そうしないとGroup Byのときにはじかれちゃいます

お礼

例で上げているNOの部分なのですが、実際は
日付データが入っていて
MONTH(日付)　AS　NO
という風にしてNOを取得しています。
デフォルトのNULL状態のときに
DBに　1900/01/01　と入ってまして、WHERE句にて
YEAR(TableA.日付)<>'1900'と足してみたら、
値がとれなくなってしまいました。
(正しくいうと値が入ってる行のみ表示)

回答No.2

7marine

実はこの問いは実はかなり頭を使っていけば解決できます。
むかしむかし雑誌で見たのでおぼろげな記憶をたどって

select num from(
select
bit0 * power(2,0) +
bit1 * power(2,1) +
bit2 * power(2,2) +
bit3 * power(2,3) +
bit4 * power(2,4) +
bit5 * power(2,5) +
bit6 * power(2,6) +
bit7 * power(2,7) +
1 as num from
(select 0 as bit0 union all select 1) as Bits0
cross join (select 0 as bit1 union all select 1) as Bits1
cross join (select 0 as bit2 union all select 1) as Bits2
cross join (select 0 as bit3 union all select 1) as Bits3
cross join (select 0 as bit4 union all select 1) as Bits4
cross join (select 0 as bit5 union all select 1) as Bits5
cross join (select 0 as bit6 union all select 1) as Bits6
cross join (select 0 as bit7 union all select 1) as Bits7
) as nums
order by num

このクエリを使用するとまったく実テーブルを使用せずに
2の8乗の連番を取得できます。

後はこの長いクエリとleft joinすれば望みの結果が得られます

それにしても考え付いた人ってどんな頭の構造してんだろ！
凄過ぎます

（注意）
もっと連番を増やす場合はpower関数は処理が重いので
あらかじめ計算して掛けた方がパフォーマンスがよくなります

お礼

ありがとうございます！！！
こちらを実行してみたら連番が取得できました！

だけど、ひとつ問題がでてきてしまいました
(質問時に前提条件に書き忘れていたのですが)

Aというテーブル自体は
1　aaa
1　aaa
3　bbb
4　ddd
4　ddd
6 eee

という風に重複データが存在しており、取得時にGROUPBYして取り出しています。
なので、LeftJoinしても
1　aaa
3　bbb
4　ddd
6　eee
とテーブルデータのまま取得してしまいました・・・。
大切な事を記述し忘れて申し訳ありません…。

回答No.1

asahina02

通常のSQL文での方法はとりあえず思い浮かびません。

で、こんなやり方ではダメですか？
別に連番が入ったテーブルを用意する
　テーブルB：列はNoのみで1～の連番が入っている

select テーブルB.no,名前 from テーブルA
right join テーブルB on テーブルA.No = テーブルB.No

お礼

やはり通常のSQL文では無理でしょうか…。
DBのテーブルが安易に勝手に増やせない為、できれば何か特殊なやり方でこのAテーブルのみで取得できれば…と思っています。