有一表如下：
- SQL code
--创建表TableCREATE TABLE [dbo].[Table]( [ID] [int] IDENTITY(1,1) NOT NULL, [Tb1] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL, [Tb2] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]--插入数据declare @X intselect @X=0lblHere:select @[email protected]+1insert into [Table](Tb1,Tb2)values('A',[email protected]+'')while @X<=10 goto lblHere------------------------------------------------declare @X intselect @X=0lblHere:select @[email protected]+1insert into [Table](Tb1,Tb2)values('B',[email protected]+'')while @X<=10 goto lblHere--------------------declare @X intselect @X=0lblHere:select @[email protected]+1insert into [Table](Tb1,Tb2)values('C',[email protected]+'')while @X<=10 goto lblHere-----------------------declare @X intselect @X=0lblHere:select @[email protected]+1insert into [Table](Tb1,Tb2)values('D',[email protected]+'')while @X<=10 goto lblHere-----------------------declare @X intselect @X=0lblHere:select @[email protected]+1insert into [Table](Tb1,Tb2)values('E',[email protected]+'')while @X<=10 goto lblHere
要求条件：Tb1字段中A,D的数据全部输入，B,C,E则只输出Tb2大于5的数据。
想要的结果是：
------解决方案--------------------
- SQL code
select * from [table] where tb1 in ('a','d') or (tb1 in ('b','c','e') and tb2>5)/*ID Tb1 Tb2----------- ---------------- ----------1 A 12 A 23 A 34 A 45 A 56 A 67 A 78 A 89 A 910 A 1011 A 1117 B 618 B 719 B 820 B 921 B 1022 B 1128 C 629 C 730 C 831 C 932 C 1033 C 1134 D 135 D 236 D 337 D 438 D 539 D 640 D 741 D 842 D 943 D 1044 D 1150 E 651 E 752 E 853 E 954 E 1055 E 11*/
------解决方案--------------------
你用goto 插入太麻烦了直接这样就可以了。
- SQL code
INSERT INTO [table]SELECT tb1 , tb2FROM ( SELECT CHAR(number) AS tb1 FROM master..spt_values WHERE type = 'p' AND number BETWEEN 65 AND 69 ) a CROSS JOIN ( SELECT number AS tb2 FROM master..spt_values WHERE type = 'p' AND number BETWEEN 1 AND 11 ) b
------解决方案--------------------
- SQL code
select * from [table] where tb1 in('A','D')union allselect * from [table] where tb1 in('B','C','E') and tb2>5
------解决方案--------------------
- SQL code
select * from [table] where tb1 in('A','D') or tb in('B','C','E') and tb2>5