如果每天能采集一遍店铺的商品列表,那么我们可以使用SQL语句的EXCEPT子语句,从今日采集到的商品数据中,排除掉以前采集到的商品数据,剩下的就是今日新品.
从店铺监控面板,登录淘宝数据库,点击"今日新品"菜单.
在云端淘宝数据库的SQL查询界面,点击"运行".
查询结果如下所示,按销量排序.
完整的SQL语句如下所示.
SELECT
*
FROM
(
SELECT
*
FROM
shoplistitem
WHERE
(clientid={{clientid}})
AND (date < toString(today() + 1)
and date >= toString(today()))
) AS todaydata
INNER JOIN (
SELECT
distinct itemid
FROM
shoplistitem
WHERE
(clientid={{clientid}})
AND (date < toString(today() + 1)
and date >= toString(today()))
EXCEPT
SELECT
distinct itemid
FROM
shoplistitem
WHERE
(clientid={{clientid}})
AND (date < toString(today())
and date >= toString(today() - 30))
) AS newitem ON
todaydata.itemid = newitem.itemid
ORDER BY sold desc
SQL EXCEPT子句/操作符用于合并两个SELECT语句,并从那些没有被第二个SELECT语句返回的第一个SELECT语句返回行。这意味着EXCEPT仅返回行,在第二个SELECT语句不可用。
正如使用UNION操作,同样的规则时,使用EXCEPT操作符适用。MySQL不支持EXCEPT运算符。
语法
EXCEPT基本的语法如下:
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
EXCEPT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
在这里,给定的条件可能是根据您的需要给定任意的表达。
例子:
考虑下面的两个表中,(a)CUSTOMERS表如下:
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
(b)另一个ORDERS表如下:
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在,让我们在SELECT语句中加入这两个表如下:
SQL> SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
EXCEPT
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
这将产生以下结果:
+----+---------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+---------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+----+---------+--------+---------------------+