Sample data volume 1.5 million, table is as follows:
Sales order goods

20190101 A

20190101 B

20190101 C

20190102 A

20190102 C

20190103 C

20190103 D

I wrote a SQL, data can also be less, but also very slow, the data quantity of stuck directly, ask the great spirit show

SELECT commodity code 1 y., y. commodity code 2, COUNT (DISTINCT y1. The sales order) as js

The FROM (

Commodity code as commodity code 1, SELECT a. b. commodity code as commodity code 2

The FROM (SELECT DISTINCT commodity code FROM sales table) A,

(SELECT DISTINCT commodity code FROM sales table) B

WHERE A commodity code & lt; B. commodity code

) y INNER JOIN sales table y1 ON y1. The h.s.code=y. The commodity code 1

WHERE E sales table ISTS (SELECT * FROM sales table y2 WHERE y2. The sales order number=y1. The sales order AND y2, commodity code=y. commodity code 2)

GROUP BY commodity code 1 y., y. commodity code 2;

CodePudding user response:

There is distinct must be slow

CodePudding user response:

According to your sample, hope to get the result of what?

CodePudding user response:

Look from the sample table A and C in the same goods sales is the number on the list of the most

CodePudding user response:

Didn't understand, is to look at most times in total, or the most is the number of times in the same sales list?

Look from your example, biggest total C, but your reply is A and C?

reference sbeg571 reply: 3/f

look from the sample table A and C in the same commodity sales is the number on the list of the most

CodePudding user response:

Didn't understand, but slow because the

WHERE A commodity code & lt; B. commodity code

0.5 * n * n the amount of data, must be stuck

CodePudding user response:

Write so I don't have your watch, do not know the result, isn't that right,
Above all, want to assume you don't have 2 in the same sales order or more of the same goods,
Next, I returned to content, it is the same in the sales order, 2 items and corresponding occurrences, according to the reverse arrangement,

SELECT
T1. Commodity code,
T2. Commodity code,
COUNT (0)
The FROM
Sales table t1,
Sales table t2
WHERE a t1. The sales order number=t2. Sales order
AND t1. Hs code & gt; T2. Commodity code
GROUP BY t1. Commodity code,
T2. Commodity code
The ORDER BY DESC;

CodePudding user response:

Add that need to be indexed, the sales number + commodity code combination index, order don't wrong,

CodePudding user response:

You see if this is the statement you want

 
The select Max (num) 
The from (
Select count (goods) as num, commodity 
The from tableName groupby commodity 
) 

CodePudding user response:

Appears at the same time want to find A sales in the list of goods, from the sample table A and C on the goods at the same time in the same sales is the number on the list of the most, because the two sales in 20190101 and 20190102 have goods A and C,

CodePudding user response:

At the same time, this concept is not clear, at the same time a few goods? A count? 2 or 3 or 4, not clear rules can't write code

references 9 f sbeg571 response:

appears at the same time want to find A sales in the list of goods, from the sample table A and C on the goods at the same time in the same sales is the number on the list of the most, because the two sales in 20190101 and 20190102 A and C are commodities,