MySQL,MSSQL TEMPORARY TABLE

MYSQL 

CREATE TEMPORARY TABLE date_list (
        oc_date date NOT NULL
);
INSERT INTO date_list (oc_date ) values('2014-08-26');
INSERT INTO date_list (oc_date ) values('2014-08-27');
INSERT INTO date_list (oc_date ) values('2014-08-28');
INSERT INTO date_list (oc_date ) values('2014-08-29');

SELECT * FROM date_list;

MSSQL

CREATE TABLE #date_list (
        oc_date date NOT NULL
);
INSERT INTO #date_list (oc_date ) values('2014-08-26');
INSERT INTO #date_list (oc_date ) values('2014-08-27');
INSERT INTO #date_list (oc_date ) values('2014-08-28');
INSERT INTO #date_list (oc_date ) values('2014-08-29');

SELECT * FROM #date_list;

DROP TABLE #date_list;

SQL INDEX

create index log_id on log_records(id);

create index log_dt_id on log_records(datetime);

create index log_rrdt_id on relay_records(datetime);

create index log_crdt_id on card_records(datetime);

SQL 隨機取一筆

SQL Server 2005

SELECT TOP 1 Field1, …, FieldN
FROM Table1
ORDER BY NEWID()

Access

SELECT TOP 1 Field1 , …, FieldN
FROM Table1
ORDER BY Rnd(Field1)

MySQL

SELECT Field1, …, FieldN
FROM Table1
ORDER BY RAND()
LIMIT 1

Oracle 10g Express

SELECT Field1, …, FieldN
FROM ( SELECT Field1, …, FieldN
FROM Table1
ORDER BY dbms_random.value)
WHERE rownum <= 1

SQLite 3

SELECT Field1, …, Field2
FROM Table1
ORDER BY Random()
LIMIT 1

PostgreSQL 8.3

SELECT “Field1″, “…”, “FieldN”
FROM “Table1″
ORDER BY RANDOM()
LIMIT 1

SQL IF ELSE

if not exists (select TOP(1) * from test_result_list where sub_cls_id ='C0000001-001' AND empno = '00000046')

insert into test_result_list (sub_cls_id,empno,is_pass,point) values('C0000001-001','00000046',0,12)

else

update test_result_list set sub_cls_id = 'C0000001-001',empno='00000046',is_pass=1,point=70 where sub_cls_id = 'C0000001-001' AND empno='00000046'

MySQL 日期

datetime('ColName','-1 day')日期減一天

MySQL datediff(date1,date2)：取得date1 - date2 的天數

select datediff('2008-08-08', '2008-08-01');結果: -7

select datediff('2008-08-01', '2008-08-08');結果: 7

MySQL timediff(datetime1,datetime2)：取得datetime1 - datetime2 的天數

select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00');結果:08:08:08

select timediff('08:08:08', '00:00:00');結果:08:08:08

MSSQL

time 型態 增加分鐘 DATEADD(MINUTE, @MinutesToAdd, @StartTime);

datetime 型態 增加分鐘 DATEADD(MINUTE, @StartTime, @MinutesToAdd);