\u672c\u6587\u603b\u7ed3\u4e00\u4e0bPresto SQL\u7684\u4f18\u5316\u5c0f\u6280\u5de7\uff0c\u7b80\u5316\u4ee3\u7801\u6216\u8005\u63d0\u9ad8\u6267\u884c\u6548\u7387\u3002<\/p>\n\n\n\n
\u5728\u505a\u5173\u8054\u5224\u65ad\u6216\u8005\u6253\u6807\u7b7e\u7684\u65f6\u5019\uff0c\u9700\u8981\u5d4c\u5957\u5b50\u67e5\u8be2\u6216\u8005\u521b\u5efa\u4e2d\u95f4\u8868\uff0c\u6b64\u65f6\u53ef\u4ee5\u8003\u8651\u4f7f\u7528with\u8bed\u53e5\u907f\u514d\u5d4c\u5957\u5b50\u67e5\u8be2\u6216\u8005\u521b\u5efa\u8fc7\u591a\u7684\u8fc7\u7a0b\u8868\u3002<\/p>\n\n\n\n
with \n t1 as (\n select\n cust_id\n from loan\n where apply_date < current_date and apply_date >= current_date - interval '30' day\n group by 1\n )\nselect\n t2.cust_id,\n case when t1.cust_id is not null then 1 else 0 end as is_apply_30d\nfrom detail as t2\nleft join t1\non t2.cust_id = t1.cust_id;<\/pre>\n\n\n\nwith\n a as (select cust_id from table1 where ...),\n b as (select cust_id from table2 where ...)\nselect\n ...\nfrom table3 as t\nleft join a\non t.cust_id = a.cust_id\nleft join b\non t.cust_id = b.cust_id;<\/pre>\n\n\n\n<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
2. \u63d0\u9ad8row_number() over ()\u7684\u6267\u884c\u6548\u7387<\/h3>\n\n\n\n
\u5076\u5c14\u53ef\u80fd\u4f1a\u78b0\u5230row_number() over ()\u6267\u884c\u5f88\u6162\u751a\u81f3\u5185\u5b58\u6ea2\u51fa\u62a5\u9519\u7684\u60c5\u51b5\uff0c\u6b64\u65f6\u53ef\u4ee5\u8003\u8651\u52a0\u4e0awhere\u6761\u4ef6 or rand() < 0<\/strong> \u4ee5\u63d0\u9ad8\u6267\u884c\u6548\u7387\u3002Presto\u9ed8\u8ba4\u4f7f\u7528\u7684\u662fTopNRowNumberOperator\u6267\u884c\u5668\uff0c\u52a0\u4e0a or rand() < 0<\/strong> \u80fd\u8bf1\u5bfcPresto\u4f7f\u7528Window\u6267\u884c\u5668\uff0c\u4ece\u800c\u5728\u4e0d\u5f71\u54cd\u7ed3\u679c\u7684\u540c\u65f6\u63d0\u9ad8\u6267\u884c\u6548\u7387\u3002<\/p>\n\n\n\n
select\n cust_id,\n order_no,\n order_type,\n order_date,\n order_time\nfrom \n (\n select\n cust_id,\n order_no,\n order_type,\n order_date,\n order_time,\n row_number() over (partition by cust_id, order_date order by order_time) as row_n\n from order_detail\n ) as t\nwhere t.row_n= 1 or rand() < 0;<\/pre>\n\n\n\n<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
3. \u51cf\u5c11\u7c7b\u578b\u8f6c\u6362<\/h3>\n\n\n\n
--\u5047\u8bbeorder_detail\u8868\u4e2d\u7684order_date\u7c7b\u578b\u4e3a\u5b57\u7b26, \u5b58\u50a8\u683c\u5f0f\u4e3ayyyy-mm-dd, \u7b2c1\u79cd\u5199\u6cd5\u8981\u4f18\u4e8e\u7b2c2\u79cd\u5199\u6cd5\n\n--\u7b2c1\u79cd\u5199\u6cd5\nselect\n order_no,\n order_date\nfrom order_detail\nwhere order_date >= '2021-09-01'\n\n--\u7b2c2\u79cd\u5199\u6cd5\nselect\n order_no,\n order_date\nfrom order_detail\nwhere date(order_date) >= date'2021-09-01' --\u5b57\u6bb5order_date\u6bcf\u4e00\u4e2a\u503c\u90fd\u8981\u505a\u4e00\u6b21date()\u8f6c\u6362<\/pre>\n\n\n\n<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
4. \u4f7f\u7528join\u8bed\u53e5\u4ee3\u66ffwhere in\u548c\u5b50\u67e5\u8be2<\/h3>\n\n\n\n
\u5f53 where in ()<\/strong> \u6216 where not in ()<\/strong> \u6240\u7528\u5230\u7684\u8868\u6216\u6240\u4ea7\u751f\u7684\u7ed3\u679c\u6570\u636e\u91cf\u8f83\u5927\u65f6\uff0c\u8bed\u53e5\u6267\u884c\u6548\u7387\u5c31\u4f1a\u660e\u663e\u4e0b\u964d\uff0c\u53ef\u4ee5\u8003\u8651\u6539\u5199\u4e3a join<\/strong> \u8bed\u53e5\uff0c\u540c\u65f6\u8fd8\u53ef\u4ee5\u7ed3\u5408\u4f7f\u7528\u524d\u6587\u8bf4\u7684 with<\/strong> \u8bed\u53e5\u3002<\/p>\n\n\n\n
--where in\nselect\n ...\nfrom table1\nwhere cust_id in (select cust_id from table2);\n\n--\u53ef\u6539\u5199\u4e3a\uff1a\nselect\n ...\nfrom table1 as t1\ninner join table2 as t2\non t1.cust_id = t2.cust_id;\n\n\n--where not in\nselect\n ...\nfrom table1\nwhere cust_id not in (select cust_id from table2 where type = 'xxx');\n\n--\u53ef\u6539\u5199\u4e3a\uff1a\nselect\n ...\nfrom table1 as t1\nleft join table2 as t2\non t1.cust_id = t2.cust_id and t2.type = 'xxx'\nwhere t2.cust_id is null;<\/pre>\n\n\n\n