teratail
回答率
85.35%
teratail header banner
teratail header banner
質問するログイン新規登録

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

新規登録して質問してみよう
ただいま回答率
85.35%
トップGROUP BYに関する質問
GROUP BY

GROUP　BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

JOIN

これはSQL文のJOINに関するタグです。リレーショナルデータベースシステムの二つ以上のテーブルを結合する際に、この構文が利用されます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

2回答

1049閲覧

複数のテーブルの生産実績集計を行いたい

beatleslife
beatleslife

総合スコア0

GROUP BY

GROUP　BYとはSQL文のひとつで、SELECT文において特定の列の値が等しい行ごとに表をグループ化します。

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

JOIN

これはSQL文のJOINに関するタグです。リレーショナルデータベースシステムの二つ以上のテーブルを結合する際に、この構文が利用されます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2021/08/15 15:33

編集2021/08/15 15:38

0

0

前提・実現したいこと

・SQLServer 2019
・SQL文

SQLにて複数のテーブルの実績集計を行い
下記のような集計_Data結果を得たいです。

商品シリアルの部分一致集計や複数のテーブルの集計が必要のため
非常に難航しており困っています。

ID日時商品名InコードOutコードIn計画Out計画In実績Out実績
12021-08-01パンAJI-00001TH-1234561111
22021-08-01パンBJI-00002TH-1000122222
32021-08-02パンAJI-00001TH-1234561111
42021-08-03パンCJI-00003DH-52R-452222
52021-08-03パンAJI-00001TH-1234562222
62021-08-04パンCJI-00003DH-52R-451110
72021-08-04パンBJI-00002TH-1000121110
82021-08-04パンAJI-00001TH-1234562200

1.計画データは

  • T_計画_Data　テーブルから予定日/商品コード毎に

　　|In計画|Out計画|を集計

2.IN実績データは

  • T_In_Data　テーブルから出荷日時/商品コード毎にカウント
  • |判定|がOKのもので

　商品シリアルの重複は取り除いてカウントを行いたいです。

  • 商品シリアル(個別番号+連番)で実績が残っているため

　　T_紐づけ表　テーブルの
|商品シリアル|開始文字数|文字数|に一致するものを
商品コード毎に集計する必要があります。（含む検索？）

  • 実績は当日8:30～翌8:30が同日実績の扱いになります。

3.Out実績データは

  • T_Out_Data　テーブルから納入完了日/商品コード毎に
    納入状況が[完了]のものを　|納入数|　を合算集計します。
  • 実績は当日8:30～翌8:30が同日実績の扱いになります。
  • ※IN実績とOUT実績の商品コードが違うため

　T_紐づけ表テーブルの|Inコード|Outコード|で紐づける必要があります。

T_計画_Data

ID予定日商品名商品コードIn計画Out計画
12021-08-01パンAJI-0000111
22021-08-01パンBJI-0000222
32021-08-02パンAJI-0000111
42021-08-03パンCJI-0000322
52021-08-03パンAJI-0000122
62021-08-04パンCJI-0000311
72021-08-04パンBJI-0000211
82021-08-04パンAJI-0000122

T_In_Data

ID商品シリアル検査日時判定出荷日時
11AB1000012021-08-01 08:31:01NG2021-08-02 15:03:02
22AB1000022021-08-01 08:35:01OK2021-08-01 15:01:09
33TE000012021-08-01 08:41:01OK2021-08-01 15:10:02
43TE000022021-08-01 08:52:01OK2021-08-01 15:15:02
51AB1000012021-08-02 08:52:01OK2021-08-02 15:03:02
6AF1-1250012021-08-02 09:03:01OK2021-08-02 15:09:02
75G0000012021-08-02 23:21:01OK2021-08-03 01:01:02
8AF1-1250022021-08-03 09:21:01OK2021-08-03 15:01:02
91AB1000032021-08-03 08:31:01OK2021-08-03 15:08:02
102AB1000042021-08-03 08:31:01OK2021-08-03 15:09:02
113TE1000032021-08-03 08:31:01OK2021-08-03 15:03:02

T_Out_Data

|ID|商品名|商品コード|納入状況|納入予定日|納入完了日|予定数|納入数|
|:--:|:--:|:--:|:--:|--:|--:|
|1|パンA|TH-123456|完了|2021-08-01 17:00:00|2021-08-01 16:01:09|1|1|
|2|パンB|TH-100012|完了|2021-08-01 17:00:00|2021-08-01 16:01:19|2|2|
|3|パンC|DH-52R-45|完了|2021-08-03 09:00:00|2021-08-03 08:01:09|2|2|
|4|パンC|DH-52R-45|未納|2021-08-04 09:00:00|NULL|1||
|5|パンA|TH-123456|完了|2021-08-02 17:00:00|2021-08-02 16:01:09|1|1|
|6|パンA|TH-123456|未納|2021-08-03 17:00:00|NULL|2||

