如何将两个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)更多推荐
发布评论