Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2006
    Posts
    38

    Unanswered: How to convert the SQL from Mysql to SqlServer

    the below SQL can be run in Mysql
    Code:
    select distinct(entryinfo.entryid),entryinfo.columnid,entryinfo.entryname,entryinfo.entryaddr,entryinfo.starttime,entryinfo.finishtime 
    from entryinfo,entryauditbaseplaytimeinfo 
    where entryauditbaseplaytimeinfo.entryid=entryinfo.entryid and entryauditbaseplaytimeinfo.editoverflag='1' 
    and (entryauditbaseplaytimeinfo.auditingFlag='-1' or entryauditbaseplaytimeinfo.artauditingflag='-1') l
    imit ?,?;
    but in MSSQL,it can't,So How to convert?

  2. #2
    Join Date
    Feb 2006
    Posts
    38
    I create a view for select,and change the limit
    but the SQL is not correct,who can tell me why?
    Code:
    CREATE VIEW dbo.selectEntryInfo_view
    AS
    SELECT DISTINCT 
          appsdb.entryinfo.entryid, appsdb.entryinfo.columnid, appsdb.entryinfo.entryname, 
          appsdb.entryinfo.entryaddr, appsdb.entryinfo.starttime, 
          appsdb.entryinfo.finishtime
    FROM appsdb.entryinfo INNER JOIN
          appsdb.entryauditbaseplaytimeinfo ON 
          appsdb.entryinfo.entryid = appsdb.entryauditbaseplaytimeinfo.entryid
    WHERE (appsdb.entryauditbaseplaytimeinfo.editoverflag = '1') AND 
          (appsdb.entryauditbaseplaytimeinfo.auditingFlag = '-1') OR
          (appsdb.entryauditbaseplaytimeinfo.editoverflag = '1') AND 
          (appsdb.entryauditbaseplaytimeinfo.artauditingflag = '-1')

    Code:
    SELECT TOP ? * FROM selectEntryInfo_view WHERE(entryid NOT IN (SELECT TOP ? entryid FROM selectEntryInfo_view order by entryid))order by entryid;

  3. #3
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by kingxiaobao
    I create a view for select,and change the limit
    but the SQL is not correct,who can tell me why?
    Code:
    CREATE VIEW dbo.selectEntryInfo_view
    AS
    SELECT DISTINCT 
          appsdb.entryinfo.entryid, appsdb.entryinfo.columnid, appsdb.entryinfo.entryname, 
          appsdb.entryinfo.entryaddr, appsdb.entryinfo.starttime, 
          appsdb.entryinfo.finishtime
    FROM appsdb.entryinfo INNER JOIN
          appsdb.entryauditbaseplaytimeinfo ON 
          appsdb.entryinfo.entryid = appsdb.entryauditbaseplaytimeinfo.entryid
    WHERE (appsdb.entryauditbaseplaytimeinfo.editoverflag = '1') AND 
          (appsdb.entryauditbaseplaytimeinfo.auditingFlag = '-1') OR
          (appsdb.entryauditbaseplaytimeinfo.editoverflag = '1') AND 
          (appsdb.entryauditbaseplaytimeinfo.artauditingflag = '-1')

    Code:
    SELECT TOP ? * FROM selectEntryInfo_view WHERE(entryid NOT IN (SELECT TOP ? entryid FROM selectEntryInfo_view order by entryid))order by entryid;
    u have to specify value instead of '?'.see below example

    Code:
    use pubs
    go
    create view vauthors
    as
     select  distinct city from authors
    
    select top 5 city from vauthors where city not in (select top 5 city from vauthors order by city)
    order by city
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Feb 2006
    Posts
    38
    ok,thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •