{"id":2366,"date":"2022-09-30T11:11:19","date_gmt":"2022-09-30T02:11:19","guid":{"rendered":"https:\/\/www.dbsheetclient.jp\/blog\/?p=2366"},"modified":"2022-09-30T11:11:19","modified_gmt":"2022-09-30T02:11:19","slug":"iif%e3%80%81choose%ef%bc%88sql-server%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=2366","title":{"rendered":"IIF\u3001CHOOSE\uff08SQL Server\uff09"},"content":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002
\"\u7cbe\u9032\u6e56\u304b\u3089\u306e\u5bcc\u58eb\u5c71\"<\/a><\/p>\n

\u304b\u306a\u308a\u6642\u9593\u304c\u7d4c\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u304c\u3001\u4e0a\u306e\u5199\u771f\u306f\u3001\u4eca\u5e74\u306e\u6625\u306b\u3001\u666e\u6bb5\u304b\u3089\u4ea4\u6d41\u306e\u3042\u308b\u5730\u57df\u306e\u58ee\u5e74\u305f\u3061\u3068\u4e00\u7dd2\u306b\u5bcc\u58eb\u5c71\u306e\u8fd1\u304f\u306b\u3044\u3063\u305f\u3068\u304d\u306e\u5199\u771f\u3067\u3059\u3002\u5f53\u65e5\u306f\u671d\u304b\u3089\u66c7\u308a\u3067\u3001\u306a\u304b\u306a\u304b\u304d\u308c\u3044\u306a\u5bcc\u58eb\u5c71\u304c\u64ae\u308c\u306a\u304b\u3063\u305f\u306e\u3067\u3059\u304c\u3001\u96f2\u306e\u5408\u9593\u306b\u9802\u4e0a\u304c\u898b\u308c\u3066\u3044\u308b\u5199\u771f\u3067\u3059\u3002\u5bcc\u58eb5\u6e56\u306e\u4e00\u3064\u3001\u7cbe\u9032\u6e56\uff08\u3057\u3087\u3046\u3058\u3053\uff09\u3001\u7cbe\u9032\u6599\u7406\u306e\u7cbe\u9032\u306b\u6e56\u3068\u66f8\u3044\u3066\u3001\u3057\u3087\u3046\u3058\u3053\u3068\u8a00\u3046\u307f\u305f\u3044\u3067\u3059\u304c\u3001\u305d\u306e\u6e56\u306e\u508d\u304b\u3089\u64ae\u3063\u305f\u5199\u771f\u3067\u3059\u3002\u5bcc\u58eb5\u6e56\u3068\u3044\u3046\u306e\u306f\u3001\u5c71\u68a8\u770c\u5074\u306e\u5bcc\u58eb\u5c71\u9e93\u306b\u4f4d\u7f6e\u3059\u308b5\u3064\u306e\u6e56\u306e\u7dcf\u79f0\u3067\u3001\u672c\u6816\u6e56\u3001\u7cbe\u9032\u6e56\u3001\u897f\u6e56\u3001\u6cb3\u53e3\u6e56\u3001\u5c71\u4e2d\u6e56\u3068\u3042\u3063\u3066\u30012013\u5e74\u306b\u306f\u4e16\u754c\u6587\u5316\u907a\u7523\u306b\u767b\u9332\u3055\u308c\u3066\u3044\u307e\u3059\u3002
\u5bcc\u58eb\u5c71\u304c\u7dba\u9e97\u306b\u898b\u3048\u308b\u30d1\u30ce\u30e9\u30de\u53f0\u3068\u3044\u3046\u5834\u6240\u306b1\u6642\u9593\u534a\u304b\u3051\u3066\u767b\u3063\u3066\u3001\u30ac\u30b9\u30b3\u30f3\u30ed\u3068\u6c34\u3001\u3084\u304b\u3093\u3092\u7528\u610f\u3057\u3066\u3044\u305f\u306e\u3067\u3001\u305d\u3053\u3067\u6e6f\u3092\u6cb8\u304b\u3057\u3066\u3001\u7686\u3067\u30ab\u30c3\u30d7\u30cc\u30fc\u30c9\u30eb\u3092\u98df\u3079\u307e\u3057\u305f\u3002\u3068\u3066\u3082\u304a\u3044\u3057\u304b\u3063\u305f\u3067\u3059\u3002\u305d\u306e\u5f8c\u3001\u5c71\u3092\u4e0b\u308a\u3066\u3001\u300c\u5bcc\u58eb\u773a\u671b\u306e\u6e6f\u3086\u3089\u308a\u300d\u3068\u3044\u3046\u8fd1\u304f\u306e\u6e29\u6cc9\u306b\u884c\u3063\u305f\u3068\u304d\u306b\u306f\u6674\u308c\u3066\u3001\u9732\u5929\u98a8\u5442\u304b\u3089\u5bcc\u58eb\u5c71\u306e\u9802\u4e0a\u304c\u771f\u3093\u524d\u306b\u304d\u308c\u3044\u306b\u898b\u3048\u307e\u3057\u305f\u3002\u6e29\u6cc9\u306b\u3064\u304b\u308a\u306a\u304c\u3089\u3001\u3044\u3064\u307e\u3067\u3082\u5bcc\u58eb\u5c71\u3092\u898b\u3066\u3044\u305f\u3044\u3068\u3044\u3046\u611f\u3058\u3067\u3057\u305f\u3002\u3084\u306f\u308a\u3068\u304d\u306b\u306f\u81ea\u7136\u306e\u4e2d\u306b\u3069\u3063\u3077\u308a\u3068\u6d78\u304b\u308b\u6642\u9593\u304c\u5fc5\u8981\u3067\u3059\u306d\u3002

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
<\/strong>IIF\u3001CHOOSE\uff08SQL Server\uff09
<\/strong>
\u4ee5\u524d\u3001CASE\u6587\u3092\u53d6\u308a\u4e0a\u3052\u305f\u3053\u3068\u304c\u3042\u308a\u307e\u3057\u305f\u304c\u3001CASE\u5f0f\u306e\u7c21\u7565\u7248\u3068\u8a00\u3048\u308bIIF\u95a2\u6570\u3068\u3001\u5f8c\u306b\u7d9a\u304f\u30ea\u30b9\u30c8\u306b\u5bfe\u3059\u308b\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u8a2d\u5b9a\u3067\u304d\u308bCHOOSE\u95a2\u6570\u306b\u3064\u3044\u3066\u7d39\u4ecb\u3057\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u4f8b)
\u751f\u5f92\u306e\u8a66\u9a13\u7d50\u679c\u306b\u3064\u3044\u3066\u3001\u70b9\u6570\u306b\u3088\u3063\u3066\u6210\u7e3e\u3092\u632f\u308a\u5206\u3051\u308b\u4f8b\u3092\u8003\u3048\u3066\u307f\u307e\u3059\u3002\u307e\u305a\u3001\u751f\u5f92\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u3068\u3001\u8a66\u9a13\u7d50\u679c\u7528\u306e\u30c6\u30fc\u30d6\u30eb\u3092DB\u306b\u4f5c\u6210\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n

