没分了，来水区求一条sql

[dahuatttt]

如下表:

ID   PID   detail
1      3     葱花
2      3     糖
3      3     盐

4      4     小麦
5      4     糖
6      4     酵母
7      4     麦乳精

8      5     糖
9      5     麦乳精
10     5     盐

希望实现的sql:
在给出2个及2个以上个配料名，能找出含有所给配料名的PID组。
假设给定 糖,盐
那就该找出PID 3，5
如果给出 麦乳精,糖
那就该找出PID 4, 5
如果给出 小麦,酵母,糖
那就该找出PID 4

分数不足，见谅

[帝企鹅已被占用]

select pid from tbl where detail in ('小麦','酵母','糖')
烂sql，别和你老板说，你知道下场滴

[dahuatttt]

[Quote=引用 1 楼 patrickkong 的回复:]

select pid from tbl where detail in ('小麦','酵母','糖')
烂sql，别和你老板说，你知道下场滴
[/Quote]
这条sql是错的，你这样只是把含有3个配料中满足任一配料的数据选出来。

[chuanzhang5687]

如果是 葱花呢

[chuanzhang5687]

select pid from tb where id in (
select max(id) from tb group by detail having count(*) >=2)
这样行不？

[dahuatttt]

[Quote=引用 3 楼 chuanzhang5687 的回复:]

如果是 葱花呢
[/Quote]
至少查2项
[Quote=引用 4 楼 chuanzhang5687 的回复:]

SQL code
select pid from tb where id in (
select max(id) from tb group by detail having count(*) >=2)
这样行不？
[/Quote]
你这里也没地方传入配料名啊。。。

[coder_s]

select a.PID from testtable a,testtable b where a.PID=b.PID
and a.Detail=N'盐' and b.Detail=N'糖'

由于条件的不确定性 所以 可能需要写成动态的. 需要查询多少种类 就得 多拼几个条件,

跟 8 楼的 差不多.

[dahuatttt]

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

引用 2 楼 dahuatttt 的回复:

引用 1 楼 patrickkong 的回复:

select pid from tbl where detail in ('小麦','酵母','糖')
烂sql，别和你老板说，你知道下场滴

这条sql是错的，你这样只是把含有3个配料中满足任一配料的数据选出来。


和你的需求有出入吗？？
[/Quote]
[Quote=引用 9 楼 aoyihuashao 的回复:]

引用 1 楼 patrickkong 的回复:
select pid from tbl where detail in ('小麦','酵母','糖')
烂sql，别和你老板说，你知道下场滴


select distinct pid from tbl where detail in ('小麦','酵母','糖')
[/Quote]

直接使用in的话，只是或的关系，pid中或有A，或有B。只需满足其一即可。
而我的意思是，既要满足A又要满足B。

[dahuatttt]

[Quote=引用 6 楼 hao446tian 的回复:]

SQL code
select pid
from (select pid, t.detail from test t group by t.pid, t.detail) a
where a.detail = '糖'
INTERSECT
select pid
from (select pid, t.detail from test t group by t.pid, t.detail)……
[/Quote]
不好意思，还真没看懂这条SQL，，，头一次知道INTERSECT关键字。这个需要动态拼接的是吧？根据条件数量

[dahuatttt]

[Quote=引用 8 楼 wang123kui 的回复:]

SQL code

