27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE TEST
(
CUSTOMER INT NOT NULL,
RECNO INT NOT NULL,
PRODUCT VARCHAR(20)
);
GO
INSERT INTO TEST VALUES
----数据在这里http://pan.baidu.com/s/1mg0qgBm
GO
----查询6种产品组合被多少客户购买过
;WITH T AS
(
SELECT DISTINCT PRODUCT FROM TEST
),
A AS
(
SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),
T1.PRODUCT AS PRO1,T2.PRODUCT AS PRO2,T3.PRODUCT AS PRO3,T4.PRODUCT AS PRO4,T5.PRODUCT AS PRO5,T6.PRODUCT AS PRO6
FROM T AS T1
JOIN T AS T2
ON T1.PRODUCT < T2.PRODUCT
JOIN T AS T3
ON T2.PRODUCT < T3.PRODUCT
JOIN T AS T4
ON T3.PRODUCT < T4.PRODUCT
JOIN T AS T5
ON T4.PRODUCT < T5.PRODUCT
JOIN T AS T6
ON T5.PRODUCT < T6.PRODUCT
),
B AS
(
SELECT ID,PRODUCT FROM A
UNPIVOT(PRODUCT FOR PRO IN(PRO1,PRO2,PRO3,PRO4,PRO5,PRO6))P
),
C AS
(
SELECT TEST.CUSTOMER,B.ID,B.PRODUCT
FROM TEST
JOIN B
ON TEST.PRODUCT = B.PRODUCT
GROUP BY TEST.CUSTOMER,B.ID,B.PRODUCT
HAVING COUNT(TEST.RECNO)=6
),
D AS
(
SELECT ID,PRODUCT,COUNT(CUSTOMER) AS CT
FROM C
GROUP BY ID,PRODUCT
)
SELECT TOP 10 * FROM D ORDER BY CT DESC
;WITH T AS
(
SELECT DISTINCT PRODUCT FROM TEST
),
A AS
(
SELECT ID=ROW_NUMBER()OVER(ORDER BY GETDATE()),
T1.PRODUCT AS PRO1,T2.PRODUCT AS PRO2,T3.PRODUCT AS PRO3,T4.PRODUCT AS PRO4,T5.PRODUCT AS PRO5,T6.PRODUCT AS PRO6
FROM T AS T1
JOIN T AS T2
ON T1.PRODUCT < T2.PRODUCT
JOIN T AS T3
ON T2.PRODUCT < T3.PRODUCT
JOIN T AS T4
ON T3.PRODUCT < T4.PRODUCT
JOIN T AS T5
ON T4.PRODUCT < T5.PRODUCT
JOIN T AS T6
ON T5.PRODUCT < T6.PRODUCT
),
B AS
(
SELECT ID,PRODUCT FROM A
UNPIVOT(PRODUCT FOR PRO IN(PRO1,PRO2,PRO3,PRO4,PRO5,PRO6))P
)
SELECT * INTO C FROM B
CREATE INDEX INDEX_C ON C(PRODUCT)
CREATE INDEX INDEX_T ON TEST(PRODUCT)
;WITH D AS
(
SELECT TEST.CUSTOMER,C.ID
FROM TEST
JOIN C
ON TEST.PRODUCT = C.PRODUCT
GROUP BY TEST.CUSTOMER,C.ID
HAVING COUNT(TEST.RECNO)=6
),
E AS
(
SELECT ID,COUNT(CUSTOMER) AS CT
FROM D
GROUP BY ID
)
SELECT TOP 10 C.*,E.CT FROM C JOIN E ON C.ID=E.ID ORDER BY CT DESC
应该创建哪种索引?