在sqlite中使用列别名进行计算

Question

我正在做一个业余项目，自学编程和sql。 这是一款应用程序，可以让你为d&；d扩展。 我的问题是，一个单位的成本来源于它的属性（攻击、力量、防御、韧性、士气），而这些属性又来源于几个变量（它的祖先（矮人）、经验（老兵）、装备（重型）和类型（步兵）

这是我提出的代码，我要问的是第二条：

def display_unit(connection, rowid:int):
rowid = str(rowid)
with connection:
    cursor = connection.cursor()
    cursor.execute("""SELECT name, ancestry, experience, equipment, type FROM units WHERE rowid = ?;""", rowid)
    result = cursor.fetchone()
    (name, ancestry, experience, equipment, type) = result
    print(f"{name}:\t{ancestry} {experience} {equipment} {type}")
    print("Army: \n")
    print("Attack  Power  Defense  Toughness  Morale  Cost")
    cursor.execute("""SELECT 
    (ancestry.attack + experience.attack + equipment.attack + type.attack) AS attack,
    (ancestry.power + experience.power + equipment.power + type.power) AS power,
    (ancestry.defense + experience.defense + equipment.defense + type.defense) AS defense,
    (ancestry.toughness + experience.toughness + equipment.toughness + type.toughness) AS toughness,
    (ancestry.morale + experience.morale + equipment.morale + type.morale) AS morale,
    ((((attack + power + defense + toughness + (morale*2)) * type.costmp) * size.costmp) * 10 + 30) as cost
    FROM units
    JOIN ancestry on units.ancestry = ancestry.ancestry
    JOIN experience on units.experience = experience.experience
    JOIN equipment on units.equipment = equipment.equipment
    JOIN type on units.type = type.type
    JOIN size on units.size = size.size
    WHERE units.rowid = ?; 
    
    """, (rowid))
    result = cursor.fetchone()
    (attack, power, defense, toughness, morale, cost) = result
    print(f"   {attack:1}{power:7}{defense:8}{toughness:10}{morale:10}{cost:8}")

因此，为了计算单位的成本，我想对组合的统计数据使用别名，但显然不能在定义别名的同一select语句中使用别名进行计算。 现在我可能可以复制创建列别名的行并将其插入公式中，但这会将它变成一个混乱的语句，我无法相信创建sqlite的人没有想到它，并且想出了一个不必这样做的聪明方法。 这里计算单位成本的正确方法是什么

编辑： 肖恩的解决方案奏效了。 我现在有了一个先获取统计数据的函数：

def get_stats(connection): #creates a new view of all the units' stats
    with connection:
        cursor = connection.cursor()
        cursor.execute("""DROP VIEW IF EXISTS stats;""")
        cursor.execute("""CREATE VIEW stats AS SELECT
        (ancestry.attack + experience.attack + equipment.attack + type.attack) AS attacktotal,
        (ancestry.power + experience.power + equipment.power + type.power) AS powertotal,
        (ancestry.defense + experience.defense + equipment.defense + type.defense) AS defensetotal,
        (ancestry.toughness + experience.toughness + equipment.toughness + type.toughness) AS toughnesstotal,
        (ancestry.morale + experience.morale + equipment.morale + type.morale) AS moraletotal,
        typecostmp, sizecostmp, units.rowid
        FROM units
        JOIN ancestry on units.ancestry = ancestry.ancestry
        JOIN experience on units.experience = experience.experience
        JOIN equipment on units.equipment = equipment.equipment
        JOIN type on units.type = type.type
        JOIN size on units.size = size.size;""")

然后使用该视图获取成本：

def get_cost(connection, rowid): # returns the stats plus the cost for the unit as a tuple.
    with connection:               # (attack, power, defense, toughness, morale, cost)
        cursor = connection.cursor()
        get_stats(connection)
        cursor.execute("""SELECT attacktotal, powertotal, defensetotal, toughnesstotal, moraletotal,
                ((((attacktotal + powertotal + defensetotal + toughnesstotal + (moraletotal*2)) 
                * typecostmp) * sizecostmp) * 10 + 30)
                FROM stats
                WHERE rowid = ?
                """, rowid)
        result = cursor.fetchone()
        return result

Answer 1

肖恩的解决方案奏效了。我现在有了一个先获取统计数据的函数：

def get_stats(connection): #creates a new view of all the units' stats
    with connection:
        cursor = connection.cursor()
        cursor.execute("""DROP VIEW IF EXISTS stats;""")
        cursor.execute("""CREATE VIEW stats AS SELECT
        (ancestry.attack + experience.attack + equipment.attack + type.attack) AS attacktotal,
        (ancestry.power + experience.power + equipment.power + type.power) AS powertotal,
        (ancestry.defense + experience.defense + equipment.defense + type.defense) AS defensetotal,
        (ancestry.toughness + experience.toughness + equipment.toughness + type.toughness) AS toughnesstotal,
        (ancestry.morale + experience.morale + equipment.morale + type.morale) AS moraletotal,
        typecostmp, sizecostmp, units.rowid
        FROM units
        JOIN ancestry on units.ancestry = ancestry.ancestry
        JOIN experience on units.experience = experience.experience
        JOIN equipment on units.equipment = equipment.equipment
        JOIN type on units.type = type.type
        JOIN size on units.size = size.size;""")

然后使用该视图获取成本：

def get_cost(connection, rowid): # returns the stats plus the cost for the unit as a tuple.
    with connection:               # (attack, power, defense, toughness, morale, cost)
        cursor = connection.cursor()
        get_stats(connection)
        cursor.execute("""SELECT attacktotal, powertotal, defensetotal, toughnesstotal, moraletotal,
                ((((attacktotal + powertotal + defensetotal + toughnesstotal + (moraletotal*2)) 
                * typecostmp) * sizecostmp) * 10 + 30)
                FROM stats
                WHERE rowid = ?
                """, rowid)
        result = cursor.fetchone()
        return result