CREATE TABLE dbo.Student(
\u5b66\u7c4d\u756a\u53f7\u00a0 NVARCHAR(8),
\u6c0f\u540d\u00a0\u00a0\u00a0\u00a0\u00a0 NVARCHAR(20),
\u6027\u5225\u00a0\u00a0\u00a0\u00a0\u00a0 NVARCHAR(1),
\u751f\u5e74\u6708\u65e5\u00a0 DATE,
CONSTRAINT PK_Student PRIMARY KEY(\u5b66\u7c4d\u756a\u53f7));<\/p>\n

INSERT INTO dbo.Student VALUES(‘20220001’, ‘\u751f\u5f92A’, ‘\u7537’, ‘2006-05-12’);
INSERT INTO dbo.Student VALUES(‘20220002’, ‘\u751f\u5f92B’, ‘\u5973’, ‘2007-02-21’);
INSERT INTO dbo.Student VALUES(‘20220003’, ‘\u751f\u5f92C’, ‘\u5973’, ‘2006-11-07’);
INSERT INTO dbo.Student VALUES(‘20220004’, ‘\u751f\u5f92D’, ‘\u7537’, ‘2006-07-18’);
INSERT INTO dbo.Student VALUES(‘20220005’, ‘\u751f\u5f92E’, ‘\u7537’, ‘2006-12-25’);<\/p>\n

