爱悠闲 > 数据库常用写法,笔记难的SQL写法

数据库常用写法,笔记难的SQL写法

分类: 数据库  |  标签: 数据库,sqlserver,报表,table,存储,sql  |  作者: lan861698789 相关  |  发布日期 : 2013-07-07  |  热度 : 422°
 
1、sqlserver 中建立临时表存储数据,然后查询
  
select 种类,编号,时间,内容,创建时间,
          into TableBak
    from dbo.SLottery
    where 种类='3'
        order by ctime
   select *
   from TableBak
   drop table TableBak ;  //这是一条SQL语句。

2、将行变成列。(去看看以前在银行做报表时候海波龙的列子)
 

代码

SELECT STDNAME,
       (CASE
         WHEN STDSUBJECT = '语文' THEN
          GRADE
       END) AS '语文',
       (CASE
          WHEN STDSUBJECT = '数学' THEN
           GRADE
        END) AS '数学' ,(CASE
         WHEN STDSUBJECT = '化学' THEN
          GRADE
       END) AS '化学' ,(CASE
         WHEN STDSUBJECT = '物理' THEN
          GRADE
       END) AS '物理'
  FROM TEMP_SC group by stdname;
  
----行列互换
select t.* from temp_sc t;
select sc.stdname, a.grade 语文, b.grade 数学, c.grade 物理, d.grade 化学
  from (select distinct stdname from temp_sc) sc,
       (select stdname, grade from temp_sc where stdsubject = '语文') a,
       (select stdname, grade from temp_sc where stdsubject = '数学') b,
       (select stdname, grade from temp_sc where stdsubject = '物理') c,
       (select stdname, grade from temp_sc where stdsubject = '化学') d
 where sc.stdname = a.stdname
   and sc.stdname = b.stdname
   and sc.stdname = c.stdname
   and sc.stdname = d.stdname;
 
 
3、插入的方法
INSERT INTO COUNTER (NAME,CURRENTID) SELECT 'com.endplay.feeds.model.Schedule', NVL(MAX(SCHEDULE_ID),0)  FROM SCHEDULE;

4、难点 :需要update多个数据,而这些符合条件的数据需要like出来。
     方法 :先查出需要update的记录,拼装成为带%的字段,然后做为一个表。
update QRTZ_SIMPLE_TRIGGERS QST set REPEAT_COUNT = 0 where EXISTS (  
  select * from (select 'TRIGGER_' || groupId || '%' as triggers_name  from group_ where liveGroupid = 0)  GP  where QST.TRIGGER_NAME like GP.triggers_name  
);