SQL

本来空行を返すSQLで1行返す

SQL

例えば、ユーザIDからユーザの今の状態を引っ張ってきたいときに、 SELECT state FROM Users WHERE id = @uid; とかってやると、見つからなかった場合に空行が返ってくる。 これがいやな場合はこうする*1。 SELECT state FROM Users WHERE id = @uid UNION A…

HAVING句知らないんだ・・・

SQL

集計結果でフィルタしたい、って、そのためにHAVING句があるんですが・・・ 他にも、ORDER BYしたあとにGROUP BYしたい・・・って、自分で何がしたいかわかってないんじゃないかとしか思えない。

メールアドレスをドメイン名で並び替え

SQL

-- SQL Server 200 SELECT mail_address FROM Customers ORDER BY reverse(mail_address) 一応、ドメイン名ごとに並びはするよ。 じゃなくて、はじめからドメイン名で並び替えることがわかってんならカラム分ければいいのに。 -- SQL Server 2000 SELECT mai…

経路列挙モデル

RDBで親子関係を表すのに、たぶん隣接リストモデルがもっとも使われていると思う。 簡単に説明すると、子供に親へのポインタを持たせるようなものだ。 ただ、このモデルを採用すると、どうしてもきれいに問い合わせが書けなくなってしまう。 それを避けるた…

ある列の値が平均未満の行を抽出

SQL