CREATE TABLE dbo.Test_result(
\u5b66\u7c4d\u756a\u53f7\u00a0 NVARCHAR(10),
\u8a66\u9a13\u65e5\u00a0\u00a0\u00a0 DATE,
\u70b9\u6570\u00a0\u00a0\u00a0\u00a0\u00a0 INT,
CONSTRAINT PK_Test_result PRIMARY KEY(\u5b66\u7c4d\u756a\u53f7,\u8a66\u9a13\u65e5));<\/p>\n

INSERT INTO dbo.Test_result VALUES(‘20220001’, ‘2022-05-18’, 92);
INSERT INTO dbo.Test_result VALUES(‘20220002’, ‘2022-05-18’, 48);
INSERT INTO dbo.Test_result VALUES(‘20220003’, ‘2022-05-18’, 69);
INSERT INTO dbo.Test_result VALUES(‘20220004’, ‘2022-05-18’, 28);
INSERT INTO dbo.Test_result VALUES(‘20220005’, ‘2022-05-18’, 82);<\/p>\n

\u3053\u3053\u3067\u3001\u8a66\u9a13\u306e\u70b9\u6570\u306b\u3088\u3063\u3066\u3001\u6210\u7e3e\u3092S,A,B,C,D \u306b\u5206\u3051\u308b\u62bd\u51fa\u3092\u884c\u3063\u3066\u307f\u307e\u3059\u3002CASE\u6587\u3092\u4f7f\u3063\u3066\u62bd\u51fa\u3059\u308b\u3068\u3001\u4e0b\u8a18\u306e\u3088\u3046\u306bSQL\u6587\u3092\u66f8\u3051\u307e\u3059\u3002<\/p>\n

SELECT
\u5b66\u7c4d\u756a\u53f7,
\u8a66\u9a13\u65e5,
\u70b9\u6570,
CASE WHEN \u70b9\u6570 > 90 THEN ‘S’
\u00a0 \u00a0 \u00a0WHEN \u70b9\u6570 > 80 THEN ‘A’
\u00a0 \u00a0 \u00a0WHEN \u70b9\u6570 > 60 THEN ‘B’
\u00a0 \u00a0 \u00a0WHEN \u70b9\u6570 > 30 THEN ‘C’
\u00a0 \u00a0 \u00a0ELSE ‘D’ END AS \u6210\u7e3e
FROM dbo.Test_result
ORDER BY \u5b66\u7c4d\u756a\u53f7;<\/p>\n

\"blog107_1\"<\/a><\/p>\n

\u3053\u308c\u3092\u3001IIF\u95a2\u6570\u3092\u4f7f\u3046\u3068\u3001\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u3082\u66f8\u3051\u307e\u3059\u3002<\/p>\n

SELECT
\u5b66\u7c4d\u756a\u53f7,
\u8a66\u9a13\u65e5,
\u70b9\u6570,
IIF(\u70b9\u6570 > 90, ‘S’, IIF(\u70b9\u6570 > 80, ‘A’, IIF(\u70b9\u6570 > 60, ‘B’, IIF(\u70b9\u6570 > 30, ‘C’, ‘D’)))) AS \u6210\u7e3e
FROM dbo.Test_result
ORDER BY \u5b66\u7c4d\u756a\u53f7;<\/p>\n

\"blog107_2\"<\/a><\/p>\n

IIF\u95a2\u6570\u306e\u69cb\u6587\u306f\u3001IIF( boolean_expression, true_value, false_value ) \u3068\u3044\u3046\u3053\u3068\u3067\u3001boolean_expression \u304c true \u306b\u8a55\u4fa1\u3055\u308c\u305f\u5834\u5408\u306b\u3001true value \u306e\u5024\u3092\u8fd4\u3057\u3001false \u306b\u8a55\u4fa1\u3055\u308c\u305f\u5834\u5408\u306b\u3001flase value \u306e\u5024\u3092\u8fd4\u3057\u307e\u3059\u3002IIF\u95a2\u6570\u306f\u3001\u6700\u592710\u306e\u5165\u308c\u5b50\u304c\u3067\u304d\u308b\u3068\u3044\u3046\u3053\u3068\u306a\u306e\u3067\u3001\u4e0a\u8a18\u306e\u4f8b\u3067\u306f\u5165\u308c\u5b50\u306e\u8a2d\u5b9a\u306b\u3057\u3066\u307f\u307e\u3057\u305f\u304c\u3001CASE\u6587\u3068\u540c\u69d8\u306e\u62bd\u51fa\u304c\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n

