sql查询

Question

比如表字段
id     ticketNo（起始票号）      orderNum（购买张数）      
1      10000                  1
1      10010                  2
1      10100                  3
1      11000                  4
1      20000                  5
..........
..........我查询出来的结果，如果张数是1则增加一个查询列票号10000
如果张数是2，则查询的列为10011,10012
以此类推

Answer 1

declare @T table([id] int,[ticketNo] int,[orderNum] int)
insert @T
select 1,10000,1 union all
select 1,10010,2 union all
select 1,10100,3 union all
select 1,11000,4 union all
select 1,20000,5select 
a.id,a.ticketNo+b.number AS ticketNo,a.orderNum 
from @T a 
LEFT JOIN master..spt_values b ON a.orderNum>=b.number
WHERE b.type='p'/*
id          ticketNo    orderNum
----------- ----------- -----------
1           10000       1
1           10001       1
1           10010       2
1           10011       2
1           10012       2
1           10100       3
1           10101       3
1           10102       3
1           10103       3
1           11000       4
1           11001       4
1           11002       4
1           11003       4
1           11004       4
1           20000       5
1           20001       5
1           20002       5
1           20003       5
1           20004       5
1           20005       5
*/

Answer 2

select a.id,a.ticketNo+b.number AS ticketNo,a.orderNum 
from tb a 
LEFT JOIN master..spt_values b 
ON a.orderNum>=b.number
WHERE b.type='p

Answer 3

没说清楚，我需要的查询结果是这样的
id     ticketNo（起始票号）      orderNum（购买张数）     ticket  
1      10000                  1                           10000
1      10010                  2                           10011,10012
1      10100                  3                           10101,10102,10103
1      11000                  4                           11001,11002,11003,11004
1      20000                  5                           20001,20002,20003,20004,20005

Answer 4

ticket  为增加的查询列

Answer 5

create table #T([id] int,[ticketNo] int,[orderNum] int)
insert #T
select 1,10000,1 union all
select 1,10010,2 union all
select 1,10100,3 union all
select 1,11000,4 union all
select 1,20000,5;with t as
(
select 
a.id,a.ticketNo,a.orderNum,a.ticketNo+b.number AS ticket
from #T a 
LEFT JOIN master..spt_values b ON a.orderNum>=b.number
WHERE b.type='p'
)
select id,ticketNo,orderNum
,ticket=stuff((select ','+convert(varchar(20),ticket) from t b where a.id=b.id and a.ticketNo=b.ticketNo and a.orderNum=b.orderNum for xml path('')),1,1,'')
from t a
在叶子的基础上改的

Answer 6

这个查询出来的是
id ticketNo orderNum ticket
1 10000 1 10000,10001
1 10000 1 10000,10001
1 10010 2 10010,10011,10012
1 10010 2 10010,10011,10012
1 10010 2 10010,10011,10012
1 10100 3 10100,10101,10102,10103
1 10100 3 10100,10101,10102,10103
1 10100 3 10100,10101,10102,10103
1 10100 3 10100,10101,10102,10103
1 11000 4 11000,11001,11002,11003,11004
1 11000 4 11000,11001,11002,11003,11004
1 11000 4 11000,11001,11002,11003,11004
1 11000 4 11000,11001,11002,11003,11004
1 11000 4 11000,11001,11002,11003,11004
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
我需要返回的结果集是
id ticketNo orderNum ticket
1      10000                  1                           10000
1      10010                  2                           10011,10012
1      10100                  3                           10101,10102,10103
1      11000                  4                           11001,11002,11003,11004
1      20000                  5                           20001,20002,20003,20004,20005

Answer 7

create table #T([id] int,[ticketNo] int,[orderNum] int)
insert #T
select 1,10000,1 union all
select 2,10010,2 union all
select 3,10100,3 union all
select 4,11000,4 union all
select 5,20000,5;with t as
(
select 
a.id,a.ticketNo,a.orderNum,a.ticketNo+b.number AS ticket
from #T a 
LEFT JOIN master..spt_values b ON a.orderNum>=b.number
WHERE b.type='p'
)
select id,ticketNo,orderNum
,ticket=stuff((select ','+convert(varchar(20),ticket) from t b where a.id=b.id and a.ticketNo=b.ticketNo and a.orderNum=b.orderNum for xml path('')),1,1,'')
from t a[/code]
在叶子的基础上改的这个查询出来的是
id ticketNo orderNum ticket
1 10000 1 10000,10001
1 10000 1 10000,10001
2 10010 2 10010,10011,10012
2 10010 2 10010,10011,10012
2 10010 2 10010,10011,10012
3 10100 3 10100,10101,10102,10103
3 10100 3 10100,10101,10102,10103
3 10100 3 10100,10101,10102,10103
3 10100 3 10100,10101,10102,10103
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
5 20000 5 20000,20001,20002,20003,20004,20005
5 20000 5 20000,20001,20002,20003,20004,20005
5 20000 5 20000,20001,20002,20003,20004,20005
5 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
我需要返回的结果集是
id ticketNo orderNum ticket
1      10000                  1                           10000
2      10010                  2                           10010,10011
3      10100                  3                           10100,10101,10102
4      11000                  4                           11000,11001,11002,11003
5      20000                  5                           20000,20001,20002,20003,20004

Answer 8

这个查询出来的是
id ticketNo orderNum ticket
1 10000 1 10000,10001
1 10000 1 10000,10001
2 10010 2 10010,10011,10012
2 10010 2 10010,10011,10012
2 10010 2 10010,10011,10012
3 10100 3 10100,10101,10102,10103
3 10100 3 10100,10101,10102,10103
3 10100 3 10100,10101,10102,10103
3 10100 3 10100,10101,10102,10103
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
4 11000 4 11000,11001,11002,11003,11004
5 20000 5 20000,20001,20002,20003,20004,20005
5 20000 5 20000,20001,20002,20003,20004,20005
5 20000 5 20000,20001,20002,20003,20004,20005
5 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
1 20000 5 20000,20001,20002,20003,20004,20005
我需要返回的结果集是
id ticketNo orderNum ticket
1      10000                  1                           10000
2      10010                  2                           10010,10011
3      10100                  3                           10100,10101,10102
4      11000                  4                           11000,11001,11002,11003
5      20000                  5                           20000,20001,20002,20003,20004create table #T([id] int,[ticketNo] int,[orderNum] int)
insert #T
select 1,10000,1 union all
select 1,10010,2 union all
select 1,10100,3 union all
select 1,11000,4 union all
select 1,20000,5;with t as
(
select 
a.id,a.ticketNo,a.orderNum,a.ticketNo+b.number AS ticket
from #T a 
LEFT JOIN master..spt_values b ON a.orderNum>=b.number
WHERE b.type='p'
)
select id,ticketNo,orderNum
,ticket=stuff((select ','+convert(varchar(20),ticket) from t b where a.id=b.id and a.ticketNo=b.ticketNo and a.orderNum=b.orderNum for xml path('')),1,1,'')
from t a
group by id,ticketNo,orderNum
忘了加最后一句了