with t as
(
select 1 id , 3 pid , '葱花' detail from dual
union all
select 2 , 3 , '糖' from dual
union all
select 3 , 3 , '盐' from dual
union all ……
[/Quote]
t表必须存在吗？配料可能随时间一直在增加了。

[Quote=引用 10 楼 socialism_s13 的回复:]

select a.PID from testtable a,testtable b where a.PID=b.PID
and a.Detail=N'盐' and b.Detail=N'糖'

由于条件的不确定性 所以 可能需要写成动态的. 需要查询多少种类 就得 多拼几个条件,

跟 8 楼的 差不多.
[/Quote]
你这方法挺讨巧，呵呵

[zhuhongyi]

select pid from tablename where detail in ('糖' , '盐') group by pid having count(pid)=2
select pid from tablename where detail in ('糖' , '小麦', '酵母') group by pid having count(pid)=3

楼主试试我这个。主要思想是先找出detail等于给的值的行；然后按不同pid分类，每个分类有个个数，这个个数等于条件数，则说明给出的调料都在pid里。
不知道我说明白了没。

[fengnenglu]

假设表名是code;

select a.pid from
(select * from code where detail='小麦')a,
(select * from code where detail='酵母')b,
(select * from code where detail='糖')c
where a.pid=b.pid and a.pid=c.pid

几个参数就有几个(select * from code where detail='名')，可以得到你像要的答案，

[桃花岛黄岛主]

[Quote=引用 13 楼 dahuatttt 的回复:]
引用 8 楼 wang123kui 的回复:

SQL code

with t as
(
select 1 id , 3 pid , '葱花' detail from dual
union all
select 2 , 3 , '糖' from dual
union all
select 3 , 3 , '盐' from dual
union all ……

t表必须存……
[/Quote]

这个T是用来表示你的表,测试用的

[loveofmylife]

with tmp as (select pid , wmsys.wm_concat(detail) detail from t group by pid)
select pid from tmp
where detail like '%糖%' and detail like '%盐%'

[loveofmylife]

给你写了个demo

scott@ORCL> create table t (id varchar2(4) primary key,pid varchar2(4) , detail varchar2(20))
  2  ;
scott@ORCL> desc t
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- -------------------------------

 ID                                                    NOT NULL VARCHAR2(4)
 PID                                                            VARCHAR2(4)
 DETAIL                                                         VARCHAR2(20)

scott@ORCL> insert into t values(1,3,'葱花');
scott@ORCL> insert into t values(2,3,'糖');
scott@ORCL> insert into t values(3,3,'盐');
scott@ORCL> insert into t values(4,4,'小麦');
scott@ORCL> insert into t values(5,4,'糖');
scott@ORCL> insert into t values(6,4,'酵母');
scott@ORCL> insert into t values(7,4,'麦乳精');
scott@ORCL> insert into t values(8,5,'糖');
scott@ORCL> insert into t values(9,5,'麦乳精');
scott@ORCL> insert into t values(10,5,'盐');
scott@ORCL> commit;

定义type变长数组

CREATE OR REPLACE TYPE testArr AS VARRAY(50) of varchar(20);

定义过程

create or replace procedure p_t_test(i_timeArr in testArr)
is
       type c_ref_type is ref cursor;
       dataCur c_ref_type;
       v_sqlFragment varchar2(500);
       type rec_type is record(
            pid t.pid%type
       );
       rec rec_type;
begin  
       
       for i in 1..i_timeArr.COUNT-1 loop
           v_sqlFragment:=v_sqlFragment||' detail like ''%'||i_timeArr(i)||'%'' and ';
       end loop;
       v_sqlFragment :=v_sqlFragment||'detail like ''%'||i_timeArr(i_timeArr.COUNT)||'%''';
       open dataCur for q'!with tmp as (select pid , wmsys.wm_concat(detail) detail from t group by pid)
                              select pid from tmp
                              where !' ||v_sqlFragment ;
       loop
         fetch dataCur
           into rec;
         exit when dataCur%notfound;
         DBMS_OUTPUT.put_line('rec.pid : ' ||rec.pid  );
      end loop;
      close dataCur;
exception 
      when others then 
           DBMS_OUTPUT.put_line(q'!with tmp as (select pid , wmsys.wm_concat(detail) detail from t group by pid)
                              select pid from tmp
                              where!' ||v_sqlFragment) ;
           raise;      
end p_t_test;

测试

SQL> declare
  2        test_arr testArr := testArr();
  3  begin
  4        test_arr.EXTEND;
  5        test_arr(1) := '糖';
  6        test_arr.EXTEND;
  7        test_arr(2) := '盐';
  8        dbms_output.put_line(test_arr(1)||' '||test_arr(2));
  9        p_t_test(test_arr);
 10  end;
 11  /
 
糖 盐
rec.pid : 3
rec.pid : 5
 
PL/SQL procedure successfully completed

楼主没啥疑问了吧

[hao446tian]

select pid
  from (select pid, t.detail from test t group by t.pid, t.detail) a
 where a.detail = '糖'
INTERSECT
select pid
  from (select pid, t.detail from test t group by t.pid, t.detail) a
 where a.detail = '小麦'
INTERSECT
select pid
  from (select pid, t.detail from test t group by t.pid, t.detail) a
 where a.detail = '酵母'

[积木]

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

引用 1 楼 patrickkong 的回复:

select pid from tbl where detail in ('小麦','酵母','糖')
烂sql，别和你老板说，你知道下场滴

这条sql是错的，你这样只是把含有3个配料中满足任一配料的数据选出来。
[/Quote]

和你的需求有出入吗？？

[桃花岛黄岛主]

with t as
(
select 1 id   ,  3  pid ,  '葱花' detail from dual
union all        
select 2    ,  3   ,  '糖' from dual
union all         
select 3    ,  3   ,  '盐' from dual
union all         
select 4    ,  4   ,  '小麦' from dual
union all         
select 5    ,  4   ,  '糖' from dual
union all         
select 6    ,  4   ,  '酵母' from dual
union all         
select 7    ,  4   ,  '麦乳精' from dual
union all         
select 8    ,  5   ,  '糖' from dual
union all         
select 9    ,  5   ,  '麦乳精' from dual
union all         
select 10   ,  5   ,  '盐' from dual
)

select distinct pid from t a where 
a.detail in ('糖','盐') and 
 exists(select 1 from t b where a.pid = b.pid and detail = '糖')
 and exists(select 1 from t b where a.pid = b.pid and detail = '盐')
--------------------
pid
-----
3
5

[aoyihuashao]

[Quote=引用 1 楼 patrickkong 的回复:]
select pid from tbl where detail in ('小麦','酵母','糖')
烂sql，别和你老板说，你知道下场滴
[/Quote]

select distinct pid from tbl where detail in ('小麦','酵母','糖')