tb:
aa bb cc
---------------------
abc ccc 3
eee ddd 4
eee ffg 6tb2:
aa bb
-----------
qqq aaa
ccc eef
ffdd edf
当tb2的某些记录插入到tb时,希望cc字段要自动以最大值加1:tb:
aa bb cc
---------------------
abc ccc 3
eee ddd 4
eee ffg 6
qqq aaa 7
ccc eef 8
ffdd edf 9请问SQL语句如何写:insert into tb(aa,bb,cc) select aa,bb,'?' from tb2 SQLinsert
aa bb cc
---------------------
abc ccc 3
eee ddd 4
eee ffg 6tb2:
aa bb
-----------
qqq aaa
ccc eef
ffdd edf
当tb2的某些记录插入到tb时,希望cc字段要自动以最大值加1:tb:
aa bb cc
---------------------
abc ccc 3
eee ddd 4
eee ffg 6
qqq aaa 7
ccc eef 8
ffdd edf 9请问SQL语句如何写:insert into tb(aa,bb,cc) select aa,bb,'?' from tb2 SQLinsert
解决方案 »
- 各位好,请教一个关于ASP。NET(c#)中的datagrid1_ItemDataBound中的绑定问题
- 一个关于统计报表的SQL写法,有点急,今天加班,解决后再加100分.
- 问一个很菜的问题,请问SQL的check约束里能否区分大小写?
- 关于触发器的问题,急,在线等.
- 要买一套正版sql server2000,给个建议!好困惑。
- SQLServer2005导出表到Oracle10g,提示导出成功,但是oracle查没有数据
- sql server ce支持存储过程吗?
- TONY哥,再请教高度难动态多版本BOM成本计算
- 这么简单的存储过程怎么错了呢?
- 急!我遇到一个oracle权限问题
- 帮我看一下我的SQL测验成绩,全对么?
- 求一个sql
select aa,
bb,
(select max(cc) from tb)+row_number() over(order by getdate())
from tb2
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-06 19:43:02
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([aa] varchar(3),[bb] varchar(3),[cc] int)
insert [tb]
select 'abc','ccc',3 union all
select 'eee','ddd',4 union all
select 'eee','ffg',6--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([aa] varchar(4),[bb] varchar(3))
insert [tb2]
select 'qqq','aaa' union all
select 'ccc','eef' union all
select 'ffdd','edf'
--------------开始查询--------------------------
--------------开始查询----------------------------select * from [tb]; WITH cte AS (
select *,(SELECT MAX(cc) FROM TB )+1 cc, ROW_NUMBER() OVER(ORDER BY GETDATE() )id
from [tb2]
),
cte2 AS (
SELECT * FROM cte
WHERE id=1
UNION ALL
SELECT b.aa,b.bb,a.cc+1 ,b.id
FROM cte2 a INNER JOIN cte b ON a.id=b.id-1
)
SELECT aa,bb,cc
FROM cte2
UNION ALL
SELECT *
FROM TB
ORDER BY cc
----------------结果----------------------------
/*
aa bb cc
---- ---- -----------
abc ccc 3
eee ddd 4
eee ffg 6
qqq aaa 7
ccc eef 8
ffdd edf 9
*/
(aa varchar(10), bb varchar(10), cc int)insert into tb
select 'abc', 'ccc', 3 union all
select 'eee', 'ddd', 4 union all
select 'eee', 'ffg', 6create table tb2
(aa varchar(10), bb varchar(10))insert into tb2
select 'qqq', 'aaa' union all
select 'ccc', 'eef' union all
select 'ffdd', 'edf'
select aa,
bb,
identity(int,1,1) 'cc'
into #tb2
from tb2 insert into tb(aa,bb,cc)
select aa,bb,
(select max(cc) from tb)+cc
from #tb2-- 结果
select aa,bb,cc from tb
/*
aa bb cc
---------- ---------- -----------
abc ccc 3
eee ddd 4
eee ffg 6
qqq aaa 7
ccc eef 8
ffdd edf 9(6 row(s) affected)
*/
insert into tb(aa,bb,cc)
select aa,
bb,
(select max(cc) from tb)+row_number() over(order by getdate())
from tb2
唐诗三百首这个如何解决..............