T_紐づけ表

ID商品名InコードOutコード商品シリアル開始文字数文字数
1パンAJI-00001TH-123456AB123
2パンBJI-00002TH-1000123TE13
3パンCJI-00003DH-52R-45AF1-12517
4パンCJI-00003DH-52R-455GJ13

発生している問題・エ

ラーメッセージ

エラーメッセージ

該当のソースコード

ソースコード

試したこと

下記のような8:30-8:30の日毎集計は
IN実績/OUT実績で行えることは確認できましたが、
IN実績商品コード一部一致集計や複数テーブルとのJOINが全くうまくいかない状態です。
'=========OUT実績=========
SELECT
CONVERT (varchar,dateadd(HOUR,-8.5,[納入完了日]),111) as 納入完了日,
[商品コード] AS [OUTコード],
Count (*) as 計数,
sum(完了数量)
FROM smt.T_Out_Data
WHERE [納入状況] = '完了' And [完了日時] >= '2021-08-01'
GROUP BY CONVERT (varchar,dateadd(HOUR,-8.5,[納入完了日]),111),[商品コード]

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2件

0

T_In_Data 及び T_Out_Data のデータを拝見すると
T_計画_Data に記載されていない商品の入出荷が散見されます。

したがって、ご希望の結果セットを返すためには
テーブル間で、以下の結合を用いる必要があるでしょう。

