|Thank you for reply.
SQl lacks stepwise computation ablility, so It is not a ideal solution. What I mean about stepwise is:
1. In excel, I can write number "10" in A1 cell, then formula "=A1*5" in A2 cell. then formula "=A2+2" in A3 cell. A1->A2->A3, It's stepwise.
2. When I change 10 to 9 in A1, then result in A2 and A3 will changed automatic.
3. an example: a. to select out the 10 categories of best sellers b. as a further computation on the basis of result from a., to select out the top 20 products from each category, c. as a further comparison with that of the last year based on the result from a.
let's call SQL_A,SQL_B,SQL_C as SQL statements for a,b,c. when I changed SQL_A (for example change 10 categories to 9 categories ), I must change SQL_B and SQL_C. why? because SQL_B and SQL_C is based on SQL_A. that is, SQL_B may like:
with SQL_A as A
Excel has another problem, It is too simple to process mass data analysis. for example, to compute the product whose annual sales values are all among the top 100.
data structure( sales table's fields): productID, time, value .
the sql solution is:
WITH sales1 AS (
SELECT productID, YEAR(time) AS year, SUM(value) AS value1
GROUP BY productID, YEAR(time)
SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC) rankorder
FROM sales1 ) T1
WHERE rankorder<=100) T2
GROUP BY productID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1)
above code is hard to write in Excel.
So what I want is some like SQL + Excel, do you have some idea?