sn lineno stockcode qty
100007 1 A123113 5
100007 2 A123113 22
100007 3 A123113 33
100007 4 A123113 4
100007 5 A123114 5444
100007 6 A123113 55
100007 7 A123114 5643
-->查询结果
100007 1 A123113 sum(qty)
100007 2 A123114 sum(qty)我想查到同一个sn里 相同料号的数量合计,怎样自动添加新的行号?
100007 1 A123113 5
100007 2 A123113 22
100007 3 A123113 33
100007 4 A123113 4
100007 5 A123114 5444
100007 6 A123113 55
100007 7 A123114 5643
-->查询结果
100007 1 A123113 sum(qty)
100007 2 A123114 sum(qty)我想查到同一个sn里 相同料号的数量合计,怎样自动添加新的行号?
解决方案 »
- sql server 2005上网很难下啊,是不是要买碟啊
- 请教高手MSSQL去空格问题?THK!
- 这样的数据库应该设计多个表还是在一个表?
- 在执行批量插入时,被插入的文本中含有乱码,为何SQL就终止执行了呢?
- 对Studen、Course和Sc表,按以下要求用T-SQL语句建立存储过程,并调用存储过程。
- SQLSERVER2005,2000同一主机DTC问题.
- SQL哪种字符串对重音的支持比较好?
- 這個insert語句怎麼寫
- 各位兄弟,帮帮忙啊!
- 2000S下有共享1600K,现想从另一98工作站上用共享的1600K,提示要在
- MSSQL 列处理问题.......
- XX-YY-123 形式的字段的排序查询,要按照最后的数字的大小排序,如何写?
lineno = (select count(1) from (select sn , stockcode , sum(qty) qty from tb group by sn , stockcode) n where n.sn < m.sn or (n.sn = m.sn and n.stockcode < m.stockcode)) + 1,
m.stockcode,
m.qty
from
(
select sn , stockcode , sum(qty) qty from tb group by sn , stockcode
) m
insert into tb values('100007' , 1 , 'A123113' , 5 )
insert into tb values('100007' , 2 , 'A123113' , 22)
insert into tb values('100007' , 3 , 'A123113' , 33)
insert into tb values('100007' , 4 , 'A123113' , 4)
insert into tb values('100007' , 5 , 'A123114' , 5444)
insert into tb values('100007' , 6 , 'A123113' , 55)
insert into tb values('100007' , 7 , 'A123114' , 5643)
goselect m.sn ,
[lineno] = (select count(1) from (select sn , stockcode , sum(qty) qty from tb group by sn , stockcode) n where n.sn < m.sn or (n.sn = m.sn and n.stockcode < m.stockcode)) + 1,
m.stockcode,
m.qty
from
(
select sn , stockcode , sum(qty) qty from tb group by sn , stockcode
) m
drop table tb/*
sn lineno stockcode qty
---------- ----------- ---------- -----------
100007 1 A123113 119
100007 2 A123114 11087(所影响的行数为 2 行)
*/
insert into tb values('100007' , 1 , 'A123113' , 5 )
insert into tb values('100007' , 2 , 'A123113' , 22)
insert into tb values('100007' , 3 , 'A123113' , 33)
insert into tb values('100007' , 4 , 'A123113' , 4)
insert into tb values('100007' , 5 , 'A123114' , 5444)
insert into tb values('100007' , 6 , 'A123113' , 55)
insert into tb values('100007' , 7 , 'A123114' , 5643)
goselect m.sn ,
[lineno] = row_number() over(order by sn , stockcode) ,
m.stockcode,
m.qty
from
(
select sn , stockcode , sum(qty) qty from tb group by sn , stockcode
) m
drop table tb/*
sn lineno stockcode qty
---------- -------------------- ---------- -----------
100007 1 A123113 119
100007 2 A123114 11087(2 行受影响)
*/
(
sn nvarchar(20),
[lineno] int,
stockcoce nvarchar(20),
qty int
)insert into tb_test
select '100007', 1 , 'A123113', 5
union all
select '100007', 2, 'A123113', 22
union all
select '100007', 3, 'A123113', 33
union all
select '100007', 4, 'A123113', 4
union all
select '100007', 5, 'A123114', 5444
union all
select '100007', 6, 'A123113', 55
union all
select '100007', 7, 'A123114', 5643 select * from tb_testselect max(sn) as sn,row_number() over (order by stockcoce) as [lineno] , stockcoce,sum(qty)as qty from tb_test group by stockcocesn lineno stockcoce qty
-------------------- -------------------- -------------------- -----------
100007 1 A123113 119
100007 2 A123114 11087(2 row(s) affected)
select m.sn ,
[lineno] = row_number() over(order by sn , stockcode) ,
m.stockcode,
sum(m.qty)
from
tb m
group by sn,stockcode
可以根据sn将行号分开吗?
有其它sn的时候,这个行号是整个sn自动增加的.1000007 1
1000007 21000008 1
1000008 2
create table tb(sn varchar(10), [lineno] int, stockcode varchar(10), qty int)
insert into tb values('100007' , 1 , 'A123113' , 5 )
insert into tb values('100007' , 2 , 'A123113' , 22)
insert into tb values('100007' , 3 , 'A123113' , 33)
insert into tb values('100008' , 4 , 'A123113' , 4)
insert into tb values('100007' , 5 , 'A123114' , 5444)
insert into tb values('100007' , 6 , 'A123113' , 55)
insert into tb values('100007' , 7 , 'A123114' , 5643)
goselect m.sn ,
[lineno] = (select count(1) from (select sn , stockcode , sum(qty) qty from tb group by sn , stockcode) n where n.sn = m.sn and n.stockcode < m.stockcode) + 1,
m.stockcode,
m.qty
from
(
select sn , stockcode , sum(qty) qty from tb group by sn , stockcode
) m
order by sn , [lineno]
drop table tb/*
sn lineno stockcode qty
---------- ----------- ---------- -----------
100007 1 A123113 115
100007 2 A123114 11087
100008 1 A123113 4(所影响的行数为 3 行)
*/
--sql 2005
create table tb(sn varchar(10), [lineno] int, stockcode varchar(10), qty int)
insert into tb values('100007' , 1 , 'A123113' , 5 )
insert into tb values('100007' , 2 , 'A123113' , 22)
insert into tb values('100007' , 3 , 'A123113' , 33)
insert into tb values('100008' , 4 , 'A123113' , 4)
insert into tb values('100007' , 5 , 'A123114' , 5444)
insert into tb values('100007' , 6 , 'A123113' , 55)
insert into tb values('100007' , 7 , 'A123114' , 5643)
goselect m.sn ,
[lineno] = row_number() over(partition by sn order by stockcode) ,
m.stockcode,
m.qty
from
(
select sn , stockcode , sum(qty) qty from tb group by sn , stockcode
) m
order by sn ,[lineno]
drop table tb/*
sn lineno stockcode qty
---------- -------------------- ---------- -----------
100007 1 A123113 115
100007 2 A123114 11087
100008 1 A123113 4(3 行受影响)
*/
go
--> -->
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([sn] int,[lineno] int,[stockcode] nvarchar(7),[qty] int)
Insert #T
select 100007,1,N'A123113',5 union all
select 100007,2,N'A123113',22 union all
select 100007,3,N'A123113',33 union all
select 100007,4,N'A123113',4 union all
select 100007,5,N'A123114',5444 union all
select 100007,6,N'A123113',55 union all
select 100007,7,N'A123114',5643
Go
Select [sn],row_number()over(partition by [sn] order by sum([qty]) )[lineno],[stockcode],sum([qty]) as [qty] from #T group by [sn],[stockcode]
sn lineno stockcode qty
----------- -------------------- --------- -----------
100007 1 A123113 119
100007 2 A123114 11087(2 個資料列受到影響)
if not object_id('tb') is null
drop table tb
Go
Create table tb([sn] int,[lineno] int,[stockcode] nvarchar(7),[qty] int)
Insert tb
select 100007,1,N'A123113',5 union all
select 100007,2,N'A123113',22 union all
select 100007,3,N'A123113',33 union all
select 100007,4,N'A123113',4 union all
select 100007,5,N'A123114',5444 union all
select 100007,6,N'A123113',55 union all
select 100007,7,N'A123114',5643 union all
select 100008,8,N'A123115',232 union all
select 100008,9,N'A123456',234
Go
Select * from tbselect m.sn ,
[lineno] = row_number() over(order by sn , stockcode) ,
m.stockcode,
sum(m.qty)
from
tb m
group by sn,stockcode
100007 1 A123113 119
100007 2 A123114 11087
100008 3 A123115 232
100008 4 A123456 234
该一下
select m.sn ,
[lineno] = row_number() over(partition by sn order by sn ) ,
m.stockcode,
sum(m.qty)
from
tb m
group by sn,stockcode100007 1 A123113 119
100007 2 A123114 11087
100008 1 A123115 232
100008 2 A123456 234
(select sn,stockcode,sum(qty) qty from tb group by sn,stockcode) a
select m.sn ,
[lineno] = row_number() over(partition by sn order by sn ) ,
m.stockcode,
sum(m.qty)
from
tb m
group by sn,stockcode
insert into tb values('100007' , 1 , 'A123113' , 5 )
insert into tb values('100007' , 2 , 'A123113' , 22)
insert into tb values('100007' , 3 , 'A123113' , 33)
insert into tb values('100007' , 4 , 'A123113' , 4)
insert into tb values('100007' , 5 , 'A123114' , 5444)
insert into tb values('100007' , 6 , 'A123113' , 55)
insert into tb values('100007' , 7 , 'A123114' , 5643)
go
select sn,stockcode,Sum(qty) as 总量 from tb group by sn,stockcodedrop table tb感觉比较简单啊,楼上的怎么这么复杂