如何将两个SQL SELECTqueries合并到SQL Server CE中的一个查询中？(How to merge two SQL SELECTqueries into one query in

如何将两个SQL SELECTqueries合并到SQL Server CE中的一个查询中？(How to merge two SQL SELECTqueries into one query in SQL Server CE?)

我有两个SELECT查询，返回超过45和小于45.我需要将这两个查询合并为一个。 如果有可能，我该如何实现呢？

查询1：

SELECT SUM(tbl_invoices.pendingamount) AS morethan45 FROM tbl_debit INNER JOIN tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice WHERE (tbl_invoices.state = - 1) AND (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)

查询2：

SELECT SUM(tbl_invoices.pendingamount) AS lessthan45 FROM tbl_debit INNER JOIN tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice WHERE (tbl_invoices.state = - 1) AND (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45)

我尝试了以下查询，但它返回错误。

SELECT (Query 1),(Query 2)

请帮我解决这个问题。

I have two SELECT queries, those return more than 45 and less than 45. I need to merge these 2 queries into one. If it is possible, how can I achieve it?

Query 1:

SELECT SUM(tbl_invoices.pendingamount) AS morethan45 FROM tbl_debit INNER JOIN tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice WHERE (tbl_invoices.state = - 1) AND (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45)

Query 2:

SELECT SUM(tbl_invoices.pendingamount) AS lessthan45 FROM tbl_debit INNER JOIN tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice WHERE (tbl_invoices.state = - 1) AND (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45)

I tried following query but it returns an error.

SELECT (Query 1),(Query 2)

Please help me to solve this issue.

最满意答案

你应该可以使用CASE ，比如;

SELECT SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45) THEN tbl_invoices.pendingamount ELSE 0 END) AS morethan45, SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45) THEN tbl_invoices.pendingamount ELSE 0 END) AS lessthan45 FROM tbl_debit INNER JOIN tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice WHERE (tbl_invoices.state = - 1)

You should be able to use CASE, something like;

SELECT SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) >= 45) THEN tbl_invoices.pendingamount ELSE 0 END) AS morethan45, SUM(CASE WHEN (DATEDIFF(day, tbl_debit.purchasedate, GETDATE()) < 45) THEN tbl_invoices.pendingamount ELSE 0 END) AS lessthan45 FROM tbl_debit INNER JOIN tbl_invoices ON tbl_debit.invoice = tbl_invoices.invoice WHERE (tbl_invoices.state = - 1)