请问,我现在只想通过SQL语句来计算图片中cnt列的SUM值,我该怎么计算?图片中的数值也是通过SQL语句查询出来的,我目前只会把这些语句变为一个实际的视图,然后再对这个视图进行计算。
但是我不想这么做,只想通过SQL语句弄出来。求各位大侠了
这里是图片中的SQL语句
SELECT
COUNT(1) AS cnt
FROM
dbo.ENT_SkillWorker AS esk INNER JOIN dbo.s_DictItem AS s1 ON esk.FLockedType = s1.FDictID LEFT OUTER JOIN
dbo.PRO_LaborContract AS pl ON esk.FLockedTenderNO = pl.FTenderNO
WHERE
(esk.FIsDeleted = 0) AND (esk.FLockStatus = 1) AND (pl.FIsDeleted = 0) AND (esk.FEntNO = '22012324288')
and (esk.FLockedTenderNO = '2011C123')
GROUP BY esk.FLockedType, s1.FName, pl.FBeginDate, pl.FEndDate
但是我不想这么做,只想通过SQL语句弄出来。求各位大侠了
这里是图片中的SQL语句
SELECT
COUNT(1) AS cnt
FROM
dbo.ENT_SkillWorker AS esk INNER JOIN dbo.s_DictItem AS s1 ON esk.FLockedType = s1.FDictID LEFT OUTER JOIN
dbo.PRO_LaborContract AS pl ON esk.FLockedTenderNO = pl.FTenderNO
WHERE
(esk.FIsDeleted = 0) AND (esk.FLockStatus = 1) AND (pl.FIsDeleted = 0) AND (esk.FEntNO = '22012324288')
and (esk.FLockedTenderNO = '2011C123')
GROUP BY esk.FLockedType, s1.FName, pl.FBeginDate, pl.FEndDate
select sum(cnt) from(SELECT
COUNT(1) AS cnt
FROM
dbo.ENT_SkillWorker AS esk INNER JOIN dbo.s_DictItem AS s1 ON esk.FLockedType = s1.FDictID LEFT OUTER JOIN
dbo.PRO_LaborContract AS pl ON esk.FLockedTenderNO = pl.FTenderNO
WHERE
(esk.FIsDeleted = 0) AND (esk.FLockStatus = 1) AND (pl.FIsDeleted = 0) AND (esk.FEntNO = '22012324288')
and (esk.FLockedTenderNO = '2011C123')
GROUP BY esk.FLockedType, s1.FName, pl.FBeginDate, pl.FEndDate )t
from
(SELECT
COUNT(1) AS cnt
FROM
dbo.ENT_SkillWorker AS esk INNER JOIN dbo.s_DictItem AS s1 ON esk.FLockedType = s1.FDictID LEFT OUTER JOIN
dbo.PRO_LaborContract AS pl ON esk.FLockedTenderNO = pl.FTenderNO
WHERE
(esk.FIsDeleted = 0) AND (esk.FLockStatus = 1) AND (pl.FIsDeleted = 0) AND (esk.FEntNO = '22012324288')
and (esk.FLockedTenderNO = '2011C123')
GROUP BY esk.FLockedType, s1.FName, pl.FBeginDate, pl.FEndDate )
a
SELECT
COUNT(1) AS cnt
FROM
dbo.ENT_SkillWorker AS esk INNER JOIN dbo.s_DictItem AS s1 ON esk.FLockedType = s1.FDictID LEFT OUTER JOIN
dbo.PRO_LaborContract AS pl ON esk.FLockedTenderNO = pl.FTenderNO
WHERE
(esk.FIsDeleted = 0) AND (esk.FLockStatus = 1) AND (pl.FIsDeleted = 0) AND (esk.FEntNO = '22012324288')
and (esk.FLockedTenderNO = '2011C123')
--GROUP BY esk.FLockedType, s1.FName, pl.FBeginDate, pl.FEndDate
--不用group,就是你要的结果.
这么写SQL2000好使么~?我怎么没理解这个意思呢?结果是正确的
我这里MSSQL2005能运行。服务器是2000的不知道能否运行
你的查询已经查出了不同 esk.FLockedType, s1.FName, pl.FBeginDate, pl.FEndDate 的记录数统计值.你说要求 cnt 的 sum,只要在外面套一层查询,把你的语句看成是子查询,就可以求得 cnt 的 sum 了.
如果把原来句子的 group 去掉,则结果与上面说的一样.
不过,我总觉得你没表述清楚,你究竟是要求什么的"和"?