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

2010-10-25 04:03:05 150 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Mac电脑本地安装Stable Diffusion小白教程避开所有坑教程如果出现黄字部分警告，如WARNING: The scripts pip, pip3 and pip3.9 are installed inz‘/usr/local/opt/python@3.9/Frameworks/Python.framework/Versions/3.9/bin’ which is not on PATH）需要将其添加到path。下载pip，输入：curl https://bootstrap.pypa.io/pip/get-pip.py -o get-pip.py。阅读详情

我有一个表:
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
---------------------------------------------------------------
想问一下各位高手这个语句怎么写，小弟感激不尽。

...全文

Anaconda报错no qt platform plugin could be initialized 今天因为在anaconda中安装了一个新包，安装成功后运行python 程序报错：no qt platform plugin could be initialized。 1.安装Anaconda3-4.2.0-Windows-x86_64，对应python版本为3.5.2，scikit learn版本是0.17。因为要用Isolation Forest算法，只有0.18以上版本scikit learn才包含此算法，所以想更新scikit learn。 conda install scikit-lear 阅读详情

darkroom

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

打赏
举报

个人感觉一条语句难呵呵期待下面高手.

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

相关推荐