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中使用。
解决方案 »
- label+edit delphi 自己开发控件问题
- 關於sql-server服務器不存在或拒絕訪問的問題
- EnumProcessModules不能取得PID为0和4的系统进程映像名?或者它根本就没有映像名?
- (100分)这样的表该如何处理,不够再加
- 请教思路:如何实现将QUICKREPORT的报表生成JPG图片,谢谢!
- 我该不该辞职?
- 怎么写穷举密码的算法
- 做过进销存的来看看,你们的库存表里有重复的记录吗?
- 新手提问:ShellExecute使用方法?
- ◆◆◆Delphi中,三层数据库的多表更新问题?◆◆◆
- 谁能给我详细讲解一下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极为垃圾,不会改。