SQL - 每列的where条件

发布于 2024-11-08 19:53:16 字数 550 浏览 0 评论 0原文

这是一个一般的 SQL 问题，但我如果你必须知道数据库是 Firebird。

我有这个表（简化的）：

user  amount  type
--------------------
john  25      credit
john  20      debit
john  5       debit
john  15      credit
mike  15      credit

我想要使用单个查询得到一个看起来像这样的结果：

user  credit  debit
--------------------
john  40      25
mike  15      NULL

Where credit = SUM(amount) WHERE type=credit
   and debit = SUM(amount) WHERE type=debit

基本上我想在结果上有多个字段基于一个字段的总和（在本例中为金额）但在不同的条件（在本例中为类型）。

预先感谢您的任何建议。

原文

This is a general SQL question, but I if you must know the database is Firebird.

I have this table (simplified):

user  amount  type
--------------------
john  25      credit
john  20      debit
john  5       debit
john  15      credit
mike  15      credit

I want to have a result that looks like this, using a single query:

user  credit  debit
--------------------
john  40      25
mike  15      NULL

Where credit = SUM(amount) WHERE type=credit
   and debit = SUM(amount) WHERE type=debit

Basically I want to have multiple fields on the result based on a SUM of a field (in this case amount) but on different conditions (in this case type).

Thanks in advance for any suggestions.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

把人绕傻吧 2024-11-15 19:53:16

标准 SQL 是：

SELECT "user",
    SUM(CASE WHEN type='credit' then amount END) as Credit,
    SUM(CASE WHEN type='debit' then amount END) as Debit
FROM
    "table"
GROUP BY
    "user"

Standard SQL would be:

SELECT "user",
    SUM(CASE WHEN type='credit' then amount END) as Credit,
    SUM(CASE WHEN type='debit' then amount END) as Debit
FROM
    "table"
GROUP BY
    "user"

回复收藏 0 原文

说谎友 2024-11-15 19:53:16

我不确定 Firebird 是如何做到的，但是你能做类似的事情吗：

SELECT user, 
sum(SELECT amount FROM table AS t2 WHERE t2.user = t1.user AND type="credit" ) AS credit, 
sum(SELECT amount FROM table AS t3 WHERE t3.user = t1.user AND type="debit" ) AS debit
FROM table AS t1
GROUP BY user
ORDER BY user

我正在引用我认为在 PostgreSQL 中的内容，以防帮助你找到你需要的东西。

I'm not sure how Firebird does it, but can you do something like:

SELECT user, 
sum(SELECT amount FROM table AS t2 WHERE t2.user = t1.user AND type="credit" ) AS credit, 
sum(SELECT amount FROM table AS t3 WHERE t3.user = t1.user AND type="debit" ) AS debit
FROM table AS t1
GROUP BY user
ORDER BY user

I'm referencing what I think it would be in PostgreSQL, in case that helps you find what you need.

回复收藏 0 原文

孤者何惧 2024-11-15 19:53:16

试试这个：

SELECT credit.user_name, credit.cre, debit.deb
  FROM (SELECT user_name, SUM(amount) cre FROM t WHERE TYPE = 'credit' GROUP BY user_name) credit
  LEFT OUTER JOIN (SELECT user_name, SUM(amount) deb
                     FROM t
                    WHERE TYPE = 'debit'
                    GROUP BY user_name) debit
    ON (credit.user_name = debit.user_name)

try this :

SELECT credit.user_name, credit.cre, debit.deb
  FROM (SELECT user_name, SUM(amount) cre FROM t WHERE TYPE = 'credit' GROUP BY user_name) credit
  LEFT OUTER JOIN (SELECT user_name, SUM(amount) deb
                     FROM t
                    WHERE TYPE = 'debit'
                    GROUP BY user_name) debit
    ON (credit.user_name = debit.user_name)

回复收藏 0 原文

~没有更多了~