外部表内部表結合種別
T_紐づけ表T_In_Data内部結合
T_紐づけ表T_Out_Data内部結合
T_紐づけ表T_計画_Data内部結合
T_In_DataT_計画_Data完全外部結合
T_Out_DataT_計画_Data完全外部結合
　
　
```sql
WITH v_base ( a, b, c, d, e, f, g, h, i ) AS
(
SELECT x.ID
     , cast( x.予定日 As date )
     , x.商品名
     , x.商品コード
     , y.Outコード
     , x.In計画
     , x.Out計画
     , 0
     , 0
FROM T_計画_Data x
INNER JOIN
(
    SELECT DISTINCT Inコード, Outコード
    FROM T_紐づけ表
) y
ON x.商品コード = y.Inコード

)

, v_in ( a, b, c, d, e, f, g, h, i ) AS
(
SELECT 0
, cast( dateadd( minute, -510, y.出荷日時 ) as date )
, x.商品名
, x.Inコード
, x.Outコード
, 0
, 0
, 1
, 0
FROM T_紐づけ表 x
, T_In_Data y
WHERE y.判定 = 'OK'
AND x.商品シリアル = substring( y.商品シリアル, x.開始文字数, x.文字数 )
AND cast( dateadd( minute, -510, y.出荷日時 ) as date )
>= datefromparts( year( getdate() ), month( getdate() ), 1 )
)

, v_out ( a, b, c, d, e, f, g, h, i ) AS
(
SELECT 0
, cast( dateadd( minute, -510, x.納入完了日 ) as date )
, x.商品名
, y.Inコード
, x.商品コード
, 0
, 0
, 0
, x.納入数
FROM T_Out_Data x
INNER JOIN
(
SELECT DISTINCT Inコード, Outコード
FROM T_紐づけ表
) y
ON x.商品コード = y.Outコード
WHERE x.納入状況 = '完了'
AND cast( dateadd( minute, -510, x.納入完了日 ) as date )
>= datefromparts( year( getdate() ), month( getdate() ), 1 )
)

SELECT max( a ) ID
, b 日時
, c 商品名
, d Inコード
, e Outコード
, sum( f ) In計画
, sum( g ) Out計画
, sum( h ) In実績
, sum( i ) Out実績
FROM
(
SELECT a, b, c, d, e, f, g, h, i
FROM v_base
UNION ALL
SELECT a, b, c, d, e, f, g, h, i
FROM v_in
UNION ALL
SELECT a, b, c, d, e, f, g, h, i
FROM v_out
) q
GROUP BY b
, c
, d
, e
ORDER BY 2, 1 ;

### 結果
( T_In_Data の商品シリアル 5G000001 は 5GJ00001 の記載ミスと解釈しています )
| ID |    日時    | 商品名 |  Inコード |  Outコード | In計画 | Out計画 | In実績 | Out実績 |
|:--:|:----------:|:------:|:---------:|:----------:|:------:|:-------:|:------:|:-------:|
| 1  | 2021-08-01 | パンA  | JI-00001  | TH-123456  | 1      | 1       | 1      | 1       |
| 2  | 2021-08-01 | パンB  | JI-00002  | TH-100012  | 2      | 2       | 2      | 2       |
| 0  | 2021-08-02 | パンC  | JI-00003  | DH-52R-45  | 0      | 0       | 2      | 2       |
| 3  | 2021-08-02 | パンA  | JI-00001  | TH-123456  | 1      | 1       | 1      | 1       |
| 0  | 2021-08-03 | パンB  | JI-00002  | TH-100012  | 0      | 0       | 1      | 0       |
| 4  | 2021-08-03 | パンC  | JI-00003  | DH-52R-45  | 2      | 2       | 1      | 0       |
| 5  | 2021-08-03 | パンA  | JI-00001  | TH-123456  | 2      | 2       | 2      | 0       |
| 6  | 2021-08-04 | パンC  | JI-00003  | DH-52R-45  | 1      | 1       | 0      | 0       |
| 7  | 2021-08-04 | パンB  | JI-00002  | TH-100012  | 1      | 1       | 0      | 0       |
| 8  | 2021-08-04 | パンA  | JI-00001  | TH-123456  | 2      | 2       | 0      | 0       |

追記：
----
> ・ T_In_Data　テーブルから出荷日時/商品コード毎にカウント
> ・ 商品シリアルの重複は取り除いてカウントを行いたいです。 

については、ヒントを出しておきます。

| ID | 商品シリアル |       検査日時      | 判定 |       出荷日時      |
|:--:|:------------:|:-------------------:|:----:|:-------------------:|
| 50 | AF1-125010   | 2021-08-10 09:20:59 | OK   | 2021-08-10 14:11:34 |
| 51 | 5GJ00021     | 2021-08-10 10:01:27 | OK   | 2021-08-10 22:08:55 |
| 52 | 5GJ00022     | 2021-08-11 05:58:43 | OK   | 2021-08-11 07:46:02 |


これらのレコードを何件と定義するかで 共通式 ```v_in``` の記述を変更するといいでしょう。
- 1件として扱う ```SELECT DISTINCT 0,``` ... 
- 2件として扱う ```count( distinct T_紐づけ表.商品シリアル )```
- 3件として扱う ```現状のSQL文を維持```、或いは ```count(*)```

投稿2021/08/16 16:12

編集2021/08/17 14:22
mayu-
mayu-

総合スコア335

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

やりたいことが複雑で大きいので、部分一致検索の方法だけ回答します。

SQLの部分一致は LIKE 句で行うことができます。
LIKE 句ではワイルドカードが使用でき、
% は0文字以上の任意の文字、_ は1文字の任意の文字を表します。
例えば以下です。

SQL
1SELECT id FROM T_In_Data WHERE 商品シリアル LIKE 'A%'
2-- id　6,8 が取れる
3
4SELECT id FROM T_In_Data WHERE 商品シリアル LIKE '_AB%'
5-- id 1, 2, 5, 9, 10 が取れる

次に、 LIKE 句に列の値を使用したい場合は、 CONCAT 関数を使用します。
例えば以下です。

SQL
1SELECT id FROM T_In_Data WHERE 商品シリアル LIKE CONCAT('%', 判定, '%')
2-- 商品シリアルにその行の判定列（OK/NG）の文字列が含まれるデータが取れる

これらを組み合わせれば、紐づけテーブルと In_Data テーブルを結合することはできそうです。
紐づけテーブルの 開始文字数 列が「In_Dataテーブルの商品シリアルの何文字目から 紐づけテーブルの商品シリアル列が入るか」ということを表しているっぽいので、

SQL
1SELECT *
2FROM T_紐づけ表 AS h LEFT JOIN T_In_Data AS i
3  ON (h.開始文字数 = 1 AND i.商品シリアル LIKE CONCAT(h.商品シリアル, '%'))
4    OR (h.開始文字数 = 2 AND i.商品シリアル LIKE CONCAT('_', h.商品シリアル, '%'))
5    OR (h.開始文字数 = 3 AND i.商品シリアル LIKE CONCAT('__', h.商品シリアル, '%'))

でどうかなと。開始文字数が4以上のものがある場合は適宜OR条件で付与してください。

ここの結合ができれば、サブクエリなり何なり使って集計はできそうな気がします。

投稿2021/08/16 13:15

gekijin
gekijin

総合スコア187

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう！

ただいまの回答率
85.35%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

トップGROUP BYに関する質問

複数のテーブルの生産実績集計を行いたい