一个查询问题

antsing_2008 2010-05-15 10:11:11

求解：
临时表A
流水ID
1
2
3
4
5
6
7
...
31
---------------------------------------------------
表B
班次天数
A01 5
A02 2
需要的结果：
ID 班次
1 A01
2 A01
3 A01
4 A01
5 A01
6 A02
7 A02
8 A01
...
--------------------------------
我本来是用 from A a,B b where a.ID<=天数
可是排到7就没了，呵呵，所以请高手解决，谢谢，等等等等。。。

...全文

126 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

htl258_Tony 2010-05-15

打赏
举报

--改一下通用性比较好

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2010-05-15 10:56:02

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog   : http://blog.csdn.net/htl258

----------------------------------------------------------------------------------



--> 生成测试数据表: [A]

IF OBJECT_ID('[A]') IS NOT NULL

	DROP TABLE [A]

GO

CREATE TABLE [A] ([ID] [int])

INSERT INTO [A]

SELECT '1' UNION ALL

SELECT '2' UNION ALL

SELECT '3' UNION ALL

SELECT '4' UNION ALL

SELECT '5' UNION ALL

SELECT '6' UNION ALL

SELECT '7' UNION ALL

SELECT '8' UNION ALL

SELECT '9' UNION ALL

SELECT '10' UNION ALL

SELECT '11' UNION ALL

SELECT '12' UNION ALL

SELECT '13' UNION ALL

SELECT '14' UNION ALL

SELECT '15' UNION ALL

SELECT '16' UNION ALL

SELECT '17' UNION ALL

SELECT '18' UNION ALL

SELECT '19' UNION ALL

SELECT '20' UNION ALL

SELECT '21' UNION ALL

SELECT '22' UNION ALL

SELECT '23' UNION ALL

SELECT '24' UNION ALL

SELECT '25' UNION ALL

SELECT '26' UNION ALL

SELECT '27' UNION ALL

SELECT '28' UNION ALL

SELECT '29' UNION ALL

SELECT '30' UNION ALL

SELECT '31'



--> 生成测试数据表: [B]

IF OBJECT_ID('[B]') IS NOT NULL

	DROP TABLE [B]

GO

CREATE TABLE [B] ([班次] [nvarchar](10),[天数] [int])

INSERT INTO [B]

SELECT 'A01','5' UNION ALL

SELECT 'A02','2' UNION ALL

--加入两条数据

SELECT 'A03','4'



--SELECT * FROM [A]

--SELECT * FROM [B]



-->SQL查询如下:

select a.id,b.班次

from a

    left join(

        select 班次,天数,dt=(select sum(天数) from b where 班次<=t.班次)

        from b t

        ) b

        on (a.id-1)%(select sum(天数) from b) between dt-天数 and dt-1

/*

id          班次

----------- ----------

1           A01

2           A01

3           A01

4           A01

5           A01

6           A02

7           A02

8           A03

9           A03

10          A03

11          A03

12          A01

13          A01

14          A01

15          A01

16          A01

17          A02

18          A02

19          A03

20          A03

21          A03

22          A03

23          A01

24          A01

25          A01

26          A01

27          A01

28          A02

29          A02

30          A03

31          A03



(31 行受影响)

*/

zhangsong841 2010-05-15

打赏
举报

select t.* , case when 流水ID%7 between 1 and 5 then 'A01'
else 'A02'
end as 班次

from 临时表A t

njlywy 2010-05-15

打赏
举报

顶楼上的…

htl258_Tony 2010-05-15

打赏
举报

----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2010-05-15 10:56:02

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

-- Blog   : http://blog.csdn.net/htl258

----------------------------------------------------------------------------------



--> 生成测试数据表: [A]

IF OBJECT_ID('[A]') IS NOT NULL

	DROP TABLE [A]

GO

CREATE TABLE [A] ([ID] [int])

INSERT INTO [A]

SELECT '1' UNION ALL

SELECT '2' UNION ALL

SELECT '3' UNION ALL

SELECT '4' UNION ALL

SELECT '5' UNION ALL

SELECT '6' UNION ALL

SELECT '7' UNION ALL

SELECT '8' UNION ALL

SELECT '9' UNION ALL

SELECT '10' UNION ALL

SELECT '11' UNION ALL

SELECT '12' UNION ALL

SELECT '13' UNION ALL

SELECT '14' UNION ALL

SELECT '15' UNION ALL

SELECT '16' UNION ALL

SELECT '17' UNION ALL

SELECT '18' UNION ALL

SELECT '19' UNION ALL

SELECT '20' UNION ALL

SELECT '21' UNION ALL

SELECT '22' UNION ALL

SELECT '23' UNION ALL

SELECT '24' UNION ALL

SELECT '25' UNION ALL

SELECT '26' UNION ALL

SELECT '27' UNION ALL

SELECT '28' UNION ALL

SELECT '29' UNION ALL

SELECT '30' UNION ALL

SELECT '31'



--> 生成测试数据表: [B]

IF OBJECT_ID('[B]') IS NOT NULL

	DROP TABLE [B]

GO

CREATE TABLE [B] ([班次] [nvarchar](10),[天数] [int])

INSERT INTO [B]

SELECT 'A01','5' UNION ALL

SELECT 'A02','2'



--SELECT * FROM [A]

--SELECT * FROM [B]



-->SQL查询如下:

select a.id,b.班次

from a

	left join(

		select 班次,

			bt=(select sum(天数) from b where 班次<=t.班次)-天数,

			et=(select sum(天数) from b where 班次<=t.班次)-1 

		from b t

		) b

		on (a.id+6)%7 between bt and et

/*

id          班次

----------- ----------

1           A01

2           A01

3           A01

4           A01

5           A01

6           A02

7           A02

8           A01

9           A01

10          A01

11          A01

12          A01

13          A02

14          A02

15          A01

16          A01

17          A01

18          A01

19          A01

20          A02

21          A02

22          A01

23          A01

24          A01

25          A01

26          A01

27          A02

28          A02

29          A01

30          A01

31          A01



(31 行受影响)



*/

htl258_Tony 2010-05-15

打赏
举报

[Quote=引用 4 楼 antsing_2008 的回复:]
都不对啊，可能我没说清楚啦，结果集中应该要以A表为准，也就是有个潜在的循环
1 A01
2 A01
3 A01
4 A01
5 A01
6 A02
7 A02
继续往下重排B表
8 A01
...
一直到A表的最大序号
[/Quote]＝＝

antsing_2008 2010-05-15

打赏
举报

都不对啊，可能我没说清楚啦，结果集中应该要以A表为准，也就是有个潜在的循环
1 A01
2 A01
3 A01
4 A01
5 A01
6 A02
7 A02
继续往下重排B表
8 A01
...
一直到A表的最大序号

zhengduan964532 2010-05-15

打赏
举报



IF object_id('tb')IS NOT NULL DROP TABLE tb 

CREATE TABLE tb(bc VARCHAR(10),day int)

INSERT INTO tb 

SELECT   'A01' ,5 UNION ALL 

SELECT    'A02', 2 





SELECT number,bc FROM 

master..spt_values a,tb WHERE  type='p' AND number>0 AND number<32

AND number<=tb.day