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

Question

我正在做一个业余爱好项目，自学编程和sql。这是一款应用程序，可以让你创建单位并建立一支军队来进行研发扩张。我的问题是，一个单位的成本是由它的属性(攻击，力量，防御，坚韧，士气)得出的，而这些属性又是由几个变量(它的祖先(矮人)，经验(老兵)，装备(重型)和类型(步兵))得出的。

这是我想出来的代码，我询问的语句是第二个：

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的人没有考虑到这一点，并想出了一个聪明的方法来避免这样做。在这里计算单位成本的正确方法是什么？

编辑:Shawn的解决方案起作用了。我现在有一个函数来首先获取统计数据：

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