从多行中选择多列并返回包含总计数的行

发布于 2024-12-05 02:51:17 字数 285 浏览 0 评论 0原文

好吧,这个解释起来有点复杂,我会尽力的,如果你不明白我欢迎你提问。

我有一个这样设计的表格:

TITLE |分公司 |分行1 |分支2 | BRANCH3

一个 BRANCH (其中任何一个)可能包含“bank”、“credit”等等,

我想要做的是返回具有不同分支和计数的行,其中标题等于“xxx”

类似:

银行| xxx 结果

信用 | xxx 结果 等等。

ok, this is kind of complicated to explain, i will try my best, you are welcome to ask questions if you dont understand me.

i have a table desiged like this:

TITLE | BRANCH | BRANCH1 | BRANCH2 | BRANCH3

a BRANCH (any of them) might contain "bank","credit" and so on

what i want to do, is return rows with distinct branches and count, where the title equals "xxx"

something like:

BANK | xxx Results

CREDIT | xxx Results
and so on.

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

倾城泪 2024-12-12 02:51:17

如果您无法更改表格以符合1FN,则以下内容应该可以帮助您开始

  • 使用 UNION ALL UNPIVOT 列,为您提供符合 1FN
  • 使其成为子选择
  • 从该子选择中过滤 TITLE
  • GROUP BYCOUNT 结果

SQL 语句

SELECT  BRANCH, COUNT(*)
FROM    (
          SELECT TITLE, BRANCH FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH1 FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH2 FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH3 FROM MyTable 
        ) q
WHERE   TITLE = 'ATitle'        
GROUP BY
        BRANCH

If you can't alter your tables to conform to 1FN, following should get you started

  • UNPIVOT the columns using a UNION ALL giving you a resultset that conforms to 1FN.
  • Make this into a subselect
  • Filter on the TITLE from this subselect
  • GROUP BY and COUNT the results

SQL Statement

SELECT  BRANCH, COUNT(*)
FROM    (
          SELECT TITLE, BRANCH FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH1 FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH2 FROM MyTable 
          UNION ALL SELECT TITLE, BRANCH3 FROM MyTable 
        ) q
WHERE   TITLE = 'ATitle'        
GROUP BY
        BRANCH
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文