求一条select 语句

[zengzhimin1980]

表A：

name code1 code2 code3
==============================
张三 2051 2051 null
李四 null 2051 2066
里斯本 2051 2000 2052
昌盛 2051 2067 null
蝇头 2501 2051 2051
..........................


如何统计2051出现的次数

[黄_瓜]

select sum(case code1  when then 1 else 0 end )
      +sum(case code2  when then 1 else 0 end )
      +sum(case code2  when then 1 else 0 end )
from tb

[--小F--]

select code1,conut(1) as 出现次数  from a  where code1='2051' group by code1

[华夏小卒]

if object_id('TB')is not null drop table TB
go 
create table TB(name varchar(10) ,code1 int, code2 int, code3  INT)
insert into TB  SELECT 
'张三',    2051  ,2051,    null UNION ALL SELECT
'李四' ,   null  ,2051 ,   2066 UNION ALL SELECT
'里斯本',  2051  ,2000  ,  2052 UNION ALL SELECT
'昌盛'   , 2051 , 2067   , null UNION ALL SELECT
'蝇头'    ,2501,  2051    ,2051 


select count(*) from(
select code1 as code from tb 
union all 
select code2 from tb
union all 
select code3 from tb 
) t where code=2051

-----------
7

(1 行受影响)

[黄_瓜]

[Quote=引用 1 楼 beirut 的回复:]
SQL codeselectsum(case code1whenthen1else0end )+sum(case code2whenthen1else0end )+sum(case code2whenthen1else0end )from tb
[/Quote]
修改

--> 测试数据：@tb
declare @tb table([name] varchar(6),[code1] int,[code2] int,[code3] int)
insert @tb
select '张三',2051,2051,null union all
select '李四',null,2051,2066 union all
select '里斯本',2051,2000,2052 union all
select '昌盛',2051,2067,null union all
select '蝇头',2501,2051,2051

select sum(case code1  when 2051 then 1 else 0 end )
      +sum(case code2  when 2051 then 1 else 0 end )
      +sum(case code3  when 2051 then 1 else 0 end )
  as 2051出现的次数
from @tb
/*

2051出现的次数
-----------
7

(1 行受影响)


*/

[soft_wsx]

select sum(case when code1='2051' then 1 else 0 end )
      +sum(case when code2='2051' then 1 else 0 end )
      +sum(case when code3='2051' then 1 else 0 end )
from tb

[zhaoyh0530]

select sum(case when code1 = '2051' then 1 else 0 end )
      +sum(case when code2 = '2051' then 1 else 0 end )
      +sum(case when code3 = '2051' then 1 else 0 end )
  as 2051出现的次数
from tb

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-09-10 14:21:18
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([name] varchar(6),[code1] int,[code2] int,[code3] int)
insert [tb]
select '张三',2051,2051,null union all
select '李四',null,2051,2066 union all
select '里斯本',2051,2000,2052 union all
select '昌盛',2051,2067,null union all
select '蝇头',2501,2051,2051
--------------开始查询--------------------------
select 
  sum(出现次数) as 出现次数
from
(select code1,count(1) as 出现次数  from tb  where code1='2051' group by code1
union all
select code2 as code1 ,count(1) as 出现次数  from tb  where code2='2051' group by code2
union all
select code3 as code1 ,count(1) as 出现次数  from tb  where code3='2051' group by code3)t
----------------结果----------------------------
/* 出现次数
-----------
7

(1 行受影响)

*/

[黄_瓜]

[Quote=引用 6 楼 zhaoyh0530 的回复:]
SQL codeselectsum(casewhen code1='2051'then1else0end )+sum(casewhen code2='2051'then1else0end )+sum(casewhen code3='2051'then1else0end )as 2051出现的次数from tb
[/Quote]
路MM

[htl258_Tony]

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] nvarchar(3),[code1] int,[code2] int,[code3] int)
INSERT [tb]
SELECT N'张三','2051','2051',null UNION ALL
SELECT N'李四',null,'2051','2066' UNION ALL
SELECT N'里斯本','2051','2000','2052' UNION ALL
SELECT N'昌盛','2051','2067',null UNION ALL
SELECT N'蝇头','2501','2051','2051'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
SELECT COUNT(*) AS [2051cnt] FROM tb UNPIVOT(code FOR type IN(code1,code2,code3))t WHERE code=2051
/*
2051cnt
-----------
7

(1 行受影响)
*/

[lihan6415151528]

select sum(case when code1 = '2051' then 1 else 0 end )
+sum(case when code2 = '2051' then 1 else 0 end )
+sum(case when code3 = '2051' then 1 else 0 end )
as 2051出现的次数
from tb