SELECT COUNT(RegisterCode) AS 隐患总数,
SUM(CASE WHEN ResultStatus = '已整改' THEN 1 ELSE 0 END) AS 已整改合计,
SUM(CASE WHEN ResultStatus = '未整改' THEN 1 ELSE 0 END) AS 未整改合计,
SUM(CASE WHEN ResultStatus = '未复查' THEN 1 ELSE 0 END) AS 未复查合计,
COUNT(PunishID) AS 处罚通报合计,
SUM(PTM) AS 队组罚金合计,
SUM(PPM) AS 个人罚金合计
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'
以上SQL语句的结果如下图所示:
现在想根据以上结果,显示成这样,如图:
请问,SQL语句应该怎样写?后附:
View_HiddCollect的代码:
ALTER VIEW [dbo].[VIEW_HiddCollect]
AS
SELECT DATEPART(YEAR, A.CheckDate) AS YearValue,
DATEPART(MONTH, A.CheckDate) AS MonthValue,
A.CheckPlace,
A.RegisterCode,
A.CheckDate AS CKDate,
A.ResultStatus,
PunishID,
ISNULL(PTM,0) AS PTM,
ISNULL(PPM,0) AS PPM
FROM HiddenMaster A
LEFT JOIN (SELECT RegisterCode,
PunishID,
SUM(ISNULL(PunishTeamMoney, 0)) AS PTM,
SUM(ISNULL(PunishPersonMoney, 0)) AS PPM
FROM HiddenPunish
GROUP BY RegisterCode, PunishID) AS B
ON (A.RegisterCode = B.RegisterCode)GO
使用的数据库是SQL Server 2008,在Delphi中使用。
SUM(CASE WHEN ResultStatus = '已整改' THEN 1 ELSE 0 END) AS 已整改合计,
SUM(CASE WHEN ResultStatus = '未整改' THEN 1 ELSE 0 END) AS 未整改合计,
SUM(CASE WHEN ResultStatus = '未复查' THEN 1 ELSE 0 END) AS 未复查合计,
COUNT(PunishID) AS 处罚通报合计,
SUM(PTM) AS 队组罚金合计,
SUM(PPM) AS 个人罚金合计
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'
以上SQL语句的结果如下图所示:
现在想根据以上结果,显示成这样,如图:
请问,SQL语句应该怎样写?后附:
View_HiddCollect的代码:
ALTER VIEW [dbo].[VIEW_HiddCollect]
AS
SELECT DATEPART(YEAR, A.CheckDate) AS YearValue,
DATEPART(MONTH, A.CheckDate) AS MonthValue,
A.CheckPlace,
A.RegisterCode,
A.CheckDate AS CKDate,
A.ResultStatus,
PunishID,
ISNULL(PTM,0) AS PTM,
ISNULL(PPM,0) AS PPM
FROM HiddenMaster A
LEFT JOIN (SELECT RegisterCode,
PunishID,
SUM(ISNULL(PunishTeamMoney, 0)) AS PTM,
SUM(ISNULL(PunishPersonMoney, 0)) AS PPM
FROM HiddenPunish
GROUP BY RegisterCode, PunishID) AS B
ON (A.RegisterCode = B.RegisterCode)GO
使用的数据库是SQL Server 2008,在Delphi中使用。
解决方案 »
- XE2 还没出来吗?
- 程序退出时出现Exception EAccessViolation怎么回事?
- 我喜欢的MM要去深圳,我想让她走啊,我改怎么办?大家救救我吧!
- 老兄,进来看一下吧,自己写的,顺便也散一下分!!
- shape控件的图标上有一个三角形,意思肯定是shape支持三角形了。为甚末我找不到呢?
- 分又来了
- 报表用qrdbrichText控件连接数据库怎么显示不出数据????
- “超时已过期”,当我用AdoQuery更新或删除大量数据时提示“超时已过期”
- 难以启齿,请告一个间单的问题!
- 如何取得ODBC中的DSN(用户,系统,文件)
- 谁能给我详细讲解一下adoquery1.SQL.Add('select * from 表名where 字段名='''+edit1.Text+'''’)的意思
- SPCOMM串口通信问题,真心求教!
你也可以用最简单的办法, 将你每个列的查询结果作为一个字段, 然后select 连接起来, 这样在mssql2000, mysql里面都适用
不过这样的话, sql语句会很长
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'
union all
SELECT (CASE WHEN ResultStatus = '已整改' THEN '已整改合计',
WHEN ResultStatus = '未整改' THEN '未整改合计',
WHEN ResultStatus = '未复查' THEN '未复查合计' ELSE '') AS 隐患排查情况,
COUNT(RegisterCode) AS 隐患总数
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'
Group By ResultStatus
union all
SELECT '处罚通报合计' AS 隐患排查情况,COUNT(PunishID) AS 隐患总数
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'
union all
SELECT '队组罚金合计' AS 隐患排查情况,SUM(PTM) AS 隐患总数
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'
union all
SELECT '个人罚金合计' AS 隐患排查情况,SUM(PPM) AS 隐患总数
FROM View_HiddCollect
WHERE CKDate BETWEEN '2014-10-01' AND '2014-10-31'至于前面的序号,我一般都是用一个自定义列,然后将RECNO显示上去
如果非得用SQL实现,好像是有一个{ROW NUMBER}的变量可以引用,我未试过,到SQL论坛找大神解决一下
顺便说一下,doloopcn,您的回复里,SQL Server里提示“,附近有语法错误”。我SQL极为垃圾,不会改。