Today: Yesterday: Total: Online:
カテゴリー
その他
  • RSS1.0
  • RSS2.0
  • atom0.3
  • valid XHTML1.0
  • valid CSS2
  • Credit
RSSリーダーで購読する | この日記のはてなブックマーク数 | プロフィール
コメントが一番多い記事(233コメント):人の心理の裏をかくホームページ集客術:リピータになってもらうためには
ツイートが一番多い記事(94ツイート):ちょっと待て!twitterやfacebookに子供の写真を掲載する親達への警告
いいねが一番多い記事(574いいね):facebookスパムに要注意。なりすましの見分け方とアカウント乗っ取りの手口(2013年度版)

[ カテゴリー » SQL ]

カテゴリー » SQL January 25, 2022

SQL Serverでメールアドレスの@より前と後を分割して取得する ID:1643098184


/*@より前の値を取得する*/
substring(mailAddress, 0, charindex('@',mailAddress)) as [ローカルパート],
/*@より後の値を取得する(末尾は適当に長くしている…)*/
substring(mailAddress, charindex('@',mailAddress) + 1, 999) as [ドメインパート]

https://juraku-software.net/sql-server-mailaddress-split-localpart-and-domainpart/

— posted by midilin @ 05:09PM | Comment (0)

カテゴリー » SQL September 16, 2021

SQL ServerでBASE64でデコードする ID:1631785100


SELECT 
    CONVERT
    (
        VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
    ) AS RESULT
FROM
    (
        SELECT 'cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==' AS BASE64_COLUMN
    ) A

もし、taというテーブルでcol1にデコードしたい場合は以下でデコードできた
select
 id,
 col1,
    CONVERT
    (
        VARCHAR(MAX), 
        CAST('' AS XML).value('xs:base64Binary(sql:column("col1"))', 'VARBINARY(MAX)')
    ) AS RESULT
from 
 ta

https://dba.stackexchange.com/questions/191273/decode-base64-string-natively-in-sql-server

— posted by midilin @ 06:38PM | Comment (0)

カテゴリー » SQL August 30, 2021

UNIONで重複レコードがある場合、優先順位を指定して重複除去 ID:1630303957


UNION を使うと重複除去されるので
UNION ALLを使って、全件抽出したあと、
優先順位を指定する。

table:ta1
---------------
id,col1
---------------
1,a
2,b
---------------

table:ta2
---------------
id,col1
---------------
2,c
3,d
---------------


ta1を優先として、レコードのid2は、ta1のほうを抽出する

SQL

select
	id,
	priority,
	col1,
	seq
from(
	select
		id,
		priority,
		col1,
		ROW_NUMBER() OVER(PARTITION BY id ORDER BY priority) AS seq
	from(
		select
			id,
			col1,
			1 as priority
		from
			ta1
		union all
		select
			id,
			col1,
			2 as priority
		from
			ta2
	)ta3 
) ta4
where 
	ta4.seq = 1

— posted by midilin @ 03:12PM | Comment (0)

カテゴリー » SQL July 07, 2021

SQL Server 2017 ID:1625645807


https://www.sejuku.net/blog/74068
https://www.microsoft.com/ja-jp/sql-server/sql-server-downloads
ただし、2019は64bitのみ

2014
https://www.microsoft.com/ja-jp/download/details.aspx?id=42299

— posted by midilin @ 05:16PM | Comment (0)

カテゴリー » SQL June 02, 2021

SQLで取得したレコードをグループに分ける ID:1622630261


NTILE関数を使う

例えば、4グループに分けたい場合は

SELECT id,
NTILE(4) OVER(ORDER BY id DESC) AS a
FROM test

とすると、12レコードある場合は、1〜4が均等にふられる

https://johobase.com/sqlserver-rank-denserank-rownumber-ntile/


ランダムに4グループに分けたい場合は
以下で行けそう(詳細なテストはしていない)

SELECT TOP 100 PERCENT
id,
NTILE(4) OVER(ORDER BY id DESC) AS a
FROM id
ORDER BY NEWID()


topとorder by newidでランダムを作れる
100%で全件取得し、4グループに分けるイメージ

— posted by midilin @ 07:37PM | Comment (0)

カテゴリー » SQL May 28, 2021

Order by、where区にcase文 ID:1622177271


