>我有以下 SQL 语句,显示至少完成 2 个订单的城市客户的总销售额。但是假设我只想显示某人至少完成了 2 个订单的城市,并且有两个客户居住在这个城市/城市,所以我想做的是挑选出一个客户至少下了 2 个订单的城市,然后显示居住在这个城市的两个客户的总销售额, 即使另一个客户只下了一个订单,如何完成,是否应该有一个比较报表,用于 COUNT-操作,以便能够显示城市中所有客户的销售额,如果是这样 - 如何说明?
SELECT c.CityName, SUM(p.Price * o2.Orderquantity) AS 'TotalSalesAmount'
FROM Customers c, Order1 o1, Orderrader o2, Products p,
(SELECT o1.CustomerNr
FROM Order1 o1
GROUP BY o1.CustomerNr
HAVING COUNT(o1.CustomerNr) >= 2)
AS a WHERE c.CustomerNr = a.CustomerNr AND c.CustomerNr = o1.CustomerNr
AND o1.Ordernr = o2.Ordernr AND o2.Productnr = p.Productnr
GROUP BY c.CityName
我使用的表的结构如下所示:
'Customers' has the columns: CustomerNr, City Name
'Order1' has columns: Ordernr, CustomerNr
'Order2' has columns: Ordernr, Productnr, Order quantity
'Products' has columns: ProductNr, Price
示例数据:"客户"表的示例数据:
- CustomerNr CityName:
- 01109 New York
- 01999 Los Angeles
- 20090 New York
"订单 1"的示例数据:
- Ordernr CustomerNr
- 1306 01109
- 1307 01109
- 1308 20090
"订单 2"的示例数据:
- OrderNr ProductNr Order quantity:
- 1306 15-116 3
- 1306 46-701 2
- 1307 15-96 1
- 1308 17-91 1
(等等...
"产品"的示例数据:
- ProductNr Price:
- 15-116 44.00
- 15-96 28.50
- 46-701 3000.00
- 17-91 200.00
等。。。
根据上面的 SQL 语句和示例数据,我想要的预期结果是:
- CityName TotalSalesAmount:
- New York 6360.50
尝试使用子查询。我知道这看起来有点讨厌,但一定有效。
事实证明,您在 Order1 示例中为同一客户 Nr 提供了重复的 Ordernr (1306)。我假设您的真实数据可能并非如此。
SELECT c.CityName,
(Select SUM(order2.quantity * products.Price) from order1
INNER JOIN Customers On Customers.CustomerNr=order1.CustomerNr
INNER JOIN Order2 ON Order2.Ordernr=Order1.Ordernr
INNER JOIN Products ON Products.ProductNr=Order2.ProductNr
WHERE Customers.CityName=c.CityName) AS 'TotalSalesAmount'
FROM Order1 o1
INNER JOIN (SELECT o1.CustomerNr
FROM Order1 o1
GROUP BY o1.CustomerNr
HAVING COUNT(o1.CustomerNr) >= 2
) AS a ON o1.CustomerNr = a.CustomerNr
INNER JOIN Order2 o2 ON o1.Ordernr = o2.Ordernr
INNER JOIN Customers c ON o1.CustomerNr = c.CustomerNr
INNER JOIN Products P ON o2.ProductNr = P.ProductNr
GROUP BY c.CityName;
试一试。
SELECT CityName, SUM(quantity * Price) AS TotalSalesAmount
FROM (
SELECT c.CityName, p.ProductNr, b.quantity, p.Price, (b.quantity * p.Price) as total --, SUM(b.quantity * p.Price) AS TotalSalesAmount
FROM customers c
INNER JOIN order1 a ON c.CustomerNr = a.CustomerNr
INNER JOIN order2 b ON a.Ordernr = b.Ordernr
INNER JOIN products p ON b.Productnr = p.ProductNr
GROUP BY c.CityName, p.ProductNr, b.quantity, p.Price
) AS cust
GROUP BY CityName
HAVING COUNT(cityname) >= 2
试试这个。
SELECT c.CityName,
Sum(o2.Orderquantity * p.Price) Total_sale
FROM Customers C
JOIN (SELECT o.CustomerNr
FROM Order1 o
GROUP BY o.CustomerNr
HAVING Count(o.CustomerNr) >= 2) su
ON c.CustomerNr = su.CustomerNr
JOIN Customers c1
ON c1.CityName = c.CityName
JOIN Order1 o1
ON o1.CustomerNr = c1.CustomerNr
JOIN Order2 o2
ON o2.Ordernr = o1.Ordernr
JOIN Products P
ON o2.ProductNr = P.ProductNr
GROUP BY c.CityName
问题解释为"对于单个客户下多个订单的任何城市,我需要按城市划分的所有销售额的总和。
select
c.City,
SUM(p.Price * o2.Orderquantity) AS 'TotalSalesAmount'
from
(select c.City
from @t_Customers c
inner join @t_Order1 o1
on o1.CustomerNr = c.CustomerNr
group by c.City
having count(c.City) > 1) as ct
inner join @t_Customers c
on c.City = ct.City
inner join @t_Order1 o1
on o1.CustomerNr = c.CustomerNr
inner join @t_Order2 o2
on o2.OrderNr = o1.Ordernr
inner join @t_Products p
on p.ProductNr = o2.ProductNr
group by c.City
select ci.city_name,pr.product_name,round(sum(ii.line_total_price),2) as tot
from city ci ,customer cu,invoice i,invoice_item ii,product pr where ci.id=cu.city_id and cu.id=i.customer_id and i.id=ii.invoice_id and ii.product_id=pr.id
group by ci.city_name,pr.product_name
order by tot desc,ci.city_name, pr.product_name