日期:2014-05-18  浏览次数:20443 次

求一sql语句 筛选的功能 在线等 急 请高手们指点指点。
有一表如下:
SQL code

--创建表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的数据。
想要的结果是:

------解决方案--------------------
SQL code

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
*/

------解决方案--------------------
你用goto 插入太麻烦了直接这样就可以了。
SQL code

INSERT INTO [table]
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

------解决方案--------------------
SQL code
select * from [table] where tb1 in('A','D')
union all
select * 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