显示用户id、用户名、总金额、折扣后支付金额,并给出别名User_ID、user_name、Total_amount、Paid_amount。按用户id按降序显示记录。单击表SHCEMA获取表
这是我写的代码。但这并没有显示出预期的结果:
SELECT
USERS.USER_ID AS User_ID,
NAME AS user_name,
(FARE * NO_SEATS) AS Total_amount
FROM
USERS
INNER JOIN
TICKETS ON USERS.USER_ID = TICKETS.USER_ID
INNER JOIN
PAYMENTS ON TICKETS.TICKET_ID = PAYMENTS.TICKET_ID
INNER JOIN
DISCOUNTS ON PAYMENTS.DISCOUNT_ID = DISCOUNTS.DISCOUNT_ID
GROUP BY
USERS.USER_ID, NAME, FARE, NO_SEATS
ORDER BY
USERS.USER_ID DESC;这是预期的产出:
USER_ID USER_NAME TOTAL_AMOUNT PAID_AMOUNT
----------------------------------------------
5 Krena 700 625
4 Johan 800 775
3 Ivan 3000 2900
1 John 4000 3950
1 John 4000 3950
1 John 2000 1900发布于 2017-07-16 17:46:34
这个会有用的,试试吧
SELECT
USERS.USER_ID AS USER_ID,
USERS.NAME AS USER_NAME,
(TICKETS.FARE * TICKETS.NO_SEATS) AS TOTAL_AMOUNT,
(TICKETS.FARE * TICKETS.NO_SEATS - DISCOUNTS.DISCOUNT_AMOUNT) AS PAID_AMOUNT
FROM
USERS
INNER JOIN TICKETS
ON USERS.USER_ID=TICKETS.USER_ID
INNER JOIN PAYMENTS
ON TICKETS.TICKET_ID=PAYMENTS.TICKET_ID
INNER JOIN DISCOUNTS
ON PAYMENTS.DISCOUNT_ID=DISCOUNTS.DISCOUNT_ID
ORDER BY USERS.USER_ID DESC;发布于 2017-07-16 17:11:39
试试这段代码。
SELECT
USERS.USER_ID AS USER_ID,
USERS.NAME AS USER_NAME,
(TICKETS.FARE * TICKETS.NO_SEATS) AS TOTAL_AMOUNT,
(TICKETS.FARE * DISCOUNTS.DISCOUNT_AMOUNT) + TICKETS.FARE AS PAID_AMOUNT
FROM
USERS USERS
INNER JOIN TICKETS TICKETS
ON USERS.USER_ID=TICKETS.USER_ID
INNER JOIN PAYMENTS PAYMENTS
ON TICKETS.TICKET_ID=PAYMENTS.TICKET_ID
INNER JOIN DISCOUNTS DISCOUNTS
ON PAYMENTS.DISCOUNT_ID=DISCOUNTS.DISCOUNT_ID
ORDER BY USERS.USER_ID DESC;我对您的数据库结构感到困惑,为什么它是数字数据类型?如果你想要的话,你可能想把它改为小数点,并将它的长度改为(11,2),因为我假设你的折扣表中的折扣金额是百分比,对吗?或者将示例数据库粘贴到这里,这样我就可以更新代码了。
发布于 2017-07-16 17:31:43
如果我有足够的声誉,我会写这个评论(叹息):你还没有完全确定你的预期结果是什么。
我假定以下几点:
那么,这应该是个好办法:
SELECT
USER_ID AS USER_ID,
NAME AS USER_NAME,
sum(FARE * NO_SEATS) AS TOTAL_AMOUNT,
sum((FARE - DISCOUNT_AMOUNT ) * NO_SEATS) AS PAID_AMOUNT
FROM USERS u
INNER JOIN TICKETS t
ON u.USER_ID=t.USER_ID
INNER JOIN PAYMENTS p
ON t.TICKET_ID=p.TICKET_ID
INNER JOIN DISCOUNTS d
ON p.DISCOUNT_ID=d.DISCOUNT_ID
group by USER_ID, name
ORDER BY USER_ID DESC;https://stackoverflow.com/questions/45131039
复制相似问题