必须满足如下条件的报亭，才能被选择：
2667和48632的平均net_sold_qty大于2（两个都大于2） 1107的平均net_sold_qty大于5
建表语句：

点击(此处)折叠或打开

CREATE TABLE Titles
(product_id INTEGER NOT NULL PRIMARY KEY,
magazine_sku INTEGER NOT NULL,
issn INTEGER NOT NULL,
issn_year INTEGER NOT NULL);
CREATE TABLE Newsstands
(stand_nbr INTEGER NOT NULL PRIMARY KEY,
stand_name CHAR(20) NOT NULL);
CREATE TABLE Sales
(product_id INTEGER NOT NULL REFERENCES Titles(product_id),
stand_nbr INTEGER NOT NULL /*REFERENCES Newsstands(stand_nbr)*/,
net_sold_qty INTEGER NOT NULL,
PRIMARY KEY(product_id, stand_nbr));
INSERT INTO titles
SELECT 1,12345,1,2006 FROM dual UNION ALL
SELECT 2,2667,1,2006 FROM dual UNION ALL
SELECT 3,48632,1,2006 FROM dual UNION ALL
SELECT 4,1107,1,2006 FROM dual UNION ALL
SELECT 5,12345,2,2006 FROM dual UNION ALL
SELECT 6,2667,2,2006 FROM dual UNION ALL
SELECT 7,48632,2,2006 FROM dual UNION ALL
SELECT 8,1107,2,2006 FROM dual;
INSERT INTO sales
SELECT 1,1,1 FROM dual UNION ALL
SELECT 2,1,4 FROM dual UNION ALL
SELECT 3,1,1 FROM dual UNION ALL
SELECT 4,1,1 FROM dual UNION ALL
SELECT 5,1,1 FROM dual UNION ALL
SELECT 6,1,2 FROM dual UNION ALL
SELECT 7,1,1 FROM dual UNION ALL
SELECT 4,2,5 FROM dual UNION ALL
SELECT 8,2,6 FROM dual UNION ALL
SELECT 3,2,1 FROM dual UNION ALL
SELECT 1,3,1 FROM dual UNION ALL
SELECT 2,3,3 FROM dual UNION ALL
SELECT 4,3,1 FROM dual UNION ALL
SELECT 5,3,1 FROM dual UNION ALL
SELECT 6,3,3 FROM dual UNION ALL
SELECT 7,3,3 FROM dual UNION ALL
SELECT 1,4,1 FROM dual UNION ALL
SELECT 2,4,1 FROM dual UNION ALL
SELECT 3,4,4 FROM dual UNION ALL
SELECT 4,4,1 FROM dual UNION ALL
SELECT 5,4,1 FROM dual UNION ALL
SELECT 6,4,1 FROM dual UNION ALL
SELECT 7,4,2 FROM dual;
INSERT INTO Newsstands
SELECT DISTINCT stand_nbr,stand_nbr
FROM sales;

1、将行结果转换成列结果避免在where里面写多个子查询的方法：

点击(此处)折叠或打开

select *
from (select B.stand_name,
sum(nvl(B.s1, 0)) as s1,
sum(nvl(B.s2, 0)) as s2,
sum(nvl(B.s3, 0)) as s3
from (select A.stand_name,
(case A.magazine_sku
when 1107 then
A.avg_sj
end) as s1,
(case A.magazine_sku
when 48632 then
A.avg_sj
end) as s2,
(case A.magazine_sku
when 2667 then
A.avg_sj
end) as s3
from (select ns.stand_name,
tt.magazine_sku,
avg(ss.net_sold_qty) as avg_sj
from sales ss, titles tt, newsstands ns
where ns.stand_nbr = ss.stand_nbr
and tt.product_id = ss.product_id
and tt.magazine_sku in (2667, 48632, 1107)
group by ns.stand_name, tt.magazine_sku
order by ns.stand_name) A) B
group by B.stand_name)
where s1 > 5
or (s2 > 2 and s3 > 2)

ITPub博客

关于按销售量报刊亭的选择1

最新文章