有一表如下:--创建表Table
CREATE 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 int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('A',''+@X+'')
while @X<=10 goto lblHere
------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('B',''+@X+'')
while @X<=10 goto lblHere
--------------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('C',''+@X+'')
while @X<=10 goto lblHere
-----------------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('D',''+@X+'')
while @X<=10 goto lblHere
-----------------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('E',''+@X+'')
while @X<=10 goto lblHere要求条件:Tb1字段中A,D的数据全部输入,B,C,E则只输出Tb2大于5的数据。
想要的结果是:
CREATE 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 int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('A',''+@X+'')
while @X<=10 goto lblHere
------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('B',''+@X+'')
while @X<=10 goto lblHere
--------------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('C',''+@X+'')
while @X<=10 goto lblHere
-----------------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('D',''+@X+'')
while @X<=10 goto lblHere
-----------------------------------------------------------
declare @X int
select @X=0
lblHere:
select @X=@X+1
insert into [Table](Tb1,Tb2)values('E',''+@X+'')
while @X<=10 goto lblHere要求条件:Tb1字段中A,D的数据全部输入,B,C,E则只输出Tb2大于5的数据。
想要的结果是:
select * from [table] where
tb1 in ('a','d') or (tb1 in ('b','c','e') and tb2>5)
/*
ID Tb1 Tb2
----------- ---------------- ----------
1 A 1
2 A 2
3 A 3
4 A 4
5 A 5
6 A 6
7 A 7
8 A 8
9 A 9
10 A 10
11 A 11
17 B 6
18 B 7
19 B 8
20 B 9
21 B 10
22 B 11
28 C 6
29 C 7
30 C 8
31 C 9
32 C 10
33 C 11
34 D 1
35 D 2
36 D 3
37 D 4
38 D 5
39 D 6
40 D 7
41 D 8
42 D 9
43 D 10
44 D 11
50 E 6
51 E 7
52 E 8
53 E 9
54 E 10
55 E 11
*/
SELECT tb1 ,
tb2
FROM ( 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
union all
select * from [table] where tb1 in('B','C','E') and tb2>5
select * from [table] where tb1 in('A','D') or tb in('B','C','E') and tb2>5