insert into t2 select * from t1update a
set
r=(select count(1) from t2 where s=a.s and t<=a.t)
from
t2 a
set
r=(select count(1) from t2 where s=a.s and t<=a.t)
from
t2 a
解决方案 »
- 请教一个sql查询语句的写法
- 如何在客户端判断SQL中用户的角色
- 删除二表中相同的数据。急!
- 建表是能否给列添加描述性语言
- 请问,我还想在select这边加一个字段如test_no,这个字段也是这个表里的,但是报错,请问还有什么方法吗?
- 使用CSDN的邹建《SQL Server导出表到EXCEL文件的存储过程》出现奇怪问题,在线急等!!!
- sqlserver2008远程数据库连接错误
- CSDN中可用分每天自动加10分在SQL SERVER中是怎么实现的?
- 关于向SQL写入XML的问题
- 查询数据库时,如果查到相同的几条如何只取一条(邹老大帮帮忙)
- 各位帮帮忙,关于触发器的问题谢谢了(我的分都给你们了只求你们回答我)
- 再问 求SQL语句,疑难! 数据更新
insert into @t1 select 1,1,'a'
insert into @t1 select 1,2,'b'
insert into @t1 select 1,3,'c'
insert into @t1 select 1,4,'d'
insert into @t1 select 1,5,'e'
insert into @t1 select 1,6,'f'
declare @t2 table(s int,r int,t varchar(4))
insert into @t2 select 1,2,'g'
insert into @t2 select 1,4,'h'insert into @t2 select * from @t1update a
set
r=(select count(1) from @t2 where s=a.s and t<=a.t)
from
@t2 aselect * from @t2 order by s,r/*
s r t
----------- ----------- ----
1 1 a
1 2 b
1 3 c
1 4 d
1 5 e
1 6 f
1 7 g
1 8 h
*/
--更新T2的r=r*10+1
--追加T2到T2
--重新生成T1的r
--更新T1的r=r*10
--更新T2的r=r*10+1
--追加T2到T1
--重新生成T1的r
A.finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)] ,
D.fcheckdate as [实际回款日期 (Date of cash)]
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3
WHERE
ISNULL(D.FcheckamountFor,0)=0
GROUP BY
A.FInterID,
A.Fbillno,
A.Fdate,
A.FHeadselfi0453,
A.FHeadselfi0464,
C.Fname,
D.FcheckamountFor,
D.fcheckdateUnion AllSelect
Null,
Null,
[发票号码 (Inv. No)],
Null,
Sum([ 货物净值 (Net value)]) As [ 货物净值 (Net value)],
Sum([税 额 (VAT)]) AS [税 额 (VAT)],
Null,
Sum([ 价税合计 (Total Amount)]) as [ 价税合计 (Total Amount)],
Null,
Null,
Null
From
(
select
A.finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)] ,
D.fcheckdate as [实际回款日期 (Date of cash)]
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3
WHERE
ISNULL(D.FcheckamountFor,0)=0
GROUP BY
A.FInterID,
A.Fbillno,
A.Fdate,
A.FHeadselfi0453,
A.FHeadselfi0464,
C.Fname,
D.FcheckamountFor,
D.fcheckdate
) A
Group By
[发票号码 (Inv. No)]
(s Int,
r Int,
t Varchar(10))
Insert t1 Select 1, 1, 'a'
Union All Select 1, 2, 'b'
Union All Select 1, 3, 'c'
Union All Select 1, 4, 'd'
Union All Select 1, 5, 'e'
Union All Select 1, 6, 'f'Create Table t2
(s Int,
r Int,
t Varchar(10))
Insert t2 Select 1, 2, 'g'
Union All Select 1, 4, 'h'
GO
Select *, 1 As Flag Into #T1 From T1
Insert #T1 Select *, 2 From T2Select ID = Identity(Int, 1, 1), * Into #T2 From #T1 Order By r, Flag DescDelete From T2Insert T2 Select s, ID, t From #T2Select * From T2Drop Table #T1, #T2
GO
Drop Table T1, T2
/*
s r t
1 1 a
1 2 g
1 3 b
1 4 c
1 5 h
1 6 d
1 7 e
1 8 f
*/
(s Int,
r Int,
t Varchar(10))
Insert t1 Select 1, 1, 'a'
Union All Select 1, 2, 'b'
Union All Select 1, 3, 'c'
Union All Select 1, 4, 'd'
Union All Select 1, 5, 'e'
Union All Select 1, 6, 'f'Create Table t2
(s Int,
r Int,
t Varchar(10))
Insert t2 Select 1, 2, 'g'
Union All Select 1, 4, 'h'
GO
Select ID = Identity(Int, 1, 1), * Into #T From
(Select *, 1 As Flag From T1
Union All
Select *, 2 From T2) A
Order By r, Flag DescDelete From T2Insert T2 Select s, ID, t From #T Order By IDSelect * From T2Drop Table #T
GO
Drop Table T1, T2
/*
s r t
1 1 a
1 2 g
1 3 b
1 4 c
1 5 h
1 6 d
1 7 e
1 8 f
*/
drop table t1
go
create table t1(s varchar(10),r int,t varchar(10))
insert into t1(s,r,t) values('1', 1, 'a')
insert into t1(s,r,t) values('1', 2, 'b')
insert into t1(s,r,t) values('1', 3, 'c')
insert into t1(s,r,t) values('1', 4, 'd')
insert into t1(s,r,t) values('1', 5, 'e')
insert into t1(s,r,t) values('1', 6, 'f')
insert into t1(s,r,t) values('2', 1, 'x')
insert into t1(s,r,t) values('2', 2, 'y')
insert into t1(s,r,t) values('2', 3, 'z')
goif object_id('pubs..t2') is not null
drop table t2
go
create table t2(s varchar(10),r int,t varchar(10))
insert into t2(s,r,t) values('1', 2, 'g')
insert into t2(s,r,t) values('1', 2, 'l')
insert into t2(s,r,t) values('1', 4, 'h')
insert into t2(s,r,t) values('2', 2, 'k')
goselect px = identity(int,1,1) , s , r , t into test from
(
select * , id = 2 from t1
union all
select * , id = 1 from t2
) t
order by s , r , id delete from t2insert t2(s,r,t)
select s , r = (select count(1) from test where s = n.s and px < n.px)+1 , t from test n order by s , r select * from t2drop table t1,t2 , test/*
s r t
---------- ----------- ----------
1 1 a
1 2 g
1 3 l
1 4 b
1 5 c
1 6 h
1 7 d
1 8 e
1 9 f
2 1 x
2 2 k
2 3 y
2 4 z(所影响的行数为 13 行)*/