抢分题,大家看看,谢谢啦!

Question

select * from test a
where 商店='零售小贩' 
and exists(select 1 from test b where a.商店=b.商店 and a.售货员=b.售货员 and b.水果='葡萄')
and exists(select 1 from test b where a.商店=b.商店 and a.售货员=b.售货员 and b.水果='黄桃')
and exists(select 1 from test b where a.商店=b.商店 and a.售货员=b.售货员 and b.水果='西瓜')
or 商店<>'零售小贩'说明：
查询条件描述不是太清楚。数据结构设计不太合理。

Answer 1

create table test( id int identity(1,1),[水果]varchar(20),[商店]varchar(20),[售货员]varchar(20),[区域]varchar(20)) 
insert into test([水果],[商店],[售货员],[区域]) 
select 
'香蕉' ,'超市','张', '上海' 
union all select 
'香蕉', '超市', '王','上海' 
union all select 
'香蕉', '超市', '赵', '上海' 
union all select 
'香蕉','超市' ,'黄', '上海' 
union all select 
'苹果' ,'批发站', '孙', '上海' 
union all select 
'苹果','批发站' ,'潘', '上海' 
union all select 
'苹果' ,'批发站' ,'刘' ,'上海' 
union all select 
'苹果' ,'批发站', '顾', '上海' 
union all select 
'西瓜' ,'零售小贩', '夏', '北京' 
union all select 
'葡萄' ,'零售小贩' ,'夏' ,'北京' 
union all select 
'葡萄' ,'零售小贩' ,'朱' ,'北京' 
union all select 
'黄桃','零售小贩', '朱' ,'北京' 
union all select 
'葡萄' ,'零售小贩' ,'钱' ,'天津' 
union all select 
'黄桃' ,'零售小贩', '钱', '天津' 
union all select 
'西瓜','零售小贩' ,'钱', '天津' 
go
delete from test where 售货员 in(
select 售货员 from test where 商店='零售小贩' group by 售货员 having count(水果)<3
)
select * from test
go
drop table test
/*
id          水果                   商店                   售货员                  区域
----------- -------------------- -------------------- -------------------- --------------------
1           香蕉                   超市                   张                    上海
2           香蕉                   超市                   王                    上海
3           香蕉                   超市                   赵                    上海
4           香蕉                   超市                   黄                    上海
5           苹果                   批发站                  孙                    上海
6           苹果                   批发站                  潘                    上海
7           苹果                   批发站                  刘                    上海
8           苹果                   批发站                  顾                    上海
13          葡萄                   零售小贩                 钱                    天津
14          黄桃                   零售小贩                 钱                    天津
15          西瓜                   零售小贩                 钱                    天津(11 行受影响)
*/

Answer 2

或者:
create table test( id int identity(1,1),[水果]varchar(20),[商店]varchar(20),[售货员]varchar(20),[区域]varchar(20)) 
insert into test([水果],[商店],[售货员],[区域]) 
select 
'香蕉' ,'超市','张', '上海' 
union all select 
'香蕉', '超市', '王','上海' 
union all select 
'香蕉', '超市', '赵', '上海' 
union all select 
'香蕉','超市' ,'黄', '上海' 
union all select 
'苹果' ,'批发站', '孙', '上海' 
union all select 
'苹果','批发站' ,'潘', '上海' 
union all select 
'苹果' ,'批发站' ,'刘' ,'上海' 
union all select 
'苹果' ,'批发站', '顾', '上海' 
union all select 
'西瓜' ,'零售小贩', '夏', '北京' 
union all select 
'葡萄' ,'零售小贩' ,'夏' ,'北京' 
union all select 
'葡萄' ,'零售小贩' ,'朱' ,'北京' 
union all select 
'黄桃','零售小贩', '朱' ,'北京' 
union all select 
'葡萄' ,'零售小贩' ,'钱' ,'天津' 
union all select 
'黄桃' ,'零售小贩', '钱', '天津' 
union all select 
'西瓜','零售小贩' ,'钱', '天津' 
go
select * from test where 售货员 in(
select 售货员 from test where 商店='零售小贩' group by 售货员 having count(水果)>2
)
or 商店<>'零售小贩'
--select * from test
go
drop table test
/*
id          水果                   商店                   售货员                  区域
----------- -------------------- -------------------- -------------------- --------------------
1           香蕉                   超市                   张                    上海
2           香蕉                   超市                   王                    上海
3           香蕉                   超市                   赵                    上海
4           香蕉                   超市                   黄                    上海
5           苹果                   批发站                  孙                    上海
6           苹果                   批发站                  潘                    上海
7           苹果                   批发站                  刘                    上海
8           苹果                   批发站                  顾                    上海
13          葡萄                   零售小贩                 钱                    天津
14          黄桃                   零售小贩                 钱                    天津
15          西瓜                   零售小贩                 钱                    天津(11 行受影响)
*/

Answer 3

id          水果                   商店                   售货员                  区域
select * from tb a,
(select 售货员,区域,商店,ss=count(distinct 水果) from tb group by 售货员,区域,商店) b,
(select 区域,商店,ss=count(distinct 水果) from tb group by 售货员,区域,商店) c
where a.售货员=b.售货员 and a.区域=b.区域 and a.商店 = b.商店
and b.区域= c.区域 and b.商店 = c.商店 and b.ss = c.ss

Answer 4

这是每个售货员买的水果数：
(select 售货员,区域,商店,ss=count(distinct 水果) from tb group by 售货员,区域,商店) b,
这个每个商店卖的水果数：
(select 区域,商店,ss=count(distinct 水果) from tb group by 售货员,区域,商店) c只要他们相等就行了：
 b.区域= c.区域 and b.商店 = c.商店 and b.ss = c.ss有什么问题吗？