Order byにCase
https://style.potepan.com/articles/14920.html

WhereにCase
https://style.potepan.com/articles/23548.html


検索値に値があったら取得
https://qiita.com/nogitsune413/items/9c939f50714e430461c8


table2のレコード数が100以下ならtable1のレコード全件表示、
そうでなければ何も出力しない
select
	*
from
	table1
where
    CASE
        WHEN (select count(id) as count from table2) <= 100 THEN
            id
    END = id

— posted by midilin @ 01:47PM | Comment (0)

カテゴリー » SQL June 13, 2019

SQL serverでランダムな数字を振る ID:1560408496


https://qiita.com/yfujii01/items/a6f1d8a8cf69b0c4e291

1〜100なら以下
CONVERT(int,(1 + (RAND(CONVERT(INT, CONVERT(VARBINARY(4), NEWID()))) * (100 - 1 + 1))))

ただし、都度ランダムにふられるので、ばらつきが出る


・SQLでランダムの行数取得する

SELECT * FROM test
WHERE
(ABS(CAST((BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 10

SQL serverで動作確認ずみ
実行する度に取得レコード数が変化する

・指定の行だけ取得する(総件数の10%を取得)
SELECT TOP 10 PERCENT * FROM test
ORDER BY NEWID()

・10行取得するなら

SELECT TOP 10 * FROM test
ORDER BY NEWID()

https://sqlazure.jp/r/sql-server/393/
https://johobase.com/sqlserver-select-top-set-rowcount/

— posted by midilin @ 03:48PM | Comment (0)

カテゴリー » SQL May 29, 2019

縦横変換SQL2 ID:1559090308


----------------
id,col1,col2
----------------
1,a,NULL
1,NULL,b
----------------

----------------
id,col1,col2
----------------
1,a,b
----------------

にするには
----------------------------

select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null
----------------------------

また

----------------
id,col1,col2
----------------
1,a,NULL
1,NULL,b
2,c,null
3,null,d
----------------

----------------
id,col1,col2
----------------
1,a,b
2,c,null
3,null,d
----------------

とするには

---両方カラムがあるフィールドを抽出
select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null

union all
--col1だけあるフィールドを抽出
select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
a.id not in
(
select
a.id
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null
)
union all
--col2だけあるフィールドを抽出
select
a.id,
a.col1,
b.col2
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
b.col2 is not null
and
a.id not in
(
select
a.id
from
ta2 a
inner join
ta2 b
on a.id = b.id
where
a.col1 is not null
and
b.col2 is not null
)



あとは、以下のページも参考に


複数行のグループデータを1行にまとめるSQL
https://ginpro.winofsql.jp/article/253189068.html


縦横の変換が、数値であれば、SUMを使うともっとシンプルにできる
https://johobase.com/sqlserver-expand-data-horizontally/

MAXを使えば、文字もOK
https://qiita.com/k24d/items/79bc4828c918dfeeac34
SELECT uid,
max(CASE WHEN key = 'c1' THEN value END) AS c1,
max(CASE WHEN key = 'c2' THEN value END) AS c2,
max(CASE WHEN key = 'c3' THEN value END) AS c3
FROM vtable
GROUP BY uid

— posted by midilin @ 09:38AM | Comment (0)

カテゴリー » SQL April 22, 2019

結合キーを持たない、テーブル同士のすべての組み合わせのSQLの書き方 ID:1555909300


テーブルA、カラムID1
テーブルB、カラムID2

を持つ、すべてのレコードの組み合わせ(結合キー無)

select
tableA.ID1,tableB.ID2
from
tableA
cross join
tableB

— posted by midilin @ 02:01PM | Comment (0)

カテゴリー » SQL February 27, 2019

SQL serverのフリー版 SQL Server Expressのメモ書き ID:1551260550


SQL Server Expressとは?概要・有償版との違いについて徹底解説!
https://www.sejuku.net/blog/74368
開発用、学習用として使える

https://www.sejuku.net/blog/74068
導入方法

GUI(Manegement Studio)を使ったテーブルの作成方法

SQL Server Management StudioでExcelファイルのデータをインポートする方法
https://qiita.com/speedstar18fct/items/ce0772e825b0d5cc6e91
コピペでインポートができるとのこと

— posted by midilin @ 06:42PM | Comment (0)