代码之家 › 专栏 › 技术社区 › unmounted

跨列的SQL绝对值

postgresql oracle sql mysql python

unmounted · 技术社区 · 16 年前

word        big   expensive   smart   fast

dog         9     -10         -20     4
professor   2     4           40      -7
ferrari     7     50          0       48
alaska      10    0           1       0
gnat        -3    0           0       0

另外,程序的其余部分是python,因此,如果有任何带有普通dbapi模块或更抽象模块的python解决方案,欢迎提供帮助。

5 回复 | 直到 8 年前

James Curran 16 年前

按大的绝对值列出的单词:

select word, big from myTable order by abs(big)

每个类别的总数:

select sum(abs(big)) as sumbig, 
       sum(abs(expensive)) as sumexpensive,   
       sum(abs(smart)) as sumsmart,
       sum(abs(fast)) as sumfast
  from MyTable;

Mark Harrison 16 年前

从零开始最远的abs值:

select max(abs(mycol)) from mytbl

如果值为负值,则将为零:

select n+abs(mycol)
  from zzz
 where abs(mycol)=(select max(abs(mycol)) from mytbl);

Thorsten 16 年前

问题似乎是您主要希望在一行内工作,而这类问题在SQL中很难回答。

word property value

或者通过重新设计基础表(如果可能,并且对于应用程序的其余部分有意义的话),或者通过定义一个视图来实现这一点,比如

select word, 'big' as property, big as value from soquestion
UNION ALLL
select word, 'expensive', expensive from soquestion
UNION ALL
...

这允许您询问每个单词的最大值:

select word, max(value), 
    (select property from soquestion t2 
     where t1.word = t2.word and t2.value = max(t1.value))
from soquestion t1
group by word

仍然有点尴尬,但大多数逻辑将使用SQL,而不是您选择的编程语言。

Toby Hede 16 年前

unmounted 16 年前

提问有助于澄清问题;这是一个函数,它能让我更清楚地了解我要做的事情。是否有一种方法可以表示上面第2部分中的内容,或者有一种更有效的方法可以用SQL或python来完成我试图用SQL或python完成的任务 show_distinct ?

#!/usr/bin/env python

import sqlite3

conn = sqlite3.connect('so_question.sqlite')
cur = conn.cursor()

cur.execute('create table soquestion (word, big, expensive, smart, fast)')
cur.execute("insert into soquestion values ('dog', 9, -10, -20, 4)")
cur.execute("insert into soquestion values ('professor', 2, 4, 40, -7)")
cur.execute("insert into soquestion values ('ferrari', 7, 50, 0, 48)")
cur.execute("insert into soquestion values ('alaska', 10, 0, 1, 0)")
cur.execute("insert into soquestion values ('gnat', -3, 0, 0, 0)")

cur.execute("select * from soquestion")
all = cur.fetchall()

definition_list = ['word', 'big', 'expensive', 'smart', 'fast']

def show_distinct(db_tuple, def_list=definition_list):
    minimum = min(db_tuple[1:])
    maximum = max(db_tuple[1:])
    if abs(minimum) > maximum:
        print db_tuple[0], 'is not', def_list[list(db_tuple).index(minimum)]
    elif maximum > abs(minimum):
        print db_tuple[0], 'is', def_list[list(db_tuple).index(maximum)]
    else:
        print 'no distinct value'

for item in all:
    show_distinct(item)

运行此命令将提供:

    dog is not smart
    professor is smart
    ferrari is expensive
    alaska is big
    gnat is not big
    >>>