ある列、例えばcolの値が、そのテーブルのcolの平均値未満の行を抽出したい場合、ちょっと考えただけでもいくつかの方法が思いつく。 まずは、実行できないものから。 SELECT * FROM SomeTable WHERE -- WHEREの評価段階ではavgは計算できない col < avg(col…

第一正規形を破っているテーブルの集計

SQL

例えばアンケート結果で、複数の結果を格納する可能性がある場合に、何を血迷ったかスペース区切りで文字列として格納するようなテーブル設計をやる人がいる。 cid*1 enq_id answers 1 1 1 3 1 2 2 1 1 2 3 2 2 10 ・・・ ・・・ ・・・ こんな感じ。 今日(…

ストアドプロシージャ

SQL

ストアドプロシージャはまぁ、いつか必要になる。 でも、ストアドプロシージャの紹介方法は問題ありすぎる気がするんだよなぁ。 例えばこの記事とか。 以下主に愚痴。 ストアドプロシージャ教えるならIF文は必須だよね。でも、SQLで出来ることをわざわざスト…

SQLインジェクション対策

講習か何かに行ってきた成果を取り込んだ対策一覧 文字数を一律50文字に制限する SQL生成時にユーザが入力した文字列はサニタイジングする。ただし、メールアドレスとして使用されるような文字列はその限りではない SQL生成時に&、、"はそれぞれ、&、<、>、"…

CASE・・・演算子?

SQL

今日職場で超図解 SQLハンドブックという本*1を読んだ。 その中で、CASE式が演算子として紹介されてた。 まぁ、CASE「文」として紹介するよりも遙かにましなんだけど・・・その後にJavaのswitchと同じような物、みたいなことが書かれていてしょんぼり。 CASE…

今日のバグ

SQL

原因不明のバグがあるから見て欲しい、との事だったからコードを眺めてみると・・・ sql += "("; sql += " ( "; sql += " COL1 IS NULL "; sql += " AND "; sql += " COL2 IS NULL "; sql += " AND "; sql += " COL3 IS NULL "; sql += " ) "; sql += " OR "…

それ集約関数とCASE式(もしくはただの計算式)で

SQL

SQLServer2005からの新機能「Pivot句」 - 山本大@クロノスの日記Pivot句なんてものがあったのか・・・ ただ、例にしている「テーブル行の横展開」だけど、「ゴリゴリ組む」必要なんてないし、Pivot句を使うよりコードとしては短くなるよ。 こんな感じ*1。 S…

ゆの in SQL

SQL

たぶんどんなSQL実装でも動くと思われるゆの in Language SELECT 'ひだまりスケッチ×' AS yu , X AS no , _ AS i , 来週も見てくださいね AS n , '来週も見てくださいね!' AS language FROM (SELECT 3 AS X, 6 AS _, 5 AS 来週も見てくださいね) T WHERE X /…

ゆの in MySQL

SQL

JavaとかC++は最近触ってないし、C#等も使いこなせてるわけではないから、とりあえずMySQLでやってみた。 SELECT 'ひだまりスケッチX365 来週も見てくださいね!' FROM (SELECT '1話' AS X, '見逃した...orz' AS _) T WHERE X / _ / X < '来週も見てください…

ログをわかりやすく表示

SQL

DBに格納するログがあまりにも役に立たなかったんで、テーブル自体も新調することに。 ちなみにSQL Serverです。 Logs id int log_level char(5) message nvarchar(255) time_and_date datetime Exceptions id int l_id int name nvarchar(64) StackTraces i…

一個のSQLでやるか分割するか

SQL

コンサートの予約を行うとして、 コンサートには席数に限りがある 予約のキャンセルも可能 ただし、予約のキャンセルを行って空席が出来ても、満席状態にしておく 満席状態はis_sold_outが1かどうかで判断する という要望があったとき、 UPDATE Concerts SET…

入れ替える

SQL

UPDATE SomeTable SET is_hoge = (CASE is_hoge WHEN 1 THEN 0 ELSE 1 END) WHERE id = 42 idが42のis_hoge*1の値を入れ替える。ただそれだけ。 *1:BIT型

重複を探す

SQL

同一カテゴリ内で名前の重複がないはずのテーブルに重複があるっぽいから探して、って言われて、その場で SELECT * FROM SomeTable P WHERE EXISTS ( SELECT * FROM SomeTable C WHERE P.category = C.category AND P.id <> C.id AND P.name = C.name) ORDER…

そんなにSQLが嫌いか

あけたら毒ガスが吹き出る箱を開けてしまった気分。 SQL_SELECT = "SELECT * FROM "; SQL_COUNT = "SELECT COUNT(*) FROM "; SQL_WHERE = " WHERE " SQL_ORDER = " ORDER BY "; SQL_EQUAL = " = "; SQL_AND = " AND "; SQL_OR = " OR "; ... SQL_UPDATE = "U…

DB設計

SQL

なんというか、ひどいな。 意味の無いプレフィックス*1、おかしな名前*2、重複した項目*3、不要な項目*4、不適切なPK*5、etc... とりあえず、画面の項目をそのままテーブルにするのはやめにしませんか? *1:全てのテーブルにT_、全てのカラムにC_・・・ *2:s…

前年度との比較(SQL Server 2005版)

SQL

こっちとほとんど一緒だけど、日付関数とかに違いがある。 MySQL版だと、 -- 同じ月は対象外 (month(now()) <> month(E.doc_date)) AND ( -- 前年度の対象範囲は・・・ E.doc_date BETWEEN -- 前年度の4月1日から concat(year(now()) - CASE WHEN month(now(…

SQL Server 2000にはWITHはないっぽい

SQL

SQL Server 2005のManagement StudioでWITH使ってエラーになるから、なんでだとか思って調べたら、つないでいるDBがSQL Server 2000のものだった・・・

前年度との比較

SQL

売り上げを保持しているテーブルがこんな感じに 取引先 取引日時 金額 1 2008-5-10 100 1 2008-5-9 200 2 2008-5-1 300 1 2008-4-20 500 2 2008-4-2 100 2 2008-4-1 200 略 略 略 1 2007-5-20 10 1 2007-5-10 50 1 2007-4-25 100 2 2007-4-20 100 1 2007-4-1…

SQL

col1 col2 col3 col4 1001 2323 500 0 456 1001 0 200 1111 1001 0 300 こんな感じにデータが入ってて、 col1-2 sum(col3) sum(col4) 1001 500 500 な感じにcol1かcol2が1001になってるやつのcol3とcol4を集計したい場合*1、 SELECT CASE col3 WHEN 0 THEN c…

EXCEPTがないRDBMSで差集合

SQL

SELECT T1.* FROM SomeTable T1 LEFT JOIN AnotherTable T2 ON T1.id = T2.id WHERE T2.some_col IS NULL; こんな感じに外部結合して、T2側の項目がNULLになるものを抽出すればOK.

かぶった

SQL

上のテーブルと考え方が同じ方法がここですでに出てたよ...orz でも同じテーブル構成から全然違うSELECTが出てくる、ってのが面白いよね。 で、記事のコメントに、 あー。 accountの符号から income と outgo ってカラムがあるかのように”見せかける”のかー …

そもそもテーブル自体変更して・・・

SQL

DBわからんちんなりにから、収入と支出のどちらかにしか入らないようなテーブルにしてしまえば?ということで、こんな感じで。 CREATE TABLE Cashes ( id INTEGER UNSIGNED AUTO_INCREMENT , date TIMESTAMP , summary TEXT NOT NULL , kind ENUM('INCOME', …

countは非効率?

SQL

なんかPMがcountはテーブル全体を走査するから、行数0を判断するならtop 1使えとか言い出した。 そもそもtopはSQL Serverのみの機能というのは置いとくとしても、100万レコードのテーブルでcountでもtop 1でも瞬時に終わるんですが・・・ テーブル全体を〜、…

みんなカーソル大好き

SQL

なんでみんなそんなにカーソルが好きなんだろう? CASE WHEN (SELECT TOP 1 Hoge FROM Piyo ORDER BY HogeDate DESC) IS NULL THEN ... ELSE ... END ・・・それなんてmin?いや、むしろEXISTにしてTHENとELSEを反転させたい。 というか、TOPつければORDER B…

マジックナンバー

SQL

01という文字列を格納した@01、0を格納した@Zero・・・ 直しますよ、直しますとも・・・ 聞いてる分にはいいんだけど、実際自分の身に降りかかるとシャレにならん。

まじですか

SQL

7月に協力会社から来ていた人のSQLがやばいことが判明。 x A 条件が違うだけのSELECT句をUNION ALLでつなぎまくる そこらじゅうに散らばるリテラル 微妙な条件式 CASE式の中のすさまじい重複 などなど。 1番目は明らかなバグ。こいつ、よく今までこれでやっ…