我从几个表创建了这个视图。每个员工都能在所有商店工作。每个发票可以有一个或多个产品。我要显示的内容,商店,员工,单个产品的总销售额,总交易额(即总发票数),总发票。
这是一个示例表:
CREATE TABLE PerformanceView
([Store] varchar(6), [Employee] varchar(5), [Product] varchar(8), [ProductPrice] int,[InvoiceNo] int, [InvoiceTotal] int);
INSERT INTO PerformanceView
([Store], [Employee], [Product], [ProductPrice], [InvoiceNo], [InvoiceTotal])
VALUES
('Store1', 'Empl1', 'Product1', 577.2, 42152, 676.2),
('Store1', 'Empl1', 'Product1', 99, 42152, 676.2),
('Store1', 'Empl1', 'Product2', 20, 41969, 20),
('Store1', 'Empl1', 'Product2', 25, 41649, 25),
('Store1', 'Empl2', 'Product2', 300, 43112, 300),
('Store1', 'Empl2', 'Product2', 450, 42440, 450),
('Store1', 'Empl2', 'Product3', 226, 41666, 2040.14),
('Store1', 'Empl2', 'Product3', 282.17, 42232, 1678.5),
('Store1', 'Empl2', 'Product3', 934.67, 41666, 2040.14),
('Store1', 'Empl2', 'Product3', 1264.56, 42232, 1678.5),
('Store1', 'Empl2', 'Product4', 49.81, 41666, 2040.14),
('Store1', 'Empl2', 'Product4', 683.32, 41666, 2040.14),
('Store1', 'Empl2', 'Product4', 0, 42232, 1678.5),
('Store1', 'Empl2', 'Product4', 81.92, 42232, 1678.5),
('Store1', 'Empl2', 'Product4', 146.34, 41666, 2040.14),
('Store1', 'Empl2', 'Product4', 49.85, 42232, 1678.5),
('Store1', 'Empl2', 'Product5', 30, 43110, 30),
('Store1', 'Empl3', 'Product1', 172.5, 42875, 172.5),
('Store3', 'Empl4', 'Product2', 51, 42801, 51),
('Store3', 'Empl4', 'Product2', 51, 42488, 51),
('Store3', 'Empl4', 'Product2', 80.002, 42463, 80.002),
('Store3', 'Empl4', 'Product2', 22, 42559, 22),
('Store3', 'Empl4', 'Product2', 20, 42963, 20),
('Store3', 'Empl4', 'Product2', 35, 42436, 35),
('Store3', 'Empl4', 'Product2', 107, 43014, 107),
('Store3', 'Empl4', 'Product2', 59, 42615, 59),
('Store3', 'Empl4', 'Product2', 110, 42025, 110),
('Store3', 'Empl4', 'Product2', 89, 42137, 148),
('Store3', 'Empl4', 'Product2', 50, 42565, 100),
('Store3', 'Empl4', 'Product2', 59, 42137, 148),
('Store3', 'Empl4', 'Product5', 25, 42565, 100),
('Store3', 'Empl4', 'Product5', 25, 42565, 100),
('Store3', 'Empl5', 'Product2', 22, 42295, 44),
('Store3', 'Empl5', 'Product2', 22, 42295, 44),
('Store3', 'Empl5', 'Product2', 439, 41723, 439),
('Store3', 'Empl5', 'Product2', 197, 42267, 197),
('Store3', 'Empl5', 'Product2', 150, 41780, 150),
('Store3', 'Empl5', 'Product2', 539, 42280, 539),
('Store3', 'Empl5', 'Product2', 63, 41689, 63),
('Store3', 'Empl5', 'Product2', 269, 42274, 269),
('Store3', 'Empl5', 'Product2', 134, 41731, 134),
('Store2', 'Empl3', 'Product1', 0, 43076, 0),
('Store2', 'Empl3', 'Product1', 149.49, 42557, 666.73),
('Store2', 'Empl3', 'Product1', 0, 43076, 0),
('Store2', 'Empl3', 'Product1', 0, 42917, 0),
('Store2', 'Empl3', 'Product1', 61.01, 42557, 666.73),
('Store2', 'Empl3', 'Product1', 389.4, 42557, 666.73),
('Store2', 'Empl3', 'Product1', 66.84, 42557, 666.73),
('Store2', 'Empl3', 'Product1', 0, 43076, 0),
('Store2', 'Empl3', 'Product1', 15.42, 41650, 15.42),
('Store2', 'Empl3', 'Product1', 0, 43088, 0),
('Store2', 'Empl3', 'Product1', 0, 42920, 0),
('Store2', 'Empl3', 'Product6', 0, 43076, 0);
我已经试过了这段代码;
select
STORE,
EMPLOYEE,
PRODUCT,
SUM(ProductPrice) as ProductTotal ,
SUM(InvoiceTotal) as InvoiceTotal,
Count(Distinct InvoiceNo) as Transactions
from
PerformanceView
GROUP BY STORE,EMPLOYEE,PRODUCT
但是发票总额是错误的,因为它具有相同的发票总和。
您可以在此处进行检查http://sqlfiddle.com/#!3/e419f/6
我已经尝试过over子句,但是它没有给出相同的视图。
您的问题是,您的视图只保存一次产品销售(每销售一件产品一行),但是您还有多余的发票发票金额。在按商店,员工和产品分组时,您可以对发票金额进行数倍的计算。因此,您必须首先按商店,员工,产品和发票分组,然后才能进入最后一组:
select
store,
employee,
product,
sum(producttotal) as producttotal,
sum(invoicetotal) as invoicetotal,
count(*) as transactions
from
(
select
store,
employee,
product,
sum(productprice) as producttotal,
min(invoicetotal) as invoicetotal
from performanceview
group by store, employee, product, invoiceno
) as pre_aggregate
group by store, employee, product;
这是您的SQL提琴:http : //sqlfiddle.com/# !3/ e419f/ 32。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句