SQLを使い、特定の条件に合致するレコードのみのidを列挙したい

Question

前提・実現したいこと

javascript上でalasqlを使い、特定の条件に合致するレコードのみのidを列挙したいです。

データベースをそのまま載せられないので例で挙げますが、このようなデータベースがあります。

id	name	check	money
1	Aさん	OK	2000
2	Bさん	OK	500
3	Cさん	NG	1000
4	Aさん	OK	1400
5	Aさん	NG	600

これをnameでグループ化して、人ごとにOKとNGの合計金額を出し、OKになっているレコードのidを列挙したいです。


【求める結果】

name	OK	NG	ids
Aさん	3400	600	1,4
Bさん	500	0	2
Cさん	0	1000

該当のソースコード

alasql( "SELECT cust.name, SUM(CASE WHEN cust.check = ? THEN cust.money ELSE 0 END) as [OK], \
            SUM(CASE WHEN cust.check = ? THEN cust.money ELSE 0 END) as [NG], \
            SUM(CASE WHEN cust.check = ? or cust.check = ? THEN cust.money ELSE 0 END) as [合計], \
            GROUP_CONCAT(cust.id) as [ids] \
         FROM ? AS cust \
         GROUP BY cust.name ORDER BY cust.name", ["OK", "NG", "OK", "NG", database]
);

　
【このソースコードでの結果】

name	OK	NG	ids
Aさん	3400	600	1,4,5
Bさん	500	0	2
Cさん	0	1000	3

　
GROUP_CONCATを使って人ごとのidを列挙することができましたが、OKのレコードのみの条件を付けられません。
やり方や参考になるサイト等を教えて頂けないでしょうか。

Answer 1

SQL Server 2017以降 STRING_AGG を参考に。

Comments

[mkk]

ご回答ありがとうございます。
教えていただいたサイトで調べてみたいと思います。

Answer 2

GROUP_CONCATは非ヌル値のみを対象にします。
ですので条件に合致した時のみ値を有効にするようにすれば、良いかと思います。

alasql( "SELECT cust.name, SUM(CASE WHEN cust.check = ? THEN cust.money ELSE 0 END) as [OK], \
            SUM(CASE WHEN cust.check = ? THEN cust.money ELSE 0 END) as [NG], \
            SUM(CASE WHEN cust.check = ? or cust.check = ? THEN cust.money ELSE 0 END) as [合計], \
            GROUP_CONCAT(CASE WHEN cust.check = ? then cust.id end) as [ids] \
         FROM ? AS cust \
         GROUP BY cust.name ORDER BY cust.name", ["OK", "NG", "OK", "NG", "OK", database]
);

Comments

[mkk]

ご回答ありがとうございます。
toyotaku様の方にもほぼ同様の件を記載致しましたが、今度はelseを使わずにSQLを書いても
idsの中身が
["6", "5", "undefined", "1"]
のようになってしまいます。
"undefined" の部分がNGのレコードのidがあったところです。
javascript上でalasqlを使ってGROUP_CONCATを使っている関係で
MySqlのGROUP_CONCATと仕様が違っているなどがあるのでしょうか。

[sazi]

どうなんでしょう？
素のSQLで確認されてみましたか？

[sazi]

ありゃ？GROUP_CONCATとなっていたので、MySQLかと思ってましたが、SQLServerですか。
sqlserverなら該当するのは、string_agg()なので、alasqlが変換してるのかな？

[mkk]

テストサイトで試してみましたが、MySQLでは、sazi様の仰る通りNULLが除外されて結果が表示されました。
string_agg() も初めて聞いたので調べてみます。

[sazi]

string_agg()はSQLserverです。（タグがSQLServerになっているので）
MySQLとSQLServerのどっちなんでしょう？

[sazi]

Alasqlってライブラリーとかではなく、javaScriptデータベースですね。
だとすると、独自のリファレンスがあるはずなので確認して下さい。（見つけきれなかった）

[sazi]

結果だけ見ると、AlasqlのGROUP_CONCAT()はNull除外しないみたいなので、サブクエリーが使えるなら、それで対応するという感じですかね。

[mkk]

正直なところ、alasqlの全容がまだ見えておらずどちらとも言いがたいところです。
恐らくはalasqlという独自のものかと思いますが、ベースはSQLserverやMySQLから考えてありそうです。
下記サイトにSQL Serverと書いてあったのでタグに追加をしておりました。
https://qiita.com/rex0220/items/d1030b293c1021d28750
ただ、string_aggを使おうとしたところ関数が見つからずエラーとなってしまいました。

[mkk]

ありがとうございます。
リファレンスを探してみます。

[sazi]

> alasqlの全容がまだ見えておらずどちらとも言いがたいところです。
いえ、別物です。
> 下記サイトにSQL Serverと書いてあったのでタグに追加をしておりました。
それは、SQLを受け付けるSERVERという意味で、製品であるSQLServerとは違います。

[mkk]

なるほど、勉強になりました。
ありがとうございました。

Answer 3

GROUP_CONCAT(cust.id) のcust.idを
他の項目と同じようにcase文でOKの時だけidを返せばうまくいきそうに見えます。
else はnullすればよいでしょう。
【このソースコードでの結果】は、cさんはidsが3になっているのでは？

Comments

[mkk]

ご回答ありがとうございます。
【このソースコードでの結果】は修正させていただきました。

教えていただいた通りelseでNULLを指定してやってみたのですが、idsの中身が
["6", "5", "undefined", "1"]
のようになってしまいます。
"undefined" の部分がNGのレコードのidがあったところです。
javascript上でalasqlを使ってGROUP_CONCATを使っている関係で
MySqlのGROUP_CONCATと仕様が違っているなどがあるのでしょうか。

[toyotaku]

あら、すみません。
arasqlについては知見がありません。お力になれず申し訳ないです。

[mkk]

こちらこそ申し訳ないです。
他のSQLと同じ動きをすると思って通常のSQLのようにお伺いしてしまいました。