一个查询问题

2010-05-15 10:11:11 236 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

antsing_2008 墨衍会员 · AI 创作全网分发关注 + 加入社区

芯片设计新手必看：5分钟搞懂工艺角（Process Corner）到底是什么，为啥仿真总要用它？本文详细解析了芯片设计中的工艺角（Process Corner）概念及其在仿真中的重要性。通过类比马拉松运动员的性能差异，解释了晶体管制造过程中的天然波动，并介绍了TT/FF/SS等工艺角代码的含义。文章还探讨了为何需要跑全工艺角仿真，以及现代芯片验证的完整流程，包括PVT矩阵和多模式分析（MMMC），帮助芯片设计新手快速掌握这一关键概念。阅读详情

求解：
临时表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就没了，呵呵，所以请高手解决，谢谢，等等等等。。。

...全文

从零开始利用MATLAB进行FPGA设计（六）用ADC采集信号教程1 用AN9238采集信号的方法（1），包含PLL、FIFO、ILA的基础教程。阅读详情

antsing_2008

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

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

永生天地 2010-05-15

打赏
举报

from A a,B b on a.ID<=天数

永生天地 2010-05-15

打赏
举报

from A a lef tjoin B b where a.ID<=天数

一个查询问题

相关推荐