首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >SQL查询以生成以下输出

问SQL查询以生成以下输出
EN

Stack Overflow用户
提问于 2017-07-16 16:46:22
回答 5查看 6.9K关注 0票数 1

显示用户id、用户名、总金额、折扣后支付金额,并给出别名User_ID、user_name、Total_amount、Paid_amount。按用户id按降序显示记录。单击表SHCEMA获取表

这是我写的代码。但这并没有显示出预期的结果:

代码语言:javascript
复制
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;

这是预期的产出:

代码语言:javascript
复制
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
EN

回答 5

Stack Overflow用户

回答已采纳

发布于 2017-07-16 17:46:34

这个会有用的,试试吧

代码语言:javascript
复制
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;
票数 1
EN

Stack Overflow用户

发布于 2017-07-16 17:11:39

试试这段代码。

代码语言:javascript
复制
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),因为我假设你的折扣表中的折扣金额是百分比,对吗?或者将示例数据库粘贴到这里,这样我就可以更新代码了。

票数 0
EN

Stack Overflow用户

发布于 2017-07-16 17:31:43

如果我有足够的声誉,我会写这个评论(叹息):你还没有完全确定你的预期结果是什么。

我假定以下几点:

  • 打印用户的总金额,两个Total_amount (即。官方价格),Paid_amount (即。在折扣之后),所以它将包括所有的票和他们的付款。
  • 我假定折扣是一个从公平中扣除的正数,每1张座位。

那么,这应该是个好办法:

代码语言:javascript
复制
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;
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/45131039

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档