SQLite数据库函数应用（一）对表任一列为索引排升（降）序

newer · 发表于 2021-1-29 00:34:22

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

×

(defun c:tt ()
(defun chkerr (cmd)
(if (not cmd)
(progn
(princ (xdrx-sqlite-lasterr))
nil
)
t
)
)
(setq lst nil)
(repeat 10
(setq r (xdrx-math-rand 0 255)
r1 (xdrx-math-rand 1.0 255.0)
s (xdrx-string-rand 3)
lst (cons (list r s r1) lst)
)
)
(xdrx-prompt "lst=" (vl-prin1-to-string lst))
(setq db "d:\\test.db")
(if (findfile db)
(xdrx-file-delete db)
)
(chkerr (xdrx-sqlite-open db))
(chkerr
(xdrx-sqlite-dml
db
"create table mytable (num int, string char(64),float real);"
)
)
(setq i 0)
(mapcar '(lambda (x)
(chkerr (xdrx-sqlite-dml
db
(setq lxd (strcat "insert into mytable values ("
(itoa (car x))
",'"
(cadr x)
"',"
(rtos (last x) 2 2)
");"
)
)
)
)
)
lst
)
(setq lst1 (xdrx-sqlite-query
db
"select * from mytable order by string;"
)
)
(xdrx-prompt "\nlst1=" (vl-prin1-to-string (cdr lst1)))
(setq lst2 (xdrx-sqlite-query
db
"select * from mytable order by float desc;"
)
)
(xdrx-prompt "\nlst2=" (vl-prin1-to-string (cdr lst2)))
(chkerr (xdrx-sqlite-close db))
(princ)
)

(defun c:tt () 
  (defun chkerr        (cmd) 
    (if        (not cmd) 
      (progn 
        (princ (xdrx-sqlite-lasterr)) 
        nil 
      ) 
      t 
    ) 
  ) 
  (setq lst nil) 
  (repeat 10 
    (setq r   (xdrx-math-rand 0 255) 
          r1  (xdrx-math-rand 1.0 255.0) 
          s   (xdrx-string-rand 3) 
          lst (cons (list r s r1) lst) 
    ) 
  ) 
  (xdrx-prompt "lst=" (vl-prin1-to-string lst)) 
  (setq db "d:\\test.db") 
  (if (findfile db) 
    (xdrx-file-delete db) 
  ) 
  (chkerr (xdrx-sqlite-open db)) 
  (chkerr 
    (xdrx-sqlite-dml 
      db 
      "create table mytable (num int, string char(64),float real);" 
    ) 
  ) 
  (setq i 0) 
  (mapcar '(lambda (x) 
             (chkerr (xdrx-sqlite-dml 
                       db 
                       (setq lxd (strcat "insert into mytable values (" 
                                         (itoa (car x)) 
                                         ",'" 
                                         (cadr x) 
                                         "'," 
                                         (rtos (last x) 2 2) 
                                         ");" 
                                 ) 
                       ) 
                     ) 
             ) 
           ) 
          lst 
  ) 
  (setq        lst1 (xdrx-sqlite-query 
               db 
               "select * from mytable order by string;" 
             ) 
  ) 
  (xdrx-prompt "\nlst1=" (vl-prin1-to-string (cdr lst1))) 
   (setq        lst2 (xdrx-sqlite-query 
               db 
               "select * from mytable order by float desc;" 
             ) 
  ) 
  (xdrx-prompt "\nlst2=" (vl-prin1-to-string (cdr lst2))) 
  (chkerr (xdrx-sqlite-close db)) 
  (princ) 
)

原随机表：
lst=
((99 "DTU" 118.605) (148 "AVB" 250.888) (118 "15A" 6.60631) (181 "63Q" 4.60972) (205 "QUK" 65.0506) (221 "F7X" 84.9369) (15 "E6H" 56.9618) (241 "PBW" 196.656) (227 "ZU7" 208.845) (170 "9L0" 160.584))

对第二列字符串排升序

SQL查询语句："select * from mytable order by string;"

lst1=
((118 "15A" 6.61) (181 "63Q" 4.61) (170 "9L0" 160.58) (148 "AVB" 250.89) (99 "DTU" 118.6) (15 "E6H" 56.96) (221 "F7X" 84.94) (241 "PBW" 196.66) (205 "QUK" 65.05) (227 "ZU7" 208.85))

对第三列浮点数排降序

SQL查询语句： "select * from mytable order by float desc;"

lst2=
((148 "AVB" 250.89) (227 "ZU7" 208.85) (241 "PBW" 196.66) (170 "9L0" 160.58) (99 "DTU" 118.6) (221 "F7X" 84.94) (205 "QUK" 65.05) (15 "E6H" 56.96) (118 "15A" 6.61) (181 "63Q" 4.61))

账号		自动登录	找回密码
密码			立即注册

[每日一码] SQLite数据库函数应用（一）对表任一列为索引排升（降）序

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。