SQL質疑応答スレ 18問目

1NAME IS NULL2018/01/04(木) 22:12:22.14ID:???
このスレは
「こういうことをやりたいんだけどSQLでどう書くの?」
「こういうSQLを書いたんだけどうまく動きません><」
などの質問を受け付けるスレです。

SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。

質問するときはDBMS名を必ず付記してください。

【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明

前スレ:
SQL質疑応答スレ 17問目
https://mevius.5ch.net/test/read.cgi/db/1468157341/

293NAME IS NULL2018/04/30(月) 08:36:43.41ID:???
頼み方ってものがあるだろ
ほら言ってみろ

294NAME IS NULL2018/04/30(月) 10:55:57.85ID:FupcYnE7
コメントが一杯付いてて良かったじゃないか

295NAME IS NULL2018/04/30(月) 14:55:41.60ID:???
下みたいなAテーブル、Bマスタがあって
Aテーブル(トラン)
no 名前 上長名前 日付

Bマスタ
名前 社員番号 適用開始日 適用終了日

【抜き出したい情報】
・Aテーブルに1つもない(名前、上長名前)人員の情報(Bマスタの名前、社員番号)
・Aテーブルの名前、上長名前に1以上トランが発生している人員情報、発生件数(名前、上長名前で分ける)

を抽出したい場合はどうすればよいでしょうか?

296NAME IS NULL2018/04/30(月) 14:57:09.24ID:???
>>295
同じ名前の社員がいたとき、どうすれば良いの?

297NAME IS NULL2018/04/30(月) 15:03:04.31ID:???
結婚とか離婚して姓名変わったらどうすんだ

298NAME IS NULL2018/04/30(月) 17:56:27.86ID:???
もうここしか頼るところがございません。
SQL server7.0が、綺麗にwin10のPCだけつながらなくなりました。
「接続できませんでした。 SQL state'ST100'
SQL server エラー0
[microsoft][ODBC SQL server driver] ログインが時間切れになりました」
と出ます。どうすればいいのでしょうか

299NAME IS NULL2018/04/30(月) 18:22:54.57ID:???
>>298
20年前の製品とか捨てちまえ

300NAME IS NULL2018/04/30(月) 18:35:50.58ID:???
>>296
>>297
それぐらい自分で考えろ

301NAME IS NULL2018/04/30(月) 19:16:11.68ID:L8ngFuqq
>>298
ああこれはログインが時間切れになってるね
ログインが時間切れにならないように注意しなよ

302NAME IS NULL2018/05/01(火) 08:40:04.60ID:???
>>298

なんでこの板まで来て

Microsoft SQL Server 総合スレ 11 [無断転載禁止]©2ch.net
https://mevius.5ch.net/test/read.cgi/db/1464508208/

に行かないの?

303NAME IS NULL2018/05/04(金) 20:14:40.86ID:???
外部キーって必ず他のテーブルの主キーじゃないとダメなの?

304NAME IS NULL2018/05/04(金) 21:31:51.25ID:???
主キーでなくてもUNIQUE制約がついていれば大丈夫だと思う

305NAME IS NULL2018/05/04(金) 22:29:19.11ID:GLAEl09j
>>303
>>304
これ自分も知りたいからageてみるか

306NAME IS NULL2018/05/05(土) 00:10:26.39ID:T65X27xi
>>303
一意制約の列でもよい。要は重複値を持っている列では外部キー制約に使用できない。

3073032018/05/05(土) 01:36:42.07ID:dfGZW7dT
>>304
>>306
ありがとうございますm(_ _)m

308NAME IS NULL2018/05/10(木) 23:54:50.05ID:???
DB殆ど触ったことないので分からないのですが、
1. SQLでは[ユーザー名, パスワード, [データA], [データB]] (便宜的にリスト形式にしています)
のようなDB構造は作れるのでしょうか?

つまり、ユーザー名とパスワードは1ユーザに1つずつ、データA,B,C、、、は内部に複数のデータを持つような構造です
ちなみに扱いたいSQLはpostgreSQLです

2. node.jsとpostgreSQLを使ってウェブアプリを作ろうと思っているのですが、postgreSQLでは最初にDB構造の宣言などはしなくて良いのでしょうか?

