数据库是SQL SERVER
这个查询只涉及到一个表,
这个表的名称是tbl_change_status
有如下几个需要在查询中用到的字段:
objname:对象名称
start_status:开始状态
end_status:结束状态
hours_count:小时数.

现在的要求是这样子的:
要求统计不同对象转到某开始状态(start_status)然后以结束状态(end_status)结束的次数和小时数,,,,
我写了一个下午,终于用程序写出了下面的语句,但是还有两个解决不了的问题:
一.我不能统计次数(这个我想出了方法,但是感觉不太好),
二.我想去除所有为0的列数据(如:如果所有对象的"转S以S结束"这个字段是0的话,我就不要查询出这一列),这个问题我是怎么也解决不了,不知道大家有什么好的方法?
要求不要用存储过程,而是直接用SQL语句
,也不能用多条SQL语句,只要一条SQL语句.
大家帮帮我吧.

下面是我的语句:
SELECT objname,
SUM(转S以S结束) AS 转S以S结束,
SUM(转S以G结束) AS 转S以G结束,
SUM(转S以P结束) AS 转S以P结束,
SUM(转S以CG结束) AS 转S以CG结束,
SUM(转S以CP结束) AS 转S以CP结束,
SUM(转S以SR结束) AS 转S以SR结束,
SUM(转S以FOOS结束) AS 转S以FOOS结束,
SUM(转S以POOS结束) AS 转S以POOS结束,
SUM(转G以S结束) AS 转G以S结束,
SUM(转G以G结束) AS 转G以G结束,
SUM(转G以P结束) AS 转G以P结束,
SUM(转G以CG结束) AS 转G以CG结束,
SUM(转G以CP结束) AS 转G以CP结束,
SUM(转G以SR结束) AS 转G以SR结束,
SUM(转G以FOOS结束) AS 转G以FOOS结束,
SUM(转G以POOS结束) AS 转G以POOS结束,
SUM(转P以S结束) AS 转P以S结束,
SUM(转P以G结束) AS 转P以G结束,
SUM(转P以P结束) AS 转P以P结束,
SUM(转P以CG结束) AS 转P以CG结束,
SUM(转P以CP结束) AS 转P以CP结束,
SUM(转P以SR结束) AS 转P以SR结束,
SUM(转P以FOOS结束) AS 转P以FOOS结束,
SUM(转P以POOS结束) AS 转P以POOS结束,
SUM(转CG以S结束) AS 转CG以S结束,
SUM(转CG以G结束) AS 转CG以G结束,
SUM(转CG以P结束) AS 转CG以P结束,
SUM(转CG以CG结束) AS 转CG以CG结束,
SUM(转CG以CP结束) AS 转CG以CP结束,
SUM(转CG以SR结束) AS 转CG以SR结束,
SUM(转CG以FOOS结束) AS 转CG以FOOS结束,
SUM(转CG以POOS结束) AS 转CG以POOS结束,
SUM(转CP以S结束) AS 转CP以S结束,
SUM(转CP以G结束) AS 转CP以G结束,
SUM(转CP以P结束) AS 转CP以P结束,
SUM(转CP以CG结束) AS 转CP以CG结束,
SUM(转CP以CP结束) AS 转CP以CP结束,
SUM(转CP以SR结束) AS 转CP以SR结束,
SUM(转CP以FOOS结束) AS 转CP以FOOS结束,
SUM(转CP以POOS结束) AS 转CP以POOS结束,
SUM(转SR以S结束) AS 转SR以S结束,
SUM(转SR以G结束) AS 转SR以G结束,
SUM(转SR以P结束) AS 转SR以P结束,
SUM(转SR以CG结束) AS 转SR以CG结束,
SUM(转SR以CP结束) AS 转SR以CP结束,
SUM(转SR以SR结束) AS 转SR以SR结束,
SUM(转SR以FOOS结束) AS 转SR以FOOS结束,
SUM(转SR以POOS结束) AS 转SR以POOS结束,
SUM(转FOOS以S结束) AS 转FOOS以S结束,
SUM(转FOOS以G结束) AS 转FOOS以G结束,
SUM(转FOOS以P结束) AS 转FOOS以P结束,
SUM(转FOOS以CG结束) AS 转FOOS以CG结束,
SUM(转FOOS以CP结束) AS 转FOOS以CP结束,
SUM(转FOOS以SR结束) AS 转FOOS以SR结束,
SUM(转FOOS以FOOS结束) AS 转FOOS以FOOS结束,
SUM(转FOOS以POOS结束) AS 转FOOS以POOS结束,
SUM(转POOS以S结束) AS 转POOS以S结束,
SUM(转POOS以G结束) AS 转POOS以G结束,
SUM(转POOS以P结束) AS 转POOS以P结束,
SUM(转POOS以CG结束) AS 转POOS以CG结束,
SUM(转POOS以CP结束) AS 转POOS以CP结束,
SUM(转POOS以SR结束) AS 转POOS以SR结束,
SUM(转POOS以FOOS结束) AS 转POOS以FOOS结束,
SUM(转POOS以POOS结束) AS 转POOS以POOS结束
FROM (
SELECT objname,
CASE start_status+':'+end_status WHEN 'S:S' THEN hours_count ELSE 0 END AS 转S以S结束,
CASE start_status+':'+end_status WHEN 'S:G' THEN hours_count ELSE 0 END AS 转S以G结束,
CASE start_status+':'+end_status WHEN 'S' THEN hours_count ELSE 0 END AS 转S以P结束,
CASE start_status+':'+end_status WHEN 'S:CG' THEN hours_count ELSE 0 END AS 转S以CG结束,
CASE start_status+':'+end_status WHEN 'S:CP' THEN hours_count ELSE 0 END AS 转S以CP结束,
CASE start_status+':'+end_status WHEN 'S:SR' THEN hours_count ELSE 0 END AS 转S以SR结束,
CASE start_status+':'+end_status WHEN 'S:FOOS' THEN hours_count ELSE 0 END AS 转S以FOOS结束,
CASE start_status+':'+end_status WHEN 'SOOS' THEN hours_count ELSE 0 END AS 转S以POOS结束,
CASE start_status+':'+end_status WHEN 'G:S' THEN hours_count ELSE 0 END AS 转G以S结束,
CASE start_status+':'+end_status WHEN 'G:G' THEN hours_count ELSE 0 END AS 转G以G结束,
CASE start_status+':'+end_status WHEN 'G' THEN hours_count ELSE 0 END AS 转G以P结束,
CASE start_status+':'+end_status WHEN 'G:CG' THEN hours_count ELSE 0 END AS 转G以CG结束,
CASE start_status+':'+end_status WHEN 'G:CP' THEN hours_count ELSE 0 END AS 转G以CP结束,
CASE start_status+':'+end_status WHEN 'G:SR' THEN hours_count ELSE 0 END AS 转G以SR结束,
CASE start_status+':'+end_status WHEN 'G:FOOS' THEN hours_count ELSE 0 END AS 转G以FOOS结束,
CASE start_status+':'+end_status WHEN 'GOOS' THEN hours_count ELSE 0 END AS 转G以POOS结束,
CASE start_status+':'+end_status WHEN 'P:S' THEN hours_count ELSE 0 END AS 转P以S结束,
CASE start_status+':'+end_status WHEN 'P:G' THEN hours_count ELSE 0 END AS 转P以G结束,
CASE start_status+':'+end_status WHEN 'P' THEN hours_count ELSE 0 END AS 转P以P结束,
CASE start_status+':'+end_status WHEN 'P:CG' THEN hours_count ELSE 0 END AS 转P以CG结束,
CASE start_status+':'+end_status WHEN 'P:CP' THEN hours_count ELSE 0 END AS 转P以CP结束,
CASE start_status+':'+end_status WHEN 'P:SR' THEN hours_count ELSE 0 END AS 转P以SR结束,
CASE start_status+':'+end_status WHEN 'P:FOOS' THEN hours_count ELSE 0 END AS 转P以FOOS结束,
CASE start_status+':'+end_status WHEN 'POOS' THEN hours_count ELSE 0 END AS 转P以POOS结束,
CASE start_status+':'+end_status WHEN 'CG:S' THEN hours_count ELSE 0 END AS 转CG以S结束,
CASE start_status+':'+end_status WHEN 'CG:G' THEN hours_count ELSE 0 END AS 转CG以G结束,
CASE start_status+':'+end_status WHEN 'CG' THEN hours_count ELSE 0 END AS 转CG以P结束,
CASE start_status+':'+end_status WHEN 'CG:CG' THEN hours_count ELSE 0 END AS 转CG以CG结束,
CASE start_status+':'+end_status WHEN 'CG:CP' THEN hours_count ELSE 0 END AS 转CG以CP结束,
CASE start_status+':'+end_status WHEN 'CG:SR' THEN hours_count ELSE 0 END AS 转CG以SR结束,
CASE start_status+':'+end_status WHEN 'CG:FOOS' THEN hours_count ELSE 0 END AS 转CG以FOOS结束,
CASE start_status+':'+end_status WHEN 'CGOOS' THEN hours_count ELSE 0 END AS 转CG以POOS结束,
CASE start_status+':'+end_status WHEN 'CP:S' THEN hours_count ELSE 0 END AS 转CP以S结束,
CASE start_status+':'+end_status WHEN 'CP:G' THEN hours_count ELSE 0 END AS 转CP以G结束,
CASE start_status+':'+end_status WHEN 'CP' THEN hours_count ELSE 0 END AS 转CP以P结束,
CASE start_status+':'+end_status WHEN 'CP:CG' THEN hours_count ELSE 0 END AS 转CP以CG结束,
CASE start_status+':'+end_status WHEN 'CP:CP' THEN hours_count ELSE 0 END AS 转CP以CP结束,
CASE start_status+':'+end_status WHEN 'CP:SR' THEN hours_count ELSE 0 END AS 转CP以SR结束,
CASE start_status+':'+end_status WHEN 'CP:FOOS' THEN hours_count ELSE 0 END AS 转CP以FOOS结束,
CASE start_status+':'+end_status WHEN 'CPOOS' THEN hours_count ELSE 0 END AS 转CP以POOS结束,
CASE start_status+':'+end_status WHEN 'SR:S' THEN hours_count ELSE 0 END AS 转SR以S结束,
CASE start_status+':'+end_status WHEN 'SR:G' THEN hours_count ELSE 0 END AS 转SR以G结束,
CASE start_status+':'+end_status WHEN 'SR' THEN hours_count ELSE 0 END AS 转SR以P结束,
CASE start_status+':'+end_status WHEN 'SR:CG' THEN hours_count ELSE 0 END AS 转SR以CG结束,
CASE start_status+':'+end_status WHEN 'SR:CP' THEN hours_count ELSE 0 END AS 转SR以CP结束,
CASE start_status+':'+end_status WHEN 'SR:SR' THEN hours_count ELSE 0 END AS 转SR以SR结束,
CASE start_status+':'+end_status WHEN 'SR:FOOS' THEN hours_count ELSE 0 END AS 转SR以FOOS结束,
CASE start_status+':'+end_status WHEN 'SROOS' THEN hours_count ELSE 0 END AS 转SR以POOS结束,
CASE start_status+':'+end_status WHEN 'FOOS:S' THEN hours_count ELSE 0 END AS 转FOOS以S结束,
CASE start_status+':'+end_status WHEN 'FOOS:G' THEN hours_count ELSE 0 END AS 转FOOS以G结束,
CASE start_status+':'+end_status WHEN 'FOOS' THEN hours_count ELSE 0 END AS 转FOOS以P结束,
CASE start_status+':'+end_status WHEN 'FOOS:CG' THEN hours_count ELSE 0 END AS 转FOOS以CG结束,
CASE start_status+':'+end_status WHEN 'FOOS:CP' THEN hours_count ELSE 0 END AS 转FOOS以CP结束,
CASE start_status+':'+end_status WHEN 'FOOS:SR' THEN hours_count ELSE 0 END AS 转FOOS以SR结束,
CASE start_status+':'+end_status WHEN 'FOOS:FOOS' THEN hours_count ELSE 0 END AS 转FOOS以FOOS结束,
CASE start_status+':'+end_status WHEN 'FOOSOOS' THEN hours_count ELSE 0 END AS 转FOOS以POOS结束,
CASE start_status+':'+end_status WHEN 'POOS:S' THEN hours_count ELSE 0 END AS 转POOS以S结束,
CASE start_status+':'+end_status WHEN 'POOS:G' THEN hours_count ELSE 0 END AS 转POOS以G结束,
CASE start_status+':'+end_status WHEN 'POOS' THEN hours_count ELSE 0 END AS 转POOS以P结束,
CASE start_status+':'+end_status WHEN 'POOS:CG' THEN hours_count ELSE 0 END AS 转POOS以CG结束,
CASE start_status+':'+end_status WHEN 'POOS:CP' THEN hours_count ELSE 0 END AS 转POOS以CP结束,
CASE start_status+':'+end_status WHEN 'POOS:SR' THEN hours_count ELSE 0 END AS 转POOS以SR结束,
CASE start_status+':'+end_status WHEN 'POOS:FOOS' THEN hours_count ELSE 0 END AS 转POOS以FOOS结束,
CASE start_status+':'+end_status WHEN 'POOSOOS' THEN hours_count ELSE 0 END AS 转POOS以POOS结束
FROM tbl_change_status) AS A
GROUP BY a.objname

下面是个表的结构:
CREATE TABLE [tbl_change_status] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[ban_no] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[object_id] [int] NULL ,
[objname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[start_status] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[start_date] [datetime] NULL ,
[start_result] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[cmd_dept] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[cmd_person] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[work_person] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[end_date] [datetime] NULL ,
[end_status] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[end_result] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[run_result] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[hours_count] [numeric](24, 2) NULL ,
[userid] [int] NULL ,
[note] [varchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_tbl_change_status] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO