2つのSQLテーブルから単一の結果セットを返します

debugcn 投稿 Dev

user3580480

同じ列を返す2つのSQLクエリがあります。

返される列名は、month_name、month_number、およびtotalです。

「合計」フィールドを合計して、2つのテーブルから単一の結果セットを返すにはどうすればよいですか？

クエリ1

SELECT DISTINCT
DATENAME(MONTH,DATEADD(MONTH,month([date]),-1 )) as month_name,
MONTH([date]) as month_number,
SUM(pur.total_expenses - pur.vat) as 'total'
FROM (select distinct ID, MONTH([date]) as month_number, DATENAME(MONTH,DATEADD(MONTH,month([date]),-1 )) as month_name from [dbo].[purchase_invoices] WHERE YEAR([date]) = '2020'  ) m
LEFT JOIN [dbo].[purchase_invoices] pur ON pur.id = m.id
LEFT JOIN [dbo].[dividends] div on month(dividend_date) = month([date]) AND month(dividend_date) =  '2020'
WHERE YEAR([date]) = '2020'
GROUP BY m.month_number, MONTH([date]), m.month_name
ORDER BY month_number

クエリ2

SELECT DISTINCT
DATENAME(MONTH,DATEADD(MONTH,month([dividend_date]),-1 )) as month_name,
MONTH([dividend_date]) as month_number,
SUM(div.dividend_value) as 'total'
FROM (select distinct ID, MONTH([dividend_date]) as month_number, DATENAME(MONTH,DATEADD(MONTH,month([dividend_date]),-1 )) as month_name from [dbo].[dividends] WHERE YEAR([dividend_date]) = '2020'  ) m
LEFT JOIN [dbo].[dividends] div ON div.id = m.id
WHERE YEAR([dividend_date]) = '2020'
GROUP BY m.month_number, MONTH([dividend_date]), m.month_name
ORDER BY month_number

month_nameまたはnumberフィールドにJOINが必要なことはわかっていますが、それを実現する方法がわかりません。

どんな助けでも大歓迎です。

UPDATE（期待される出力）

|---------------------|------------------|------------------|
|      month_name     |   month_number   |      total       |
|---------------------|------------------|------------------|
|          Jan        |         1        |      4500        |
|---------------------|------------------|------------------|
|          Feb        |         2        |      6000        |
|---------------------|------------------|------------------|
|          ...        |        ...       |       ...        |

ヘザー

CTEを使用してみてください。

WITH invoice AS (
  SELECT DISTINCT
    ID,
    MONTH([date]) AS month_number,
    DATENAME(MONTH, DATEADD(MONTH, MONTH([date]), -1)) AS month_name
 FROM[dbo].[purchase_invoices]
 WHERE
    YEAR([date]) = '2020'
),
q1 AS (
SELECT DISTINCT
    DATENAME(MONTH, DATEADD(MONTH, MONTH([date]), -1)) AS month_name,
    MONTH([date]) AS month_number,
    SUM(pur.total_expenses - pur.vat) AS 'total'
FROM invoice as m
LEFT JOIN[dbo].[purchase_invoices] pur
    ON pur.id = m.id
LEFT JOIN[dbo].[dividends] DIV
    ON MONTH(dividend_date) = MONTH([date])
    AND MONTH(dividend_date) = '2020'
WHERE
    YEAR([date]) = '2020'
GROUP BY
    m.month_number,
    MONTH([date]),
    m.month_name
ORDER BY
    month_number
),
dividend AS (
  SELECT DISTINCT
    ID,
    MONTH([dividend_date]) AS month_number,
    DATENAME(MONTH, DATEADD(MONTH, MONTH([dividend_date]), -1)) AS month_name
  FROM[dbo].[dividends]
  WHERE
    YEAR([dividend_date]) = '2020'
),
q2 AS (
  SELECT DISTINCT
    DATENAME(MONTH, DATEADD(MONTH, MONTH([dividend_date]), -1)) AS month_name,
    MONTH([dividend_date]) AS month_number,
    SUM(DIV.dividend_value) AS 'total'
FROM dividend as m
LEFT JOIN[dbo].[dividends] DIV
    ON DIV.id = m.id
WHERE
    YEAR([dividend_date]) = '2020'
GROUP BY
    m.month_number,
    MONTH([dividend_date]),
    m.month_name
ORDER BY
    month_number
)
  SELECT
   month_name,
   month_number,
   q1.total + q2.total AS total
 FROM q1
 LEFT JOIN q2
   ON q1.month_name = q2.month_name
   AND q1.month_number = q2.month_number

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]