求sql语句

[CeleryZeng]

现有如下数据
relation code
1 0101
1 0102
2 0101
3 0202
4 0001S
4 0101
5 0101
5 0102
5 0103E
6 0002
6 0102
... ....

我需要通过code列的值找一个relation（ps：一个唯一的值）
例如：输入0101,0102 我需要检索出relation=1
输入0101 检索relation= 2
输入0102 检索relation= 3
输入0001，0101 检索relation=4
类推

[CeleryZeng]

[Quote=引用 11 楼 qianjin036a 的回复:]

SQL code
create table tb(relation int,code varchar(10))
insert into tb select 1,'0101'
insert into tb select 1,'0102'
insert into tb select 2,'0101'
insert into tb select 3,'0202'
insert into tb sel……
[/Quote]
relation = 1 relation =5对应的code值互换 这个就不对了。。。快帮我想办法呀

[昵称被占用了]

输入0101,0102 我需要检索出relation=1

select distinct relation 
from tab a
where code = '0101'
and exists (
  select 1 from tab
  where relation  = a.relation 
  and code = '0102'
  )
and not exists 
  select 1 from tab
  where relation  = a.relation 
  and code not in ( '0101','0102')
  )

输入0101 检索relation= 2

select distinct relation 
from tab a
where code = '0101'
and not exists 
  select 1 from tab
  where relation  = a.relation 
  and code not in ( '0101')
  )

[昵称被占用了]

程序根据条件个数拼凑如上语句（每个语句包含一个code=的条件，0到多个exists条件，一个not exists条件）
。

[-晴天]

0101,0102 ,是分别输,还是这两个值连在一起输?

[-晴天]

select distinct min(relation) from tb where code='0101'

[AcHerat]

如果 0102 的 relation 有多个呢？

[-晴天]

select min(relation) from tb where code='0101'
 
 

[薇薇]

关注

[geniuswjt]

declare @param varchar(max)   --你要传入的code
exec ('select relation from tb where code in ('''+@param+''')')

[Quote=引用楼主 celeryzeng 的回复:]
现有如下数据
relation code
1 0101
1 0102
2 0101
3 0202
4 0001S
4 0101
5 0101
5 0102
5 0103E
6 0002
6 0102
... ....

我需要通过code列的值找一个relation（ps：一个唯一的值）
例如：输入0101,0102……
[/Quote]

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-10-24 10:11:22
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([relation] int,[code] varchar(5))
insert [tb]
select 1,'0101' union all
select 1,'0102' union all
select 2,'0101' union all
select 3,'0202' union all
select 4,'0001S' union all
select 4,'0101' union all
select 5,'0101' union all
select 5,'0102' union all
select 5,'0103E' union all
select 6,'0002' union all
select 6,'0102'
--------------开始查询--------------------------
select relation from tb t where code='0101' group by relation having COUNT(1)=(select COUNT(code) from tb where relation=t.relation)
----------------结果----------------------------
/* relation
-----------
2

(1 行受影响)
*/

[CeleryZeng]

[Quote=引用 2 楼 qianjin036a 的回复:]

SQL code
select distinct min(relation) from tb where code='0101'
[/Quote]

跟min无关

[geniuswjt]

貌似不对，你这个需求要把这个表先合并列值成新表，然后再输入code查找relation
因为你的唯一性[Quote=引用 6 楼 geniuswjt 的回复:]
SQL code

declare @param varchar(max) --你要传入的code
exec ('select relation from tb where code in ('''+@param+''')')



引用楼主 celeryzeng 的回复:
现有如下数据
relation code
1 0101
1 0102
2 0101
3 020……
[/Quote]

[CeleryZeng]

[Quote=引用 7 楼 fredrickhu 的回复:]

SQL code
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-10-24 10:11:22
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10……
[/Quote]
输入0101,0102 检索relation=1
输入0001，0101 检索relation=4
输入0101,0102，0103E 检索relation=5
这个怎么弄呢？？

[-晴天]

create table tb(relation int,code varchar(10))
insert into tb select 1,'0101'
insert into tb select 1,'0102'
insert into tb select 2,'0101'
insert into tb select 3,'0202'
insert into tb select 4,'0001S'
insert into tb select 4,'0101'
insert into tb select 5,'0101'
insert into tb select 5,'0102'
insert into tb select 5,'0103E'
insert into tb select 6,'0002'
insert into tb select 6,'0102'
go
declare @col varchar(1000)
set @col='0101,0102'
--set @col='0001,0101'
;with cte as(
select row_number()over(order by (select 1))rn, substring(@col,number,charindex(',',@col+',',number+1)-number)col from master..spt_values
where type='p' and number<=len(@col) and substring(@col,number,1)<>',' and substring(','+@col,number,1)=','
)select top 1 relation from (
select distinct b.relation,a.col from cte a inner join tb b on b.code like a.col+'%'
)t group by relation having count(*)=(select max(rn) from cte)
/*
relation
-----------
1

(1 行受影响)

*/
go
drop table tb

[快溜]

if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([relation] int,[code] varchar(5))
insert [tb]
select 1,'0101' union all
select 1,'0102' union all
select 2,'0101' union all
select 3,'0202' union all
select 4,'0001S' union all
select 4,'0101' union all
select 5,'0101' union all
select 5,'0102' union all
select 5,'0103E' union all
select 6,'0002' union all
select 6,'0102'

declare @str varchar(100)
set @str='0001S,0101'
select top 1 relation from tb
 group by relation 
 order by sum(case when charindex(','+rtrim(code)+',',','+@str+',')>0 
            then 1 else 0 end) desc,count(*)
            
/*
relation
-----------
4
            

[CeleryZeng]

哦 ps上面应该是0001S 0101检索relation=4

[@信心]

学习一下！