22,209
社区成员
发帖
与我相关
我的任务
分享
--1. 建立临时表
CREATE TEMPORARY TABLE tmp_table
SELECT a.custid
FROM g_customer_tb a
WHERE a.shopid IN (5, 7, 18, 29, 47, 77, 78, 86, 87, 88, 196, 197, 198, 199)
AND (a.size1 = 1 OR a.size2 = 1 OR a.size3 = 1);
CREATE INDEX ix_tmp_table ON tmp_table(custid);
--2. 连接查询
SELECT a.custid
FROM tmp_table a JOIN g_customer_tbdata b ON a.custid = b.custid
WHERE b.rq > '2016-01-29 00:00:00 ' AND b.rq < '2018-01-27 23:59:59'
GROUP BY b.custid
HAVING COUNT(b.id) = 1;
--3. 删除临时表;
DROP TABLE tmp_table;
如果快不了, 就分页吧。[/quote]
临时表没有设置字段和数据是怎么查出来的--1. 建立临时表
CREATE TEMPORARY TABLE tmp_table
SELECT a.custid
FROM g_customer_tb a
WHERE a.shopid IN (5, 7, 18, 29, 47, 77, 78, 86, 87, 88, 196, 197, 198, 199)
AND (a.size1 = 1 OR a.size2 = 1 OR a.size3 = 1);
CREATE INDEX ix_tmp_table ON tmp_table(custid);
--2. 连接查询
SELECT a.custid
FROM tmp_table a JOIN g_customer_tbdata b ON a.custid = b.custid
WHERE b.rq > '2016-01-29 00:00:00 ' AND b.rq < '2018-01-27 23:59:59'
GROUP BY b.custid
HAVING COUNT(b.id) = 1;
--3. 删除临时表;
DROP TABLE tmp_table;
--1. 建立临时表
CREATE TEMPORARY TABLE tmp_table
SELECT a.custid
FROM g_customer_tb a
WHERE a.shopid IN (5, 7, 18, 29, 47, 77, 78, 86, 87, 88, 196, 197, 198, 199)
AND (a.size1 = 1 OR a.size2 = 1 OR a.size3 = 1);
CREATE INDEX ix_tmp_table ON tmp_table(custid);
--2. 连接查询
SELECT a.custid
FROM tmp_table a JOIN g_customer_tbdata b ON a.custid = b.custid
WHERE b.rq > '2016-01-29 00:00:00 ' AND b.rq < '2018-01-27 23:59:59'
GROUP BY b.custid
HAVING COUNT(b.id) = 1;
--3. 删除临时表;
DROP TABLE tmp_table;
如果快不了, 就分页吧。