使用ADO从另一个表上的查询创建新表

问题描述

我需要使用ADO查询从对现有表的查询中创建一个新表.数据库是MS Access2003.是否有简单的方法可以重新创建它?

I need to CREATE a new table from a query on existing tables using ADO query.DB is MS Access 2003. Is there a simple way to recreate this?

DROP TABLE IF EXISTS tmp_report;

CREATE TABLE tmp_report  
SELECT Userid, Name,  
  DATE(CheckTime) AS date,  
  MIN(CheckTime) AS first_login,  
  MAX(checktime) AS last_login,  
  COUNT(CheckTime) AS No_logins,  
  IF(COUNT(CheckTime) = 1, 'ERROR', 
  TIME_TO_SEC(TIMEDIFF(max(checktime), min(CheckTime))) AS total_sec  
FROM 
  Checkinout LEFT JOIN Userinfo USING(Userid)  
GROUP BY 
  Userid, DATE(CheckTime)  
ORDER BY
  Userid, DATE(CheckTime);

推荐答案

要通过对现有表的查询创建新表，可以使用SELECT INTO(这将创建一个新表)或INSERT INTO SELECT(这将插入到现有表)语句.

To CREATE a new table from a query on existing tables, you can use SELECT INTO(this creates a new table) or INSERT INTO SELECT(this inserts into an existing table) statements.

检查此MSDN 页，其中有您需要的漂亮示例

Check this MSDN page, it has nice examples that you need.

这篇关于使用ADO从另一个表上的查询创建新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！