表TBnumber里有个字段num记录了N行不重复的数值
表TBdata里面有某几个字段c1,c2,c3,c4分别记录了TBnumber表num字段里某个值例如TBnumber
num count
1 x
2 x
3 x
4 x
5 x
6 x
7 xTBnumber
c1 c2 c3 c4
1 2 3 7
3 4 3 7
1 4 1 6如何统计TBnumber表这几个字段,某个数值出现的次数之和,然后更新进去TBnumber表的count字段中?
表TBdata里面有某几个字段c1,c2,c3,c4分别记录了TBnumber表num字段里某个值例如TBnumber
num count
1 x
2 x
3 x
4 x
5 x
6 x
7 xTBnumber
c1 c2 c3 c4
1 2 3 7
3 4 3 7
1 4 1 6如何统计TBnumber表这几个字段,某个数值出现的次数之和,然后更新进去TBnumber表的count字段中?
解决方案 »
- 求一方法
- 不允许从数据类型 nvarchar(max) 到 varbinary(max) 的隐式转换。。。。
- 一个看似简单的MONEY型转换为字符串的问题请大家讨论
- 各位:SQL SERVER 2000 升级到SP4 会引起什么问题吗?
- 求教如何用report加for语句打印指定的记录?
- 删除了多个表,只有几个表中的数据能恢复,为什么
- 这个算不算SQL的一个BUG?
- 在字段中取出数字
- 求一SQL语句
- 将sql一张表转成二维表,执行报错,说是有语法问题!!!
- 不显示删除回复显示所有回复显示星级回复显示得分回复 SQL 数据库 所在 服务器 CPU 100%[问题点数:40分]
- 关于sql2005 客户端访问服务器失败,用windows登录共享后即可
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-05-08 10:57:24
-- 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)
--
----------------------------------------------------------------
--> 测试数据:[ta]
if object_id('[ta]') is not null drop table [ta]
go
create table [ta]([num] int,[count] varchar(1))
insert [ta]
select 1,'x' union all
select 2,'x' union all
select 3,'x' union all
select 4,'x' union all
select 5,'x' union all
select 6,'x' union all
select 7,'x'
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([c1] int,[c2] int,[c3] int,[c4] int)
insert [tb]
select 1,2,3,7 union all
select 3,4,3,7 union all
select 1,4,1,6
--------------开始查询--------------------------
update
a
set
a.[count]=b.[count]
from
ta a
left join
(
select
num,count(1) as [count]
from
(select c1 as num from tb
union all
select c2 as num from tb
union all
select c3 as num from tb
union all
select c4 as num from tb)t
group by
num
)b
on
a.num=b.num
select * from ta
----------------结果----------------------------
/* num count
----------- -----
1 3
2 1
3 3
4 2
5 NULL
6 1
7 2(7 行受影响)*/
if object_id('[TBnumber]') is not null drop table [TBnumber]
go
create table [TBnumber]([num] int,[count] int)
insert [TBnumber]
select 1,null union all
select 2,null union all
select 3,null union all
select 4,null union all
select 5,null union all
select 6,null union all
select 7,null
if object_id('[TBdata]') is not null drop table [TBdata]
go
create table [TBdata]([c1] int,[c2] int,[c3] int,[c4] int)
insert [TBdata]
select 1,2,3,7 union all
select 3,4,3,7 union all
select 1,4,1,6
---更新---
update a
set [count]=isnull(b.cnt,0)
from TBnumber a
left join
(
select c,count(1) as cnt
from
(select c1 as c from tbdata
union all select c2 from tbdata
union all select c3 from tbdata
union all select c4 from tbdata
) t
group by c
) b
on a.num=b.c ---查询---
select * from TBnumber---结果---
num count
----------- -----------
1 3
2 1
3 3
4 2
5 0
6 1
7 2(7 行受影响)
update [TBnumber] set
[count]=(select count(1) from TBdata unpivot(v for c in(c1,c2,c3,c4)) b where v=[TBnumber].num)
select * from [TBnumber]
/*
num count
----------- -----------
1 3
2 1
3 3
4 2
5 0
6 1
7 2(7 行受影响)
*/
set count = up.count
from (select n.num,count(d.num) count
from TBnumber n join ( select c1 num from TBdata union all
select c2 from TBdata union all
select c3 from TBdata union all
select c4 from TBdata ) d on n.num=d.num
group by n.num ) up
where TBnumber.num= up.num