有个复杂的查询,帮忙看下

aiq 2011-03-03 03:49:30
举个例子,
10个人(可以用代码表示m1、m2...m10), 5个店(c1、c2...c5),

有一个消费表记录每个人在每个店里的消费情况,如表a,我现只列出两列,其他信息暂不管
人代码 店代码
m1 c1
m1 c2
m2 c3
...
...
m9 c4

m1 c1表示m1这个人在店c1里消费过

给出两个条件 5个人或以上(随便) 3个店或以上(随便)

现在要找出3个店或以上来,这3个店最少有5个人来消费,而且这5个人同时在这3个店出现过

不知道讲清楚了没有,晕
...全文
274 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
aiq 2011-03-04
  • 打赏
  • 举报
回复
不会滚一般去,垃圾
aiq 2011-03-04
  • 打赏
  • 举报
回复
楼上我不想骂你,但你就是SB,
论坛本来就是交流,请教的,我不会,来问问有什么问题,2B
ALAN_XQ 2011-03-04
  • 打赏
  • 举报
回复
鄙视你这种不劳而获的人。。。
aiq 2011-03-04
  • 打赏
  • 举报
回复
或者不一定要一个语法实现
aiq 2011-03-04
  • 打赏
  • 举报
回复
还是谢谢你
aiq 2011-03-04
  • 打赏
  • 举报
回复
好像不对
notebook800 2011-03-04
  • 打赏
  • 举报
回复
不对哦,哭
notebook800 2011-03-04
  • 打赏
  • 举报
回复

with t as (select 'm1' m, 'c1' c from dual
union select 'm2' m, 'c1' c from dual
union select 'm3' m, 'c1' c from dual
union select 'm4' m, 'c1' c from dual
union select 'm5' m, 'c1' c from dual
union select 'm6' m, 'c1' c from dual
union select 'm1' m, 'c2' c from dual
union select 'm2' m, 'c2' c from dual
union select 'm3' m, 'c2' c from dual
union select 'm4' m, 'c2' c from dual
union select 'm5' m, 'c2' c from dual
union select 'm6' m, 'c2' c from dual
union select 'm1' m, 'c3' c from dual
union select 'm2' m, 'c3' c from dual
union select 'm3' m, 'c3' c from dual
union select 'm4' m, 'c3' c from dual
union select 'm5' m, 'c3' c from dual
union select 'm6' m, 'c3' c from dual
union select 'm1' m, 'c4' c from dual
union select 'm2' m, 'c4' c from dual
union select 'm3' m, 'c4' c from dual
union select 'm4' m, 'c4' c from dual
union select 'm5' m, 'c4' c from dual
union select 'm2', 'c3' from dual
union select 'm9', 'c5' from dual)
select * from t
where exists
(select * from (select * from (select c from t group by c having count(*) >= 5)
full join (select m from t group by m having count(*) > 3) on 1 = 1)
where t.m = m and t.c = c);
aiq 2011-03-03
  • 打赏
  • 举报
回复
谢谢你,其实你开始理解是对的

create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(2 ,1);
insert into t1 values(3 ,1);
insert into t1 values(4 ,1);
insert into t1 values(5 ,1);
insert into t1 values(1 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,2);
insert into t1 values(4 ,2);
insert into t1 values(5 ,2);
insert into t1 values(1 ,3);
insert into t1 values(2 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,3);
insert into t1 values(5 ,3);
insert into t1 values(1 ,4);
insert into t1 values(2 ,4);
insert into t1 values(3 ,4);
insert into t1 values(4 ,4);
insert into t1 values(5 ,4);
insert into t1 values(4 ,6);
insert into t1 values(5 ,6);

我说的相同时指storecode中的1、2、3、4对应的p都包含12345五个或以上相同的值,我得出值是storecode值是1234

create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(2 ,1);
insert into t1 values(3 ,1);
insert into t1 values(4 ,1);
insert into t1 values(5 ,1);
insert into t1 values(1 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,2);
insert into t1 values(4 ,2);
insert into t1 values(5 ,2);
insert into t1 values(1 ,3);
insert into t1 values(2 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,3);
insert into t1 values(5 ,3);
insert into t1 values(1 ,4);
insert into t1 values(2 ,4);
insert into t1 values(3 ,4);
insert into t1 values(4 ,4);
insert into t1 values(6 ,4);
insert into t1 values(7 ,4);
insert into t1 values(4 ,6);
insert into t1 values(5 ,6);


上面storecode中的1、2、3都对应的p都包含12345五个或以上相同的值,我得出值是storecode值是123,
此时storecode中4不包含5了,它和1、2、3没有5个以上相同值了
e8923704 2011-03-03
  • 打赏
  • 举报
回复
理解错误,同时有相同的5个人阿, 以为是不同的。!

PersonCode 是人物ID
StoreCode 是店面ID

那么继续写。


drop table t1;
create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(1 ,1);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,4);
insert into t1 values(4 ,4);

select * from
(select storecode from t1 group by personcode, storecode having count(*) >= 5)
where
(select count(*) from
(select storecode from t1 group by personcode, storecode having count(*) >= 5)) >= 3;
aiq 2011-03-03
  • 打赏
  • 举报
回复
楼上,不对,
insert into t1 values(5 ,3);
如果改成 insert into t1 values(7 ,3);

查询结果还有3出来,因为这时候我需要s对用的p中人最少要5个相同,现在3中有个7了
e8923704 2011-03-03
  • 打赏
  • 举报
回复

create table t1 (personcode int ,storecode int) ;
insert into t1 values(1 ,1);
insert into t1 values(2 ,1);
insert into t1 values(3 ,1);
insert into t1 values(4 ,1);
insert into t1 values(5 ,1);
insert into t1 values(1 ,2);
insert into t1 values(2 ,2);
insert into t1 values(3 ,2);
insert into t1 values(4 ,2);
insert into t1 values(5 ,2);
insert into t1 values(1 ,3);
insert into t1 values(2 ,3);
insert into t1 values(3 ,3);
insert into t1 values(4 ,3);
insert into t1 values(5 ,3);
insert into t1 values(1 ,4);
insert into t1 values(2 ,4);

select storecode from
(select storecode from
(select * from t1 group by personcode, storecode)
group by storecode having count(storecode) >= 5)
where
(select count(*) from
(select storecode from
(select * from t1 group by personcode, storecode)
group by storecode having count(storecode) >= 5)) >= 3;


查询语句可以存个临时表,就不用查两遍了。
aiq 2011-03-03
  • 打赏
  • 举报
回复
不对波
liangpei2008 2011-03-03
  • 打赏
  • 举报
回复
错了,是5个相同的人
没环境了,只能试着写了
select *
from t1 a
where not exists
(select 1 from t1 where a.storecode=storecode having(personcode)<5)
liangpei2008 2011-03-03
  • 打赏
  • 举报
回复

这样?
create table t1 (personcode int ,storecode int)
insert into t1 select 1 ,1
union all select 2 ,1
union all select 3 ,1
union all select 4 ,1
union all select 5 ,1
union all select 2 ,1
union all select 4 ,2
union all select 5 ,3
union all select 6 ,2

select *
from t1 a
where
(select distinct count(personcode) from t1 where a.storecode=storecode )>5
aiq 2011-03-03
  • 打赏
  • 举报
回复
如果某3个店(或者4个店、5个店,只要在3个店以上)
同时有相同的5个人(或以上6个、7个,但人随便,不固定)出现消费记录的,则要把这三个店找出来

我实在没法表达了,呵呵
e8923704 2011-03-03
  • 打赏
  • 举报
回复
给测试数据,和你希望得到的结果。

看了5分钟没看懂.
aiq 2011-03-03
  • 打赏
  • 举报
回复
都是空话,希望下面能给出集体步骤
kye_jufei 2011-03-03
  • 打赏
  • 举报
回复
可以用關聯、級聯(如:left join....)SQL語句進行分組group by進行聯合查詢。。。
你可以把你的一些測試數據貼出來,然後寫上想要的結果。。。。。
aiq 2011-03-03
  • 打赏
  • 举报
回复
希望给出具体步骤,基本语法我还是懂的,但怎么写都得不出结果
加载更多回复(1)

2,507

社区成员

发帖
与我相关
我的任务
社区描述
Delphi 数据库相关
社区管理员
  • 数据库相关社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