select sum(case when inMainDoc!=0 then 1 else 0 end) inChildDoc, sum(case when inMainDoc=0 then 1 else 0 end) inMainDoc from tb
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-04-09 13:30:42 -- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([inMainDoc] INT) INSERT [tb] SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 0 UNION ALL SELECT 0 GO --SELECT * FROM [tb]-->SQL查询如下: select inChildDoc = sum(1-[inMainDoc]),inMainDoc=SUM([inMainDoc]) from tb /* inChildDoc inMainDoc ----------- ----------- 6 4(1 行受影响) */?
估计楼主是想统计maindoc 的数目, 用count() ,记得加group by
倒过来了,呵呵 select sum(case when inMainDoc=0 then 1 else 0 end) inChildDoc, sum(case when inMainDoc!=0 then 1 else 0 end) inMainDoc from tb
inMainDoc列在数据库中是nchar(1)类型的,提示说无法用sum
select sum(case when inMainDoc='0' then 1 else 0 end) inChildDoc, sum(case when inMainDoc!='0' then 1 else 0 end) inMainDoc from tb
inMainDoc
0
0
1
1
0
0
1
1
0
0
怎么写SQL语句让它成为这样的效果inChildDoc inMainDoc
6 4
inMainDoc
0
0
1
1
0
0
1
1
0
0
怎么写SQL语句让它成为这样的效果inChildDoc inMainDoc
6 4
sum(case when inMainDoc!=0 then 1 else 0 end) inChildDoc,
sum(case when inMainDoc=0 then 1 else 0 end) inMainDoc
from tb
-- Author : htl258(Tony)
-- Date : 2010-04-09 13:30:42
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([inMainDoc] INT)
INSERT [tb]
SELECT 0 UNION ALL
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 0 UNION ALL
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 0 UNION ALL
SELECT 0
GO
--SELECT * FROM [tb]-->SQL查询如下:
select inChildDoc = sum(1-[inMainDoc]),inMainDoc=SUM([inMainDoc]) from tb
/*
inChildDoc inMainDoc
----------- -----------
6 4(1 行受影响)
*/?
用count() ,记得加group by
select
sum(case when inMainDoc=0 then 1 else 0 end) inChildDoc,
sum(case when inMainDoc!=0 then 1 else 0 end) inMainDoc
from tb
select
sum(case when inMainDoc='0' then 1 else 0 end) inChildDoc,
sum(case when inMainDoc!='0' then 1 else 0 end) inMainDoc
from tb