\u6b21\u306b\u3001CHOOSE\u95a2\u6570\u3067\u3059\u304c\u3001\u69cb\u6587\u306f\u3001CHOOSE ( index, val_1, val_2 [, val_n ] ) \u3067\u3042\u308a\u3001\u5f8c\u7d9a\u306e val_1 \u4ee5\u964d\u306e\u30ea\u30b9\u30c8\u304b\u3089\u3001index \u5f15\u6570\u306e\u9806\u756a\u306e\u5024\u3092\u8fd4\u3059\u3082\u306e\u306b\u306a\u308a\u307e\u3059\u3002
\u4f8b\u3048\u3070\u3001CHOOSE(3, ‘A’, ‘B’, ‘C’, ‘D’) \u3067\u3042\u308c\u3070\u3001\u2019C\u2019 \u304c\u8fd4\u3063\u3066\u304d\u307e\u3059\u3002<\/p>\n

\"blog107_3\"<\/a><\/p>\n

\u5148\u307b\u3069\u306e Student\u30c6\u30fc\u30d6\u30eb\u306e\u751f\u5f92\u306b\u3064\u3044\u3066\u3001\u751f\u5e74\u6708\u65e5\u306b\u3088\u3063\u3066\u3001\u2019\u6625\u2019,\u2019\u590f\u2019,\u2019\u79cb\u2019,\u2019\u51ac\u2019 \u306e\u3069\u306e\u5b63\u7bc0\u306b\u751f\u307e\u308c\u305f\u306e\u304b\u3092\u62bd\u51fa\u3057\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

SELECT \u5b66\u7c4d\u756a\u53f7, \u6c0f\u540d, \u751f\u5e74\u6708\u65e5,
CHOOSE (DATEPART(MM, \u751f\u5e74\u6708\u65e5), ‘\u51ac’,’\u51ac’,’\u6625’,’\u6625’,’\u6625’,’\u590f’,’\u590f’,’\u590f’,’\u79cb’,’\u79cb’,’\u79cb’,’\u51ac’) AS \u8a95\u751f\u5b63\u7bc0
FROM dbo.Student
ORDER BY \u5b66\u7c4d\u756a\u53f7;<\/p>\n

\"blog107_4\"<\/a><\/p>\n

\u8a95\u751f\u6708\u306b\u3088\u3063\u3066\u3001\u5b63\u7bc0\u3092CHOOSE\u95a2\u6570\u306e\u30ea\u30b9\u30c8\u3067\u8a2d\u5b9a\u3057\u305f\u306e\u3067\u3001\u305d\u308c\u306b\u5408\u308f\u305b\u3066\u5404\u751f\u5f92\u306e\u8a95\u751f\u65e5\u306e\u5b63\u7bc0\u304c\u62bd\u51fa\u3055\u308c\u308b\u3053\u3068\u3092\u78ba\u8a8d\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n

\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 \u304b\u306a\u308a\u6642\u9593\u304c\u7d4c\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u304c\u3001\u4e0a\u306e\u5199\u771f\u306f\u3001\u4eca\u5e74\u306e\u6625\u306b\u3001\u666e\u6bb5\u304b\u3089\u4ea4\u6d41\u306e\u3042\u308b\u5730\u57df\u306e\u58ee\u5e74\u305f\u3061\u3068\u4e00\u7dd2\u306b\u5bcc\u58eb\u5c71\u306e\u8fd1\u304f\u306b\u3044\u3063\u305f\u3068\u304d\u306e\u5199\u771f\u3067\u3059\u3002\u5f53\u65e5\u306f\u671d\u304b\u3089\u66c7\u308a\u3067\u3001\u306a\u304b\u306a\u304b\u304d\u308c\u3044\u306a\u5bcc\u58eb\u5c71\u304c\u64ae\u308c\u306a\u304b […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[9],"tags":[],"class_list":["post-2366","post","type-post","status-publish","format-standard","hentry","category-9"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2366"}],"version-history":[{"count":4,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2366\/revisions"}],"predecessor-version":[{"id":2484,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2366\/revisions\/2484"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}