--测试数据 declare @table table (A int,B int,C int) insert into @table select 1,2,null union all select null,3,1 union all select 1,1,1查询 select A,B,C, (case ISNULL(A,0) when 0 then 0 else 1 end)+ (case ISNULL(B,0) when 0 then 0 else 1 end)+ (case ISNULL(C,0) when 0 then 0 else 1 end) as '汇总' from @table
/*结果 A B C 汇总 ----------- ----------- ----------- ----------- 1 2 NULL 2 NULL 3 1 2 1 1 1 3 */
--测试数据 declare @table table (A int,B int,C int) insert into @table select 1,2,null union all select null,3,1 union all select 1,1,1--查询 select A,B,C, (case ISNULL(A,0) when 0 then 0 else 1 end)+ (case ISNULL(B,0) when 0 then 0 else 1 end)+ (case ISNULL(C,0) when 0 then 0 else 1 end) as '汇总' from @table
/*结果 A B C 汇总 ----------- ----------- ----------- ----------- 1 2 NULL 2 NULL 3 1 2 1 1 1 3 */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-09-24 23:15:11 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] int,[C] int) insert [tb] select 1,2,null union all select null,3,1 union all select 1,1,1 --------------开始查询--------------------------select a,b,c, sum(case when a is null then 0 else 1 end) +sum(case when b is null then 0 else 1 end) +sum(case when c is null then 0 else 1 end) as num from [tb] group by a,b,c ----------------结果---------------------------- /* a b c num ----------- ----------- ----------- ----------- NULL 3 1 2 1 1 1 3 1 2 NULL 2(3 行受影响) */
select *, 汇总 = case when a is null then 0 else 1 end + case when b is null then 0 else 1 end + case when c is null then 0 else 1 end ... from tb
谢谢大家,但实际问题是,有两个表:A表ID 1 2 3 4B表 ID 1 3 4 5我用SQL求取交集:SELECT * FROM( SELECT T.ID, SUM(CASE WHEN T.Flag='A' THEN 1 ELSE NULL END) AS A表, SELECT T.ID, SUM(CASE WHEN T.Flag='B' THEN 1 ELSE NULL END) AS B表, COUNT(T.Flag) AS 汇总 FROM( SELECT A.*, 'A' AS Flag FROM A UNION ALL SELECT B.*, 'B' AS Flag FROM B) T GROUP BY T.ID) M ORDER BY 汇总 DESC得出结果: T A表 B表 汇总 1 1 1 2 3 1 1 2 4 1 1 2 2 1 NULL 1 5 NULL 1 1上面的代码如何加入到这段SQL程序呢?以选择绘制大于等于得记录。
--测试数据
declare @table table (A int,B int,C int)
insert into @table
select 1,2,null union all
select null,3,1 union all
select 1,1,1查询
select A,B,C,
(case ISNULL(A,0) when 0 then 0 else 1 end)+
(case ISNULL(B,0) when 0 then 0 else 1 end)+
(case ISNULL(C,0) when 0 then 0 else 1 end) as '汇总'
from @table
/*结果
A B C 汇总
----------- ----------- ----------- -----------
1 2 NULL 2
NULL 3 1 2
1 1 1 3
*/
--测试数据
declare @table table (A int,B int,C int)
insert into @table
select 1,2,null union all
select null,3,1 union all
select 1,1,1--查询
select A,B,C,
(case ISNULL(A,0) when 0 then 0 else 1 end)+
(case ISNULL(B,0) when 0 then 0 else 1 end)+
(case ISNULL(C,0) when 0 then 0 else 1 end) as '汇总'
from @table
/*结果
A B C 汇总
----------- ----------- ----------- -----------
1 2 NULL 2
NULL 3 1 2
1 1 1 3
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-09-24 23:15:11
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] int)
insert [tb]
select 1,2,null union all
select null,3,1 union all
select 1,1,1
--------------开始查询--------------------------select
a,b,c,
sum(case when a is null then 0 else 1 end)
+sum(case when b is null then 0 else 1 end)
+sum(case when c is null then 0 else 1 end) as num
from
[tb]
group by
a,b,c
----------------结果----------------------------
/* a b c num
----------- ----------- ----------- -----------
NULL 3 1 2
1 1 1 3
1 2 NULL 2(3 行受影响)
*/
case when a is null then 0 else 1 end +
case when b is null then 0 else 1 end +
case when c is null then 0 else 1 end
...
from tb
1
2
3
4B表
ID
1
3
4
5我用SQL求取交集:SELECT * FROM(
SELECT T.ID, SUM(CASE WHEN T.Flag='A' THEN 1 ELSE NULL END) AS A表,
SELECT T.ID, SUM(CASE WHEN T.Flag='B' THEN 1 ELSE NULL END) AS B表,
COUNT(T.Flag) AS 汇总
FROM(
SELECT A.*, 'A' AS Flag FROM A UNION ALL SELECT B.*, 'B' AS Flag FROM B) T GROUP BY T.ID) M
ORDER BY 汇总 DESC得出结果:
T A表 B表 汇总
1 1 1 2
3 1 1 2
4 1 1 2
2 1 NULL 1
5 NULL 1 1上面的代码如何加入到这段SQL程序呢?以选择绘制大于等于得记录。