Swift + RealmでDBを使った際には、ユーザーモデルっていうDB構造?を宣言してから使ったので、少々混乱しています

3. node.jsとpostgreSQL(npmで)をWindowsにインストールしたのですが、postgreSQLが動きませんでした
WindowsにもpostgreSQLをインストールしなければいけないのでしょうか?


よろしくお願いします

309NAME IS NULL2018/05/11(金) 03:50:27.46ID:JrURJydT
MySQLです。数値を計算するSQL文ってあるでしょうか?

例えば、rankingテーブルに「access」と「click」というカラムがあるとします。
accessは×1でclickは×5にして、その合計数値が多い値(降順)で表示

みたいなことがしたいです。

310NAME IS NULL2018/05/11(金) 05:53:20.74ID:6wqBIw87
>>308
まずはDBを触りましょう
SQLではと書いているということは他ならわかるのでしょうか
RDBでは一般的に
[ユーザーID,ユーザー名,パスワード]
[ユーザーID,データ番号,データ]
のようなテーブルを作成してユーザーIDで結合します
これを正規化といいます

>>309
素直に
order by (ranking.access + ranking.click * 5) desc
でいけるかと

311NAME IS NULL2018/05/11(金) 17:09:15.29ID:???
SQL初心者です。
連続して1が立っている時間
(行数、データ数)は求められないでしょうか。
1が出てきたタイミングで後どれだけ続くかをその行に書き込みたいです。

312NAME IS NULL2018/05/11(金) 21:47:33.52ID:???
>>310
いけました!orderに計算するって概念がありませんでした。
とても勉強になりました。ありがとうございます

313NAME IS NULL2018/05/12(土) 09:16:12.16ID:???
>>311
どういうテーブル、イメージしてるデータの並びを書かないとレスつかないんじゃないか

314NAME IS NULL2018/05/12(土) 20:19:54.53ID:???
>>313
ありがとうございます。

A B
0 0
0 0
1 3
1 0
1 0
0 0
0 0

AからBを作りたいです。
Bの3は1が3つ続いていることを意味します。

315NAME IS NULL2018/05/12(土) 21:14:06.97ID:???
>>314
順序も規定せずに 「続いてる」とか言われてもなあ
Excel + VBA とかでやれば?

316NAME IS NULL2018/05/12(土) 21:22:35.67ID:???
>>314
連番がなければsql では無理

317NAME IS NULL2018/05/12(土) 21:39:12.17ID:???
>>315
>>316
No A B
0 0 0
1 0 0
2 1 3
3 1 0
4 1 0
5 0 0
6 0 0
7 1 2
8 1 0
9 0 0

例えばこの様な連番が有れば出来るものでしょうか。

すみません。
SQLで出来るのか?
出来るならどのような方法が有るのか
知りたいです。

よろしくお願いします。

318NAME IS NULL2018/05/12(土) 22:16:46.98ID:???
>>317
これで
2 1 3
7 1 2
がとれたらいいわけ?
時間っていってたわりにはそういう結果をかいてないが

319NAME IS NULL2018/05/12(土) 22:19:25.26ID:???
あとDBは何かしりたい

320NAME IS NULL2018/05/12(土) 22:28:15.77ID:???
最新の(つまりinsertする前の)NoでAが1で、かつ追加するデータのAが0だったら
直前のAが0のデータのNoを見る
例ならNoが9の時に0になったから直前のAが0のデータはNoが6だと。
んで、7,8が連続したものだった
だからNo7のBに2をセット

こんな感じ?

321NAME IS NULL2018/05/12(土) 23:17:44.19ID:???
>>318
分かりにくく申し訳ありません。
元々時系列のデータだったので
時間という書き方をしていました。
(何個続いたか知りたいのです)

NOとAの項目がある状態でBの項目
を追加したいと思っています。

318様のような結果でも構いません。

>>319
MSのSQL Serverです。申し訳ありませんが詳細なバージョンなどは不明です。

>>320
ありがとうございます。
書かれているようなことが
実現できればと思います。

322NAME IS NULL2018/05/12(土) 23:18:49.98ID:???
>>317
SQL文でやるならレコードを次々と参照しないといけないから再帰SQL使わないとだめだろうな
あるいはプロシージャでカーソル使う
あるいはプログラムでやる感じになる