Answer 5

select c.*
from (select count(distinct [水果]) as cnt ,[商店],[区域] from test group by [商店],[区域]) a,
     (select count([水果]) as cnt,[商店],[售货员],[区域] from test group by [商店],[售货员],[区域]) b,
     test c
where a.cnt = b.cnt and a.[区域]= b.[区域] and b.[区域]= c.[区域] and a.[商店]= b.[商店] and b.[商店]=c.[商店] and b.[售货员] = c.[售货员]

Answer 6

/*create table test( id int identity(1,1),[水果]varchar(20),[商店]varchar(20),[售货员]varchar(20),[区域]varchar(20)) 
insert into test([水果],[商店],[售货员],[区域]) 
select '香蕉' ,'超市','张', '上海' 
union all select 
'香蕉', '超市', '王','上海' 
union all select 
'香蕉', '超市', '赵', '上海' 
union all select 
'香蕉','超市' ,'黄', '上海' 
union all select 
'苹果' ,'批发站', '孙', '上海' 
union all select 
'苹果','批发站' ,'潘', '上海' 
union all select 
'苹果' ,'批发站' ,'刘' ,'上海' 
union all select 
'苹果' ,'批发站', '顾', '上海' 
union all select 
'西瓜' ,'零售小贩', '夏', '北京' 
union all select 
'葡萄' ,'零售小贩' ,'夏' ,'北京' 
union all select 
'葡萄' ,'零售小贩' ,'朱' ,'北京' 
union all select 
'黄桃','零售小贩', '朱' ,'北京' 
union all select 
'葡萄' ,'零售小贩' ,'钱' ,'天津' 
union all select 
'黄桃' ,'零售小贩', '钱', '天津' 
union all select 
'西瓜','零售小贩' ,'钱', '天津' 
*/
/*需要的查询结果 
1 香蕉 超市 张 上海 
2 香蕉 超市 王 上海 
3 香蕉 超市 赵 上海 
4 香蕉 超市 黄 上海 
5 苹果 批发站 孙 上海 
6 苹果 批发站 潘 上海 
7 苹果 批发站 刘 上海 
8 苹果 批发站 顾 上海 
13 葡萄 零售小贩 钱 天津 
14 黄桃 零售小贩 钱 天津 
15 西瓜 零售小贩 钱 天津 
说明: 
1.第9-12行记录地区是北京,北京所对应的是零售小贩,零售小贩一共包含西瓜,葡萄,黄桃. 
夏,朱两个售货员没有卖过全部这三种水果.应此这些记录是不符合要求的.去除. 
2.第13-15行记录中地区是北京,北京所对应的是零售小贩,零售小贩一共包含西瓜,葡萄,黄桃. 
钱三种水果都卖,所以记录符合要求. 
3.第1-8行也是这种规则,都符合要求所以返回在结果中 
4.查询中不能直接引用苹果,批发站,潘,上海等数据. 
*/ 
--1 查询每个区域销售水果的数量
--select count(distinct [水果]) as cnt ,[商店],[区域] from test group by [商店],[区域]--2 查询每个售货员销售水果数量
--select count([水果]) as cnt,[商店],[售货员],[区域] from test group by [商店],[售货员],[区域]--3 
select c.*
from (select count(distinct [水果]) as cnt ,[商店],[区域] from test group by [商店],[区域]) a,
     (select count([水果]) as cnt,[商店],[售货员],[区域] from test group by [商店],[售货员],[区域]) b,
     test c
where a.cnt = b.cnt and a.[区域]= b.[区域] and b.[区域]= c.[区域] and a.[商店]= b.[商店] and b.[商店]=c.[商店] and b.[售货员] = c.[售货员]
/*
（所影响的行数为 11 行）id          水果                   商店                   售货员                  区域                   
----------- -------------------- -------------------- -------------------- -------------------- 
1           香蕉                   超市                   张                    上海
2           香蕉                   超市                   王                    上海
3           香蕉                   超市                   赵                    上海
4           香蕉                   超市                   黄                    上海
5           苹果                   批发站                  孙                    上海
6           苹果                   批发站                  潘                    上海
7           苹果                   批发站                  刘                    上海
8           苹果                   批发站                  顾                    上海
13          葡萄                   零售小贩                 钱                    天津
14          黄桃                   零售小贩                 钱                    天津
15          西瓜                   零售小贩                 钱                    天津（所影响的行数为 11 行）*/

Answer 7

select t.* 
from test as t
    inner join 
     (select a.售货员,a.区域,a.商店
      from 
       (select 售货员,区域,商店,count(水果)as m
        from test
         group by 售货员,区域,商店) as a
        inner join 
       (select 区域,商店,count(水果) as n 
        from #a
        group by 区域,商店)as b
     on  a.m=b.n and 
         a.区域=b.区域 and 
         a.商店=b.商店) as v
on t.售货员=v.售货员
=----------------------------------------------
id          水果                   商店                   售货员                  区域                   
----------- -------------------- -------------------- -------------------- -------------------- 
1           香蕉                   超市                   张                    上海
2           香蕉                   超市                   王                    上海
3           香蕉                   超市                   赵                    上海
4           香蕉                   超市                   黄                    上海
5           苹果                   批发站                  孙                    上海
6           苹果                   批发站                  潘                    上海
7           苹果                   批发站                  刘                    上海
8           苹果                   批发站                  顾                    上海
13          葡萄                   零售小贩                 钱                    天津
14          黄桃                   零售小贩                 钱                    天津
15          西瓜                   零售小贩                 钱                    天津（所影响的行数为 11 行）