代码之家 › 专栏 › 技术社区 › webworm

将多个查询转换为单行

reporting sql

webworm · 技术社区 · 14 年前

我有一个报告,我想基于一个单一的SQL语句。问题是数据是基于几个SQL语句的。例如。

SELECT COUNT(*) as 'Cases Opened' 
FROM tblCases 
WHERE DateAssigned BETWEEN @StartDate AND @EndDate

SELECT COUNT(*) as 'Cases Closed' 
FROM tblCases 
WHERE ClosedDate BETWEEN @StartDate AND @EndDate

SELECT COUNT(*) as 'Tickets Issued' 
FROM tblTicket 
WHERE DateIssued BETWEEN @StartDate AND @EndDate

SELECT COUNT(*) as 'Warnings Issued' 
FROM tblWarning 
WHERE DateIssued BETWEEN @StartDate AND @EndDate

有没有办法把这四个独立的SQL语句转换成一个SQL语句,这样每个结果都列出来?例如。。

Cases Opened        Cases Closed       Tickets Issued        Warnings Issued
******************************************************************************
   256         |      165          |        56           |          165

编辑我使用的是SQL Server,表之间没有关系。

3 回复 | 直到 14 年前

Nathan Feger 14 年前

select 
 (
 SELECT COUNT(*)  
 FROM tblCases 
 WHERE DateAssigned BETWEEN @StartDate AND @EndDate
 ) as 'Cases Opened' ,
 (SELECT COUNT(*)  
 FROM tblCases 
 WHERE ClosedDate BETWEEN @StartDate AND @EndDate
 ) as 'Cases Closed' ,
 (SELECT COUNT(*)  
 FROM tblTicket 
 WHERE DateIssued BETWEEN @StartDate AND @EndDate
 ) as 'Tickets Issued' ,
 (SELECT COUNT(*) 
 FROM tblWarning 
 WHERE DateIssued BETWEEN @StartDate AND @EndDate
 )  as 'Warnings Issued'
from dual

Dan J 14 年前

SELECT SUM(CASE WHEN FieldName='Cases Opened' THEN Value ELSE 0 END) AS Cases_Opened,
       SUM(CASE WHEN FieldName='Cases Closed' THEN Value ELSE 0 END AS Cases_Closed,
       SUM(CASE WHEN FieldName='Warning Issued' THEN Value ELSE 0 END) AS Warnings_Issued,
       SUM(CASE WHEN FieldName='Tickets Issued' THEN Value ELSE 0 END) AS Tickets_Issued
FROM 
(
SELECT COUNT(*) as Value, 'Cases Opened' as FieldName
FROM tblCases 
WHERE DateAssigned BETWEEN @StartDate AND @EndDate

UNION

SELECT COUNT(*) as Value, 'Cases Closed' as FieldName
FROM tblCases 
WHERE ClosedDate BETWEEN @StartDate AND @EndDate

UNION

SELECT COUNT(*) as Value, 'Tickets Issued' as FieldName
FROM tblTicket 
WHERE DateIssued BETWEEN @StartDate AND @EndDate

UNION

SELECT COUNT(*) as Value, 'Warnings Issued' as FieldName
FROM tblWarning 
WHERE DateIssued BETWEEN @StartDate AND @EndDate
)

bigtang 14 年前

如果在SQLServer中运行,请检查pivot语句