22,207
社区成员
发帖
与我相关
我的任务
分享
/*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 行)*/
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
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 行受影响)
*/
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 行受影响)
*/