小弟求一个关于更新的sql语句

darkroom 2010-10-25 04:03:05
我有一个表:
Table A
--------------------------------
Tag Count
--------------------------------
a 0
b 0
----------------------------
还有一个表:

Table B
--------------------------------
Name Tag
-------------------------------
1 a
2 b
3 a
4 c
----------------------------------
我想更新表A的count字段,使值为在表B里出现的次数,如果该tag未出现,则新增一条数据,对于上面的运行结果应该是
Table A
------------------------------------
Tag Count
--------------------------------
a 2
b 1
c 1
---------------------------------------------------------------
想问一下各位高手这个语句怎么写,小弟感激不尽。

...全文
66 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
songguozhi 2010-10-25
IF OBJECT_ID('A') IS NOT NULL 
DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GO
CREATE TABLE A(Tag VARCHAR(32), count INT)
CREATE TABLE B(NAME VARCHAR(32), Tag VARCHAR(32));

INSERT INTO A
SELECT 'a', 0 UNION ALL
SELECT 'b', 0

INSERT INTO B
SELECT '1', 'a' UNION ALL
SELECT '2', 'b' UNION ALL
SELECT '3', 'a' UNION ALL
SELECT '4', 'c'
go
IF OBJECT_ID('sp_UpdateOrInsertTagCount') IS NOT NULL
DROP PROCEDURE sp_UpdateOrInsertTagCount
go
CREATE PROCEDURE sp_UpdateOrInsertTagCount
AS
BEGIN
UPDATE A SET [COUNT] = (SELECT COUNT(1) FROM B WHERE B.Tag = A.Tag)
INSERT INTO A(Tag, [count])
SELECT tag , count(1)
FROM B
WHERE Tag not in (SELECT Tag FROM A)
GROUP BY Tag

SELECT * FROM A
END
go
EXEC sp_UpdateOrInsertTagCount
回复
darkroom 2010-10-25
问题已解决,谢谢大家
回复
fpzgm 2010-10-25

create table A(Tag varchar(10), [Count] int)
insert into A values('a', 0)
insert into A values('b', 0)
create table B(Name int,Tag varchar(10))
insert into B values(1 ,'a')
insert into B values(2 ,'b')
insert into B values(3 ,'a')
insert into B values(4 ,'c')
go
--方法一
update A set [count] = (select count(*) from B where tag = A.tag)
insert into A
select tag , count(*) from B where tag not in (select tag from A) group by tag

--方法二
delete from A where tag in (select tag from B)
insert into A
select tag , count(*) from B group by tag

/*
Tag Count
---------- -----------
a 2
b 1
c 1

(所影响的行数为 3 行)
*/


回复
fpzgm 2010-10-25

--方法一
update A set [count] = (select count(*) from B where tag = A.tag)
insert into A
select tag , count(*) from B where tag not in (select tag from A) group by tag

--方法二
delete from A where tag in (select tag from B)
insert into A
select tag , count(*) from B group by tag

回复
dawugui 2010-10-25
create table a(Tag varchar(10), [Count] int)
insert into a values('a', 0)
insert into a values('b', 0)
create table B(Name int,Tag varchar(10))
insert into b values(1 ,'a')
insert into b values(2 ,'b')
insert into b values(3 ,'a')
insert into b values(4 ,'c')
go

update a set [count] = (select count(1) from b where b.tag = a.tag)
insert into a select tag , count(1) from b where tag not in (select tag from a) group by tag

select * from a

drop table a , b

/*
Tag Count
---------- -----------
a 2
b 1
c 1

(所影响的行数为 3 行)
*/
回复
dawugui 2010-10-25
--需要两条语句.

update a set [count] = (select count(1) from b where b.tag = a.tag)
insert into a select tag , count(1) from b where tag not in (select tag from a) group by tag
回复
王向飞 2010-10-25
分两步 
第一步 delete from A
第二部

insert into a (tag,count)
select tag,count(1) from b group by tag
回复
天下如山 2010-10-25
个人感觉一条语句难 呵呵 期待下面高手.
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-25 04:03
社区公告
暂无公告