id name cer lev
1 张三 电工证 3
2 张三 高空作业证 2
3 李四 电工证 2
4 李四 驾驶证 B1
5 王五 驾驶证 B1
6 王五 高空作业证 2
7 王五 电工证 3同过cer和lev查询符合条件的的,比如我查找具有电工证 3的,就是张三和王五,查找同时具有驾驶证和电工证的就是李四和王五求各路大神指教,本人菜鸟一个,望大神们说的详细点,不胜感激
1 张三 电工证 3
2 张三 高空作业证 2
3 李四 电工证 2
4 李四 驾驶证 B1
5 王五 驾驶证 B1
6 王五 高空作业证 2
7 王五 电工证 3同过cer和lev查询符合条件的的,比如我查找具有电工证 3的,就是张三和王五,查找同时具有驾驶证和电工证的就是李四和王五求各路大神指教,本人菜鸟一个,望大神们说的详细点,不胜感激
解决方案 »
- 老被一些木马攻击,请教一个良策
- 散分!从news表中查询第31至40条记录,主键为ID,ID可能不连续,下面三个种方法,是不是都正确呢??节日快乐!!
- sql server 2005 可以对整个数据库加密吗?
- 求一条语句
- 如何同时复制表的结构和内容到远程服务器上
- 请教如何删除记录中重复的行,只保留一行,数据库日志恢复数据?
- SuperSocket 信息: (SpnRegister) : Error 1355
- 请教一个关于SQLSERVER 2000的数据库导入导出的问题
- 数据库的日志太大了,如何删除它???
- SQL SERVER2000中文标准版的序列号
- 求解三道数据库的题
- 如何写小于或等于零时显示为0的语句
--1.有电工证 3
select [name] from 表 with(nolock) where cer='有电工证' and lev='3'
select [name] from 表 where cer in ('电工证','驾驶证') group by [name]
having count[name]=2
select name from 表名 where name in(select name from 表名 where cer='驾驶证')
and name in(select name from 表名 where cer='电工证')
应该是select distinct [name] from .....等等,我想一个
select name from tb where cer = '电工证' abd lev = 32.
select name from tb
where cer = '驾驶证'
intersect
select name from tb
where cer = '电工证'
GO
------------------------------------------------------------------------------ Create & Insert ------------------->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([id] smallint,[name] nvarchar(2),[cer] nvarchar(5),[lev] nvarchar(2))
Insert into tb
Select 1,N'张三',N'电工证',N'3'
Union all Select 2,N'张三',N'高空作业证',N'2'
Union all Select 3,N'李四',N'电工证',N'2'
Union all Select 4,N'李四',N'驾驶证',N'B1'
Union all Select 5,N'王五',N'驾驶证',N'B1'
Union all Select 6,N'王五',N'高空作业证',N'2'
Union all Select 7,N'王五',N'电工证',N'3'
Union all Select 8,N'张三',N'XXX证',N'3' -- test
Union all Select 9,N'王五',N'XXX证',N'3' -- test
------------------------------------------------------------------------------ Statement -----------------------DECLARE @cer NVARCHAR(MAX)
,@lev NVARCHAR(MAX)
,@sql NVARCHAR(MAX)
SELECT @cer=N'电工证'
,@lev=N'3'SET @lev=CASE
WHEN ISNULL(@lev,'')<>''
THEN CASE
WHEN CHARINDEX(',',@lev)>0
THEN ' And Exists(Select 1 from tb As x Where x.name=a.name And x.lev='''+REPLACE(@lev,',',''') And Exists(Select 1 from tb As x Where x.name=a.name And x.lev=''')+''')'
ELSE ' And lev='''+@lev+''''
END
ELSE ''
END
SET @cer=CASE
WHEN ISNULL(@cer,'')<>''
THEN CASE
WHEN CHARINDEX('Exists',@lev)>0
THEN ' And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'''+REPLACE(@cer,',',''') And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N''')+''')'
ELSE ' And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'''+REPLACE(@cer,',',''''+@lev+') And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N''')+''''+@lev+')'
END
ELSE @lev END
Exec ('Select name from tb As a Where 1=1'+@cer+' Group by name Order by Min(ID)')
Go
------------------------------------------------------------------------------ Stage -----------------------
---------- Stage 1.SELECT @cer=N'电工证'
,@lev=N''
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证')
Group by name Order by Min(ID)結果:
name
----
张三
李四
王五
*/---------- Stage 2.SELECT @cer=N'电工证'
,@lev=N'3'
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证' And lev='3')
Group by name Order by Min(ID)結果:
name
----
张三
王五
*/---------- Stage 3.SELECT @cer=N'驾驶证,电工证'
,@lev=N''
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'驾驶证')
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证')
Group by name Order by Min(ID)結果:
name
----
李四
王五
*/---------- Stage 4.SELECT @cer=N'电工证,XXX证'
,@lev=N'3'
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证' And lev='3')
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'XXX证' And lev='3')
Group by name Order by Min(ID)結果:
name
----
张三
王五
*/---------- Stage 5.SELECT @cer=N''
,@lev=N'2,3'
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.lev='2')
And Exists(Select 1 from tb As x Where x.name=a.name And x.lev='B1')
Group by name Order by Min(ID)結果:
name
----
李四
王五
*/
GO
------------------------------------------------------------------------------ Create & Insert ------------------->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([id] smallint,[name] nvarchar(2),[cer] nvarchar(5),[lev] nvarchar(2))
Insert into tb
Select 1,N'张三',N'电工证',N'3'
Union all Select 2,N'张三',N'高空作业证',N'2'
Union all Select 3,N'李四',N'电工证',N'2'
Union all Select 4,N'李四',N'驾驶证',N'B1'
Union all Select 5,N'王五',N'驾驶证',N'B1'
Union all Select 6,N'王五',N'高空作业证',N'2'
Union all Select 7,N'王五',N'电工证',N'3'
Union all Select 8,N'张三',N'XXX证',N'3' -- test
Union all Select 9,N'王五',N'XXX证',N'3' -- test
------------------------------------------------------------------------------ Statement -----------------------DECLARE @cer NVARCHAR(MAX)
,@lev NVARCHAR(MAX)
,@sql NVARCHAR(MAX)
SELECT @cer=N'电工证'
,@lev=N'3'SET @lev=CASE
WHEN ISNULL(@lev,'')<>''
THEN CASE
WHEN CHARINDEX(',',@lev)>0
THEN ' And Exists(Select 1 from tb As x Where x.name=a.name And x.lev='''+REPLACE(@lev,',',''') And Exists(Select 1 from tb As x Where x.name=a.name And x.lev=''')+''')'
ELSE ' And lev='''+@lev+''''
END
ELSE ''
END
SET @cer=CASE
WHEN ISNULL(@cer,'')<>''
THEN CASE
WHEN CHARINDEX('Exists',@lev)>0
THEN ' And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'''+REPLACE(@cer,',',''') And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N''')+''')'
ELSE ' And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'''+REPLACE(@cer,',',''''+@lev+') And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N''')+''''+@lev+')'
END
ELSE @lev END
Exec ('Select name from tb As a Where 1=1'+@cer+' Group by name Order by Min(ID)')
Go
------------------------------------------------------------------------------ Stage -----------------------
---------- Stage 1.SELECT @cer=N'电工证'
,@lev=N''
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证')
Group by name Order by Min(ID)結果:
name
----
张三
李四
王五
*/---------- Stage 2.SELECT @cer=N'电工证'
,@lev=N'3'
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证' And lev='3')
Group by name Order by Min(ID)結果:
name
----
张三
王五
*/---------- Stage 3.SELECT @cer=N'驾驶证,电工证'
,@lev=N''
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'驾驶证')
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证')
Group by name Order by Min(ID)結果:
name
----
李四
王五
*/---------- Stage 4.SELECT @cer=N'电工证,XXX证'
,@lev=N'3'
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'电工证' And lev='3')
And Exists(Select 1 from tb As x Where x.name=a.name And x.cer=N'XXX证' And lev='3')
Group by name Order by Min(ID)結果:
name
----
张三
王五
*/---------- Stage 5.SELECT @cer=N''
,@lev=N'2,3'
/*
Select name from tb As a
Where 1=1
And Exists(Select 1 from tb As x Where x.name=a.name And x.lev='2')
And Exists(Select 1 from tb As x Where x.name=a.name And x.lev='B1')
Group by name Order by Min(ID)結果:
name
----
李四
王五
*/