323NAME IS NULL2018/05/13(日) 00:03:02.98ID:???
SQL Serverでなく、MySQLです、済みません。テーブル名はtと仮定しました。

create procedure setB()
begin
 declare done int;
 declare _flag int;
 declare _cnt int;
 declare _a int;
 declare _no int;

 declare cur cursor for select No,A from t order by No desc;
 declare exit handler for not found set done = 0;
 set done = 1;
 set _flag = 0;
 set _cnt = 0;
 
 open cur;
 while done do
  fetch cur into _no, _a;
  if _a = 1 and _no > 0 then
   update t set b = 0 where No = _no + 1;
   set _cnt = _cnt + 1;
  else
   update t set b = _cnt where No = _no + 1;
   set _cnt = 0;
  end if;
 end while;

 if _a = 1 then
  update t set b = 0 where No = _no;
 else
  update t set b = _cnt where No = _no;
 end if;
 close cur;
end;

324NAME IS NULL2018/05/13(日) 05:37:55.80ID:???
>>322
ありがとうございます。
教えていただいたキーワードをもとに
調べてみます。

>>323
ありがとうございます。
今はPCをさわれる環境にないので月曜日に試してみたいと思います。

325NAME IS NULL2018/05/13(日) 07:21:58.29ID:???
>>322
再帰やストアドなんて要らんでしょ
>>318 の結果を得たいだけなら

select
 T1.NO, T1.A,
 (
  select min(T3.NO) as NO
  from T as T3
  where T1.NO < T3.NO and T1.A <> T3.A
 ) - T1.NO as [連続数]
from T as T1
inner join T as T2 on T1.NO - 1 = T2.NO and T1.a <> T2.a
where T1.A = 1

で十分でしょ (わかると思うけど表名は T ね)

326NAME IS NULL2018/05/13(日) 09:51:48.44ID:???
>>325
ありがとうございます。
試してみます。

327NAME IS NULL2018/05/13(日) 11:09:02.63ID:???
再帰SQLでやるならこんな感じ
最初に1がでたところで表示したいとのことなので
レコードの最後から加算積み上げてる
半角でレスしようとしたらインジョクションかなんかでエラーになった
ため全角に変換してます

WITH CTE AS (
SELECT NO,A,A AS B 
FROM TBL AS M

UNION ALL 
SELECT M.NO ,M.A ,S.B + 1 AS B −−Bを1で積上げていく
FROM TBL AS M
inner join CTE AS S
on  M.NO = S.NO −1 −−レコードの後ろから参照
and S.A = 1
and M.A = 1

), [WK1] AS (
SELECT NO,A,max(B) as B −−Bが積上開始位置により別々の数字になっているので最大値でサマリー
FROM CTE AS M
GROUP BY NO,A


SELECT M.NO,M.A,case when S.A = 0 then B else 0 end B −−前のレコードがゼロの時のみBを表示
FROM [WK1] AS M
LEFT JOIN TBL AS S
on M.NO −1 = S.NO 
ORDER BY M.NO

328NAME IS NULL2018/05/14(月) 10:07:01.26ID:???
>>325
>>327
ありがとうございます。どちらも思ったような結果が得られました。
>>323
ありがとうございます。
今回は動かせていませんが、MySQLをさわる機会に試してみます。

4月末よりSQLを使わなければいけなくなり、困っていました。
皆さんに教えていただいた方法を理解し自由にSQLが書けるよう頑張ります!

329NAME IS NULL2018/05/17(木) 11:36:55.43ID:???
教えて下さい。

下記の表Tに於いて
NO,START,STOPが与えられているとき
KEKKAという行を得たいです。
良い方法があれば教えてください。
(STARTからSTOPの間に1を立てたい)
DBはMicrosoft SQL Serverです。

NO START STOP KEKKA
1 0 0 0
2 0 0 0
3 1 0 1
4 0 0 1
5 0 0 1
6 0 1 1
7 0 0 0
8 0 0 0

よろしくお願いします。

330NAME IS NULL2018/05/17(木) 15:57:54.69ID:???
>KEKKAという行を得たい

????????

331NAME IS NULL2018/05/17(木) 16:33:50.08ID:???
>>330
わかりにくくてすみません。
KEKKAという項目は元のデータにはなく、この項目を生成したいです。

332NAME IS NULL2018/05/17(木) 19:41:40.74ID:???
STARTとSTOPは1件ずつしかないのか?

順序はNO順でいいのか?
NOは連番なのか?

333NAME IS NULL2018/05/17(木) 22:05:10.46ID:???
START、STOPは必ず1レコードあり、複数はない
って事なら、簡単なSQLで処理できそう

334NAME IS NULL2018/05/17(木) 22:12:42.54ID:???
>>332
STARTとSTOPは複数あります。
NO順です。
NOは連番です。
STARTが1である行とSTOPが1である行は必ず交互に来て、
STARTとSTOPが同時に来ることはありません。
STARTが1の行とSTOPが1の行は続くことがあります。
STOPが1の行の直後にSTARTが1の行が来る場合もあります。
STARTが1の行の数とSTOPが1の行の数は同じです。
NO順に先頭から見ていくと、STARTが1の行は、STOPが1の行より先に出現します。

よろしくお願いします。

335NAME IS NULL2018/05/17(木) 22:21:17.05ID:???
>>334 で何をやりたいかはだいたいわかったけど
(たぶん STARTしてからSTOPするまで KEKKAを1にすればよいはず)
行順を保持しつつ状態遷移させるのって普通のSQLでできるのだろうか?

336NAME IS NULL2018/05/17(木) 22:27:50.57ID:???
START、START、STOP、STOPって並びになったときはどうすんの?

337NAME IS NULL2018/05/17(木) 22:28:51.14ID:???
334見てなかった、すまん

338NAME IS NULL2018/05/17(木) 22:51:31.55ID:???
>>334
スタートがあったら1加算ストップがあったら1減算する累積計算すればいいじゃねえ
select no sum( start + end * -1) as kekka from t as m
left join t as s
on t.no<=m.no
group by no

339NAME IS NULL2018/05/18(金) 01:30:58.99ID:???
>>338
いろいろ細かいミスは置いといて、それSTOPの行が1にならんだろ

>>329
select NO,START,STOP,
(select isnull(sum(START),0) from t where t.NO <= m.NO)-
(select isnull(sum(STOP),0) from t where t.NO < m.NO) KEKKA
from t m
とかで出来んじゃね

340NAME IS NULL2018/05/18(金) 10:08:58.76ID:???
ありがとうございます。
皆さんに教えていただいた方法を試していますが、うまく行きそうです。
ありがとうございました。

341NAME IS NULL2018/05/18(金) 17:13:55.57ID:???
・DBMS名とバージョン
10.1.16-MariaDB

・テーブルデータ
テーブル:categories
cateid  catename   
----------------
1     c1
2     c2
3     c3
4     c4
5     c5
6     c6
7     c7
8     c8

テーブル:books
cateid  bookname price
--------------------
1     b11     20
1     b12     10
1     b13     30
1     b14     40
1     b15     5
2     b21     20
2     b22     10
2     b23     30
2     b24     40
2     b25     5


・欲しい結果
cateid   catename bookname price
--------------------------------
1     c1      b14     40
1     c1      b13     30
1     c1      b11     20
2     c2      b24     40
2     c2      b23     30
2     c2      b21     20

・説明
categoriesとbooksをcateidで結合。結合の順番はbooksのpriceの降順とし、更にその結合する数の上限を3としたいのですが、どういうSQLになりますか?

ちなみに↓のようなSQLを思いつきましたが上手く行かず・・・
SELECT *
FROM categories
    LEFT JOIN (SELECT * FROM books ORDER BY price DESC LIMIT 3) books
    ON (books.cateid = categories.cateid)
priceでのソートはできてるんですが、LIMITが期待通りには機能しませんでした
まあそれもそのはず・・・これではbooksテーブル全件の上位3件を拾ってこいというクエリですからね

342NAME IS NULL2018/05/18(金) 21:21:55.34ID:???
select
 t1.cateid, catename, t1.bookname, t1.price
from books t1
join category c on c.cateid=t1.cateid
where 3 >= (
 select count(*)
 from books t2
 where
  t1.cateid = t2.cateid and
  t1.price <= t2.price
)
order by cateid, price desc;

343NAME IS NULL2018/05/20(日) 02:31:03.55ID:???
>>342
できました!ありがとうございます!

新着レスの表示
